Program Flow Control
Have you ever wanted to have a macro change the order commands are executed in--or even pick which commands execute and which don't--depending on what's going on? If the answer is yes, then what you want to know is how to control the flow of a program.
Flow control involves telling the computer which command to execute next. Normally, this is the next line; unless instructed otherwise, the computer tries to executes each line in turn until it reaches the end of the program. That works fine for fairly simple, straightforward macros, but there are times when that just won't work. Loops are a variant of flow control, in that a certain set of commands are executed over and over, but there is much more to this concept than simple repetitions.
Last time, you were introduced to the Goto statement, but only in the context of loops. Here's an example of a use of the Goto statement that doesn't involve an infinite loop--or any kind of loop at all, for that matter.
sub main
x = 1
Goto Skip
x = 2
Skip:
MsgBox x
end sub
If the program executes correctly, then the result will be a message box with a "1" in it; the line with "x = 2" is skipped.
One of the main staples of any program is the If...Then "conditional" statement, which does a fairly good job of explaining itself. If a statement is true, Then execute a command; otherwise continue with the program as if the If...Then statement wasn't there.
If x = 1 Then y = 2
If you need to execute multiple commands when the statement is true, you can do so, but then you must use End If.
If x = 1 Then
y = 2
z = 3
End if
Normal programming practice is to indent anything within an extended If...Then statement, so that it is easy to see where the statement begins and ends, and what is inside. This becomes especially necessary with If...Then statements that are "nested" inside each other.
If x = 1 Then
If y = 2 Then
z = 3
MsgBox z
End if
z = z + 1
End If
If you want one command or set of commands to execute if the statement is true, and a different set to execute if false, then you can add an Else into the works.
If x = 1 Then y = 2 Else y = 3
If y = 2 Then
x = 2
y = y + 1
Else
x = 5
End If
The final piece of the If...Then puzzle is Elseif (or ElseIf, if you prefer). Depending on what sorts of conditions you are evaluating, it can come in handy. Also watch how If...Then can be combined with Goto to provide options for which sections of the program get executed or are skipped over.
If x > 2 Then
Goto Looper
Elseif x = 2 Then
Goto Main
Else
Goto Done
End If
Note that first condition. That's not an equal sign! Conditions work using Boolean logic--meaning there are only two results, "true" and "false"--with the addition that FALSE is equivalent to 0 and TRUE is equivalent to (not 0). Conditions can include =, >, <, >=, <=, <>, AND, OR, or nothing other than a variable, using parentheses to set off various parts of the statement Some examples:
If x = 1 Then y = 2 If x > 2 Then x = 2 If x < y Then z = 3 If x <> 1 Then x = 1 If x = 1 And y = 2 Then z = 3 If (x = 1 And y = 2) Then z = 3 If (x = 1 And y = 2) Or (z = 3) then x = y If x = FALSE Then y = 2 If x = TRUE then z = 3 If x Then z = 3 If InStr(a$, b$)
The first four sould be pretty easy to understand. The fifth and sixth are functionally identical--both conditions must be true for the command after Then to be executed--but the latter may be easier to read and quickly understand what's going on. For the seventh, either both of the first two equalities have to be true, or the third has to be true in order for the specified command to be executed. The eighth and ninth show the possible uses of TRUE and FALSE other than as results. The eighth says the same thing as "If x = 0...", and the ninth says the same as "If x <> 0..." The tenth line is functionally identical to the ninth. The last line shows that any command that returns a TRUE/FALSE result or a number of some sort can be used in an If...Then statement.
Another way to do many of the same things as an If...Then statement is Select Case. You give it the variable to check, and a set of possible results, and the program takes care of the rest.
Select Case x
Case 0
Goto Zero
Case 1, 3, 5, 7, 9
Goto Odds
Case 2, 4, 6, 8
Goto Evens
Case 10 To 20
Goto Teens
Case Is > 20
Goto More
Case Else
Goto Mistake
End Select
As you can see, it's possible to make any of several outcomes have the same result, as well as those that fall within a range of values (note that the lesser value must always be on the left), or are on one side of an inequality. Also, it's possible to say that everything not specified (in this case, a negative) triggers a certain command/set of commands, using Case Else. It is also possible to use strings with Select Case; such a line would be in the form
Select Case a$
Case "b"
Goto Bee
Case "c"
Goto Cee
End Select
If a$ is either "b" or "c", then the program moves on the the specified
section of the program; otherwise it just continues normally.
Next time, subroutines and functions...