What is the difference between for..if, case statements, do..while?

[dan]

I'm using Excel 2000 on Windows 2000 professional.


I've been employed since February to provide solutions in Excel and
Access 2000. I've been successful finding examples in the newsgroups
and revising them to my projects.

However, I have a big knowledge gap and I'm requesting your direction.

How does one decide which programming construct to use.

Are there some questions I could answer that would help me determine
when to use:

With
if
For..if
Do..While
Case

I also would like to learn the proper syntax for these constructs.

I've purchased John Walkenbach's "MIcrosoft Excel 2000 Bible, and Ken
Getz's "Access 2000 Developer's Handbook", but I am still not clear on
these concepts.

Is there anyone that would direct me to learning source?

Thanks,

Dan Dungan

 

[Corey]

Try the book:
Excel Programming for Dummies.
I found it great and easy to understand.

I'm using Excel 2000 on Windows 2000 professional.


I've been employed since February to provide solutions in Excel and
Access 2000. I've been successful finding examples in the newsgroups
and revising them to my projects.

However, I have a big knowledge gap and I'm requesting your direction.

How does one decide which programming construct to use.

Are there some questions I could answer that would help me determine
when to use:

With
if
For..if
Do..While
Case

I also would like to learn the proper syntax for these constructs.

I've purchased John Walkenbach's "MIcrosoft Excel 2000 Bible, and Ken
Getz's "Access 2000 Developer's Handbook", but I am still not clear on
these concepts.

Is there anyone that would direct me to learning source?

Thanks,

Dan Dungan

 

[Guest]

In programming there are only 3 things you can do. You can write a series of
instruction to be run in sequence. You can write a selection structure which
causes the program to follow one path of exectution or another. Finally you
can write a structure to loop through the same code over and over until a
certain condition is met. Most of your questions revolve around these
concepts.

Selection Structures ******************************************
If / Then / Else
Select Case
Both of the above structures cause your program to fork into different paths
of execution. Some lines of code will be executed and other will not
depending on which criteria are met or not met.
If then Else is the simplest to understand. If X is true then do this else
do that.
A little more complicated is
If Then
ElseIf Then
Else
EndIf
This structure runs through multiple criteria to determine the approporate
code to run. For Example
If x > 10 then
To That
elseif x > 0 then
Do That
Else
Do TheOther
EndIf
It is important to note that only one of the instructions This or That or
TheOther can possible be run. It can not execute both This and That or any
other combination.
Select Case is just another version of the If Then / ElseIf Then / Else
structure. It takes a look at a single value and compares it to a number of
possible cases to determine the flow of execution.

If Then / ElseIf Then / Else - is handy when you want to eveluate different
things such as
If x = 10 then
elseif y = 20 then
elseif z = 30 then
else

Select Case is handy whe you want to evaluate the same thing aginast a
number of possible cases
Select Case X
Case 10, 20, 30
Case 40, 50, 60
Case else

Looping Structures*********************************************
For Each
Do While
Loop Until
For Each is handy for traversing all of the objects in a collection such as
all of the sheets in a workbook
dim wks as worksheet
for each wks in worksheets
msgbox wks.name
next wks

Do while an loop until cause you to loop throgh the same st of instructions
until a condition is met. The difference is Do While ceches the criteria
before you enter the loop where as loop until only evaluates the condition
after the loop code has executed once. So it depends on when you want to
check your condition.

With ****************************************************
With is a construct designed to make you life a little easier and to make
code execute a little bit faster. What it does is it tells the computer to
reference back to a certain item until further notice. For example if I want
to do a bunch of things to a specific cell on a specific sheet then I could
either do this

Sheets("Sheet1").Range("A1").Value = 10
Sheets("Sheet1").Range("A1").Interior.ColorIndex = 34
Sheets("Sheet1").Range("A1").Font.ColorIndex = 1
Sheets("Sheet1").Range("A1").BorderAround = True
or
With Sheets("Sheet1").Range("A1")
..Value = 10
..Interior.ColorIndex = 34
..Font.ColorIndex = 1
..Font.ColorIndex = 1
..BorderAround = True
End With

Note that the code using the with statement is a pile shorter to write. It
will also execute faster because the computer does not have to keem making a
reference to Sheets("Sheet1").Range("A1") evey time it want to make a change
to that cell.

 

[Gary Keramidas]

well, jim, i guess it really is the first day of school<g>

i'm sure the op will appreciate your time.

 

[dan]

Hi Jim,

I do appreciate your explanation and time. Thanks for your feedback.

Dan