Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
The preceding section describes the For Each-Next construct, which is a type of loop. This
section discusses the additional ways of controlling the execution of your VBA procedures:
h GoTo statements
h If-Then constructs
h Select Case constructs
h For-Next loops
h Do While loops
h Do Until loops
The most straightforward way to change the flow of a program is to use a GoTo statement. This
statement simply transfers program execution to a new instruction, which must be preceded by a
label (a text string followed by a colon, or a number with no colon). VBA procedures can contain
any number of labels, but a GoTo statement can’t branch outside of a procedure.
The following procedure uses the VBA InputBox function to get the user’s name. If the name is
not Howard, the procedure branches to the WrongName label and ends. Otherwise, the
procedure executes some additional code. The Exit Sub statement causes the procedure to end.
UserName = InputBox(“Enter Your Name:”)
If UserName <> “Howard” Then GoTo WrongName
MsgBox (“Welcome Howard...”)
‘ -[More code here] -
MsgBox “Sorry. Only Howard can run this macro.”
This simple procedure works, but it’s not an example of good programming. In general, you should
use the GoTo statement only when you have no other way to perform an action. In fact, the only
time you really need to use a GoTo statement in VBA is for error handling (refer to Chapter 9).
Finally, it goes without saying that the preceding example is not intended to demonstrate an
effective security technique!
Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This
common instruction is one way to endow your applications with decision-making capability.
Good decision-making is the key to writing successful programs.