Microsoft Office Tutorials and References
In Depth Information
x = WorksheetFunction.SQRT(123)
‘error
Controlling Execution
Some VBA procedures start at the top and progress line by line to the bottom. Often, however, you need to con-
trol the flow of your routines by skipping over some statements, executing some statements multiple times, and
testing conditions to determine what the routine does next.
This section discusses several ways of controlling the execution of your VBA procedures:
• If-Then constructs
• Select Case constructs
• For-Next loops
• Do While loops
• Do Until loops
• On Error statements
The If-Then construct
Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This instruction is one
way to endow your applications with decision-making capability. The basic syntax of the If-Then construct is as
follows:
If condition Then true_instructions [Else false_instructions]
The If-Then construct executes one or more statements conditionally. The Else clause is optional. If included, it
enables you to execute one or more instructions when the condition that you test is not true.
The following Function procedure demonstrates an If-Then structure without an Else clause. The example deals
with time. VBA uses the same date-and-time serial number system as Excel (but with a much wider range of
dates). The time of day is expressed as a fractional value — for example, noon is represented as .5. The VBA
Time function returns a value that represents the time of day, as reported by the system clock. In the following
example, the function starts out by assigning an empty string to GREETME. The If-Then statement checks the
time of day. If the time is before noon, the Then part of the statement executes, and the function returns Good
Morning.
Function GREETME()
GREETME = “”
If Time < 0.5 Then GREETME= “Good Morning”
End Function
The following function uses two If-Then statements. It displays either Good Morning or Good Afternoon:
Function GREETME()
If Time < 0.5 Then GREETME = “Good Morning”
If Time >= 0.5 Then GREETME = “Good Afternoon”
Search JabSto ::




Custom Search