Controlling Code Execution
Some VBA procedures start at the top and progress line by line to the bottom. Macros that you record, for example, always work in this fashion. Often, however, you need to control 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 .
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:
-
GoTo statements
-
If-Then constructs
-
Select Case constructs
-
For-Next loops
-
Do While loops
-
Do Until loops
GoTo statements
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 cannot 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.
Sub GoToDemo() UserName = InputBox("Enter Your Name:") If UserName <> "Howard" Then GoTo WrongName MsgBox ("Welcome Howard...") ' -[More code here] - Exit Sub WrongName: MsgBox "Sorry. Only Howard can run this." End Sub This simple procedure works, but it's not an example of good programming. In general, you should use the GoTo statement only when there is 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!
If-Then constructs
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.
The basic syntax of the If-Then construct is
If condition Then true_instructions [Else false_instructions ]
The If-Then construct is used to execute one or more statements conditionally. The Else clause is optional. If included, the Else clause lets you execute one or more instructions when the condition that you're testing is not True .
The following procedure demonstrates an If-Then structure without an Else clause. The example deals with time, and VBA uses a date-and-time serial number system similar to Excel's. 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, a message is displayed if the time is before noon. If the current system time is greater than or equal to .5 , the procedure ends, and nothing happens.
Sub GreetMe1() If Time < 0.5 Then MsgBox "Good Morning" End Sub
Another way to code this routine is to use multiple statements, as follows :
Sub GreetMe1a() If Time < 0.5 Then MsgBox "Good Morning" End If End Sub
Notice that the If statement has a corresponding End If statement. In this example, only one statement is executed if the condition is True . You can, however, place any number of statements between the If and End If statements.
If you want to display a different greeting when the time of day is after noon, add another If-Then statement, like so:
Sub GreetMe2() If Time < 0.5 Then MsgBox "Good Morning" If Time >= 0.5 Then MsgBox "Good Afternoon" End Sub
Notice that I used >= (greater than or equal to) for the second If-Then statement. This covers the remote chance that the time is precisely 12:00 noon.
Another approach is to use the Else clause of the If-Then construct. For example,
Sub GreetMe3() If Time < 0.5 Then MsgBox "Good Morning" Else _ MsgBox "Good Afternoon" End Sub
Notice that I used the line continuation sequence; If-Then-Else is actually a single statement.
If you need to execute multiple statements based on the condition, use this form:
Sub GreetMe3a() If Time < 0.5 Then MsgBox "Good Morning" ' Other statements go here Else MsgBox "Good Afternoon" ' Other statements go here End If End Sub
If you need to expand a routine to handle three conditions (for example, morning, afternoon, and evening), you can use either three If-Then statements or a form that uses ElseIf . The first approach is the simpler:
Sub GreetMe4() If Time < 0.5 Then MsgBox "Good Morning" If Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon" If Time >= 0.75 Then MsgBox "Good Evening" End Sub
The value 0.75 represents 6:00 p.m. - three- quarters of the way through the day and a good point at which to call it an evening.
In the preceding examples, every instruction in the procedure gets executed, even if the first condition is satisfied (that is, it's morning). A more efficient procedure would include a structure that ends the routine when a condition is found to be True . For example, it might display the Good Morning message in the morning and then exit without evaluating the other, superfluous conditions. True, the difference in speed is inconsequential when you design a procedure as small as this routine. But for more complex applications, you need another syntax:
If condition Then [true_instructions] [ElseIf condition-n Then [alternate_instructions]] [Else [default_instructions]] End If
Here's how you can use this syntax to rewrite the GreetMe procedure:
Sub GreetMe5() If Time < 0.5 Then MsgBox "Good Morning" ElseIf Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon" Else MsgBox "Good Evening" End If End Sub
With this syntax, when a condition is True , the conditional statements are executed and the If-Then construct ends. In other words, the extraneous conditions are not evaluated. Although this syntax makes for greater efficiency, some find the code to be more difficult to understand.
The following procedure demonstrates yet another way to code this example. It uses nested If-Then-Else constructs (without using ElseIf ). This procedure is efficient and also easy to understand. Note that each If statement has a corresponding End If statement.
Sub GreetMe6() If Time < 0.5 Then MsgBox "Good Morning" Else If Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon" Else If Time >= 0.75 Then MsgBox "Good Evening" End If End If End If End Sub
The following is another example that uses the simple form of the If-Then construct. This procedure prompts the user for a value for Quantity and then displays the appropriate discount based on that value. Note that Quantity is declared as a Variant data type. This is because Quantity contains an empty string (not a numeric value) if the InputBox is cancelled. To keep it simple, this procedure does not perform any other error checking. For example, it does not ensure that the quantity entered is a non-negative numeric value.
Sub Discount1() Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") If Quantity = "" Then Exit Sub If Quantity >= 0 Then Discount = 0.1 If Quantity >= 25 Then Discount = 0.15 If Quantity >= 50 Then Discount = 0.2 If Quantity >= 75 Then Discount = 0.25 MsgBox "Discount: " & Discount End Sub Notice that each If-Then statement in this procedure is always executed, and the value for Discount can change. The final value, however, is the desired value.
The following procedure is the previous one rewritten to use the alternate syntax. In this case, the procedure ends after executing the True instruction block.
Sub Discount2() Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") If Quantity = "" Then Exit Sub If Quantity >= 0 And Quantity < 25 Then Discount = 0.1 ElseIf Quantity < 50 Then Discount = 0.15 ElseIf Quantity < 75 Then Discount = 0.2 Else Discount = 0.25 End If MsgBox "Discount: " & Discount End Sub I find nested If-Then structures rather cumbersome. As a result, I usually use the If- Then structure only for simple binary decisions. When you need to choose among three or more alternatives, the Select Case structure (discussed next) is often a better construct to use.
 |
VBA offers an alternative to the If-Then construct: the IIf function. This function takes three arguments and works much like Excel's IF worksheet function. The syntax is
IIf(expr, truepart, falsepart)
-
expr : (Required) Expression you want to evaluate.
-
truepart : (Required) Value or expression returned if expr is True .
-
falsepart : (Required) Value or expression returned if expr is False .
The following instruction demonstrates the use of the IIf function. The message box displays Zero if cell A1 contains a zero or is empty and displays Nonzero if cell A1 contains anything else.
MsgBox IIf(Range("A1") = 0, "Zero", "Nonzero") It's important to understand that the third argument ( falsepart ) is always evaluated, even if the first argument ( expr ) is True . Therefore, the following statement generates an error if the value of n is (zero):
MsgBox IIf(n = 0, 0, 1 / n)
| |
Select Case constructs
The Select Case construct is useful for choosing among three or more options. This construct also works with two options and is a good alternative to If-Then-Else . The syntax for Select Case is as follows:
Select Case testexpression [Case expressionlist-n [instructions-n]] [Case Else [default_instructions]] End Select
The following example of a Select Case construct shows another way to code the GreetMe examples that I presented in the preceding section:
Sub GreetMe() Dim Msg As String Select Case Time Case Is < 0.5 Msg = "Good Morning" Case 0.5 To 0.75 Msg = "Good Afternoon" Case Else Msg = "Good Evening" End Select MsgBox Msg End Sub
And here's a rewritten version of the Discount example using a Select Case construct. This procedure assumes that Quantity is always an integer value. For simplicity, the procedure performs no error checking.
Sub Discount3() Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") Select Case Quantity Case "" Exit Sub Case 0 To 24 Discount = 0.1 Case 25 To 49 Discount = 0.15 Case 50 To 74 Discount = 0.2 Case Is >= 75 Discount = 0.25 End Select MsgBox "Discount: " & Discount End Sub The Case statement also can use a comma to separate multiple values for a single case. The following procedure uses the VBA WeekDay function to determine whether the current day is a weekend (that is, the Weekday function returns 1 or 7 ). The procedure then displays an appropriate message.
Sub GreetUser1() Select Case Weekday(Now) Case 1, 7 MsgBox "This is the weekend" Case Else MsgBox "This is not the weekend" End Select End Sub
The following example shows another way to code the previous procedure:
Sub GreetUser2() Select Case Weekday(Now) Case 2, 3, 4, 5, 6 MsgBox "This is not the weekend" Case Else MsgBox "This is the weekend" End Select End Sub
Any number of instructions can be written below each Case statement, and they all are executed if that case evaluates to True . If you use only one instruction per case, as in the preceding example, you might want to put the instruction on the same line as the Case keyword (but don't forget the VBA statement-separator character, the colon). This technique makes the code more compact. For example:
Sub Discount3() Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") Select Case Quantity Case "": Exit Sub Case 0 To 24: Discount = 0.1 Case 25 To 49: Discount = 0.15 Case 50 To 74: Discount = 0.2 Case Is >= 75: Discount = 0.25 End Select MsgBox "Discount: " & Discount End Sub | Tip | VBA exits a Select Case construct as soon as a True case is found. Therefore, for maximum efficiency, you should check the most likely case first. |
Select Case structures can also be nested. The following procedure, for example, uses the VBA TypeName function to determine what is selected (a range, nothing, or anything else). If a range is selected, the procedure executes a nested Select Case and tests for the number of cells in the range. If one cell is selected, it displays One cell is selected . Otherwise, it displays a message with the number of selected rows.
Sub SelectionType() Select Case TypeName(Selection) Case "Range" Select Case Selection.Count Case 1 MsgBox "One cell is selected" Case Else MsgBox Selection.Rows.Count & " rows" End Select Case "Nothing" MsgBox "Nothing is selected" Case Else MsgBox "Something other than a range" End Select End Sub
This procedure also demonstrates the use of Case Else , a catch-all case. You can nest Select Case constructs as deeply as you need, but make sure that each Select Case statement has a corresponding End Select statement.
This procedure demonstrates the value of using indentation in your code to clarify the structure. For example, take a look at the same procedure without the indentations:
Sub SelectionType() Select Case TypeName(Selection) Case "Range" Select Case Selection.Count Case 1 MsgBox "One cell is selected" Case Else MsgBox Selection.Rows.Count & " rows" End Select Case "Nothing" MsgBox "Nothing is selected" Case Else MsgBox "Something other than a range" End Select End Sub
Fairly incomprehensible, eh?
Looping blocks of instructions
Looping is the process of repeating a block of instructions. You might know the number of times to loop, or the number could be determined by the values of variables in your program.
The following code, which enters consecutive numbers into a range, demonstrates what I call a bad loop. The procedure uses two variables to store a starting value ( StartVal ) and the total number of cells to fill ( NumToFill ). This loop uses the GoTo statement to control the flow. If the Cnt variable, which keeps track of how many cells are filled, is less than the value of NumToFill , program control loops back to DoAnother .
Sub BadLoop() Dim StartVal As Integer Dim NumToFill As Integer Dim Cnt As Integer StartVal = 1 NumToFill = 100 ActiveCell.Value = StartVal Cnt = 1 DoAnother: ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt Cnt = Cnt + 1 If Cnt < NumToFill Then GoTo DoAnother Else Exit Sub End Sub
This procedure works as intended, so why is it an example of bad looping? Programmers generally frown on using a GoTo statement when not absolutely necessary. Using GoTo statements to loop is contrary to the concept of structured coding (see the "What Is Structured Programming?" sidebar). In fact, a GoTo statement makes the code much more difficult to read because it's almost impossible to represent a loop using line indentations. In addition, this type of unstructured loop makes the procedure more susceptible to error. Furthermore, using lots of labels results in spaghetti code - code that appears to have little or no structure and flows haphazardly.
Because VBA has several structured looping commands, you almost never have to rely on GoTo statements for your decision making.
FOR-NEXT LOOPS
The simplest type of a good loop is a For-Next loop. Its syntax is
For counter = start To end [Step stepval] [instructions] [Exit For] [instructions] Next [ counter ]
Following is an example of a For-Next loop that doesn't use the optional Step value or the optional Exit For statement. This routine executes the Sum = Sum + Sqr(Count) statement 100 times and displays the result - that is, the sum of the square roots of the first 100 integers.
Sub SumSquareRoots() Dim Sum As Double Dim Count As Integer Sum = 0 For Count = 1 To 100 Sum = Sum + Sqr(Count) Next Count MsgBox Sum End Sub
In this example, Count (the loop counter variable) starts out as 1 and increases by 1 each time the loop repeats. The Sum variable simply accumulates the square roots of each value of Count .
| |
Hang around with programmers, and sooner or later you'll hear the term structured programming. You'll also discover that structured programs are considered superior to unstructured programs.
So what is structured programming? And can you do it with VBA?
The basic premise of structured programming is that a routine or code segment should have only one entry point and one exit point. In other words, a body of code should be a standalone unit, and program control should not jump into or exit from the middle of this unit. As a result, structured programming rules out the GoTo statement. When you write structured code, your program progresses in an orderly manner and is easy to follow - as opposed to spaghetti code, in which a program jumps around.
A structured program is easier to read and understand than an unstructured one. More important, it's also easier to modify.
VBA is a structured language. It offers standard structured constructs, such as If-Then-Else and Select Case and the For-Next , Do Until , and Do While loops. Furthermore, VBA fully supports modular code construction.
If you're new to programming, it's a good idea to form good structured-programming habits early.
| |
| Caution | When you use For-Next loops, it's important to understand that the loop counter is a normal variable - nothing special. As a result, it's possible to change the value of the loop counter within the block of code executed between the For and Next statements. This is, however, a bad practice and can cause unpredictable results. In fact, you should take precautions to ensure that your code does not change the loop counter. |
You can also use a Step value to skip some values in the loop. Here's the same procedure rewritten to sum the square roots of the odd numbers between 1 and 100:
Sub SumOddSquareRoots() Dim Sum As Double Dim Count As Integer Sum = 0 For Count = 1 To 100 Step 2 Sum = Sum + Sqr(Count) Next Count MsgBox Sum End Sub
In this procedure, Count starts out as 1 and then takes on values of 3 , 5 , 7 , and so on. The final value of Count used within the loop is 99 . When the loop ends, the value of Count is 101 .
A Step value in a For-Next loop can also be negative. The procedure that follows deletes Rows 2, 4, 6, 8, and 10 of the active worksheet:
Sub DeleteRows() Dim RowNum As Long For RowNum = 10 To 2 Step -2 Rows(RowNum).Delete Next RowNum End Sub
You may wonder why I used a negative Step value in the DeleteRows procedure. If you use a positive Step value, as shown in the following procedure, incorrect rows are deleted. That's because the row numbers below a deleted row get a new row number. For example, when Row 2 is deleted, Row 3 becomes the new Row 2. Using a negative Step value ensures that the correct rows are deleted.
Sub DeleteRows2() Dim RowNum As Long For RowNum = 2 To 10 Step 2 Rows(RowNum).Delete Next RowNum End Sub
The following procedure performs the same task as the BadLoop example found at the beginning of the "Looping blocks of instructions" section. I eliminate the GoTo statement, however, converting a bad loop into a good loop that uses the For-Next structure.
Sub GoodLoop() Dim StartVal As Integer Dim NumToFill As Integer Dim Cnt As Integer StartVal = 1 NumToFill = 100 For Cnt = 0 To NumToFill - 1 ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt Next Cnt End Sub
For-Next loops can also include one or more Exit For statements within the loop. When this statement is encountered , the loop terminates immediately and control passes to the statement following the Next statement of the current For-Next loop. The following example demonstrates use of the Exit For statement. This procedure determines which cell has the largest value in Column A of the active worksheet:
Sub ExitForDemo() Dim MaxVal As Double Dim Row As Long MaxVal = Application.WorksheetFunction.Max(Range("A:A")) For Row = 1 To 1048576 If Cells(Row, 1).Value = MaxVal Then Exit For End If Next Row MsgBox "Max value is in Row " & Row Cells(Row, 1).Activate End Sub The maximum value in the column is calculated by using the Excel MAX function, and the value is assigned to the MaxVal variable. The For-Next loop checks each cell in the column. If the cell being checked is equal to MaxVal , the Exit For statement terminates the loop and the statements following the Next statement are executed. These statements display the row of the maximum value and activate the cell.
| Note | The ExitForDemo procedure is presented to demonstrate how to exit from a For-Next loop. However, it is not the most efficient way to activate the largest value in a range. In fact, a single statement does the job: Range("A:A").Find(Application.WorksheetFunction.Max _ (Range("A:A"))).Activate |
The previous examples use relatively simple loops. But you can have any number of statements in the loop, and you can even nest For-Next loops inside other For-Next loops. Here's an example that uses nested For-Next loops to initialize a 10 — 10 — 10 array with the value “1 . When the procedure is finished, each of the 1,000 elements in MyArray contains “1 .
Sub NestedLoops() Dim MyArray(1 to 10, 1 to 10, 1 to 10) Dim i As Integer, j As Integer, k As Integer For i = 1 To 10 For j = 1 To 10 For k = 1 To 10 MyArray(i, j, k) = -1 Next k Next j Next i End Sub
DO WHILE LOOPS
This section describes another type of looping structure available in VBA. Unlike a For-Next loop, a Do While loop executes as long as a specified condition is met.
A Do While loop can have either of two syntaxes:
Do [While condition ] [instructions] [Exit Do] [instructions] Loop or Do [instructions] [Exit Do] [instructions] Loop [While condition ]
As you can see, VBA lets you put the While condition at the beginning or the end of the loop. The difference between these two syntaxes involves the point in time when the condition is evaluated. In the first syntax, the contents of the loop may never be executed. In the second syntax, the statements inside the loop are always executed at least one time.
The following examples insert a series of dates into the active worksheet. The dates correspond to the days in the current month, and the dates are entered in a column beginning at the active cell.
| Note | These examples use some VBA date- related functions:
|
The first example demonstrates a Do While loop that tests the condition at the beginning of the loop: The EnterDates1 procedure writes the dates of the current month to a worksheet column, beginning with the active cell.
Sub EnterDates1() ' Do While, with test at the beginning Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do While Month(TheDate) = Month(Date) ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop End Sub
This procedure uses a variable, TheDate , which contains the dates that are written to the worksheet. This variable is initialized with the first day of the current month. Inside of the loop, the value of TheDate is entered into the active cell, TheDate is incremented, and the next cell is activated. The loop continues while the month of TheDate is the same as the month of the current date.
The following procedure has the same result as the EnterDates1 procedure, but it uses the second Do While loop syntax, which checks the condition at the end of the loop.
Sub EnterDates2() ' Do While, with test at the end Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop While Month(TheDate) = Month(Date) End Sub
The following is another Do While loop example. This procedure opens a text file, reads each line, converts the text to uppercase, and then stores it in the active sheet, beginning with cell A1 and continuing down the column. The procedure uses the VBA EOF function, which returns True when the end of the file has been reached. The final statement closes the text file.
Sub DoWhileDemo1() Dim LineCt As Long Dim LineOfText As String Open "c:\data\textfile.txt" For Input As #1 LineCt = 0 Do While Not EOF(1) Line Input #1, LineOfText Range("A1").Offset(LineCt, 0) = UCase(LineOfText) LineCt = LineCt + 1 Loop Close #1 End Sub | CROSS-REFERENCE | For additional information about reading and writing text files using VBA, see Chapter 27. |
Do While loops can also contain one or more Exit Do statements. When an Exit Do statement is encountered, the loop ends immediately and control passes to the statement following the Loop statement.
DO UNTIL LOOPS
The Do Until loop structure is very similar to the Do While structure. The difference is evident only when the condition is tested . In a Do While loop, the loop executes while the condition is True ; in a Do Until loop, the loop executes until the condition is True .
Do Until also has two syntaxes:
Do [Until condition ] [instructions] [Exit Do] [instructions] Loop
or
Do [instructions] [Exit Do] [instructions] Loop [Until condition ]
The two examples that follow perform the same action as the Do While date entry examples in the previous section. The difference in these two procedures is where the condition is evaluated (at the beginning or at the end of the loop).
Sub EnterDates3() ' Do Until, with test at beginning Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do Until Month(TheDate) <> Month(Date) ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop End Sub Sub EnterDates4() ' Do Until, with test at end Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop Until Month(TheDate) <> Month(Date) End Sub
The following example was originally presented for the Do While loop but has been rewritten to use a Do Until loop. The only difference is the line with the Do statement. This example makes the code a bit clearer because it avoids the negative required in the Do While example.
Sub DoUntilDemo1() Dim LineCt As Long Dim LineOfText As String Open "c:\data\textfile.txt" For Input As #1 LineCt = 0 Do Until EOF(1) Line Input #1, LineOfText Range("A1").Offset(LineCt, 0) = UCase(LineOfText) LineCt = LineCt + 1 Loop Close #1 End Sub | Note | VBA supports yet another type of loop, While Wend . This looping structure is included primarily for compatibility purposes. I mention it here in case you ever encounter such a loop. Here's how the date entry procedure looks when it's coded to use a While Wend loop: Sub EnterDates5() Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) While Month(TheDate) = Month(Date) ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Wend End Sub |
EAN: 2147483647
Pages: 319