Controlling Program Flow

---

VBA, as a derivative of Visual Basic, is an event-driven language, which means that the code you write is executed as a response to something that has happened, such as a button being clicked or a workbook being opened. Program execution normally flows from the first line of code down to the last line within a procedure, but there are times when this top-down flow of execution needs to be broken. VBA provides several methods for repeating certain sections of code, skipping some sections of code, and making decisions about which sections of code to execute.

Selection Statements

A key facet of most applications is the ability to make decisions. This ability allows the application to make decisions about input received from the user and any values that might be calculated.

If…Then…Else Statement

The simplest form of decision making is performed with the If…Then…Else statement. It examines an expression to see if it is true or not. If the expression is True, the code performs one set of actions; otherwise, it performs a different set of actions.

If…Then…Else statements can take two forms, either single-line or multi-line. The syntax of both forms is shown here.

If condition Then statements Else elsestatements 

or

If condition Then
    [ statements ]
[ ElseIf elseifcondition [ Then ]
    [ elseifstatements ] ]
[ Else
    [Else elsestatements] ]
End If

• condition A required expression that must evaluate to a True or False Boolean value

• statements Optional block of one or more statements that are to be executed when the condition is True

• elseifcondition A required expression if ElseIf is present that must evaluate to a True or False Boolean value

• elseifstatements Optional block of one or more statements that are to be executed when the elseifcondition is True

• elsestatements Optional block of one or more statements that are to be executed if no previous condition or elseifcondition is True

• End If A required element in multi-line form and terminates the If…Then block

Using the If…Then…Else statements requires the use of comparison operators to build the needed condition statements. The comparison operators compare two or more values and decide if the values are equal to each other or if one is greater than the other; then the operators return a True or False answer. The six comparison operators are listed in Table 4-11.

Table 4-11: Comparison Operators

Operator	Description
=	Determines if two values are equal
<	Determines if value on left side of operand is less than value on right side
>	Determines if value on left side of operand is greater than value on right side
<=	Determines if value on left side of operand is less than or equal to value on right side
>=	Determines if value on left side of operand is greater than or equal to value on right side
<>	Determines if two values are not equal to each other

The following code fragments show examples of using the If…Then…Else statement to determine if a person's age allows them to vote:

If intAge >= 18 Then
    boolAllowVote = True
Else
    boolAllowVote = False
End If
 
If boolAllowVote Then
    [Display ballot and record vote] 
End If
 
intReturn = MsgBox("Do you wish to continue?", vbYesNo + vbExclamation, "My App")
If intReturn = vbYes Then
    [Continue processing] 
Else
    [Exit Procedure] 
End If

Select Case Statements

Select Case statements allow you to check for multiple values at once. Suppose you had to calculate different values depending upon what month it was. You would need to write eleven If…Then…Else statements to check for all twelve months, but using a Select Case statement lets you drop the number of conditional statements to one, making your code easier to read and maintain.

The syntax of the Select Case statement is shown here.

Select [Case] testcondition 
    [Case expressionlist 
        [statements] ]
    [Case Else
        elsestatements 
End Select 

• testcondition Required expression that must evaluate to one of the basic data types, such as Boolean, Integer, String, and so on.

• expressionlist A list of expression clauses representing values for testexpression. Multiple expression clauses are separated by commas and can take one of the following forms:

• Expression1 To Expression2Used to represent a range of values from Expression1 to Expression2. Expression1 must be less than Expression2.

• [Is] comparisonoperator Expressioncomparisonoperator is used to specify a restriction on the value of Expression.

• ExpressionThe expressions in expressionlist can be any data type so long as they are implicitly convertible to the type of test condition.

• Statements One or more statements that are executed if the testexpression matches any clause in expressionlist.

• else statements One or more statements that are executed if the testexpression does not match any clause in expressionlist.

• End Select Required to mark the end of the Select Case block.

The following code fragment demonstrates using the Select Case statement to set a variable to the number of days in each month.

Select Case strMonth
    Case "February"
        intDays = 28
 
    Case "April", "June", "September", "November"
        intDays = 30
 
    Case "January", "March", "May", "July", "August", "October", "December"
        intDays = 31
 
End Select

This preceding code is just a simple example of specifying the number of days in a month, but does not make any provisions for leap years. More code can be added to the 'February' clause to properly set the number of days in February, as in the following code:

    Case "February"
        If (intYear Mod 100) = 0 Then
            If (intYear Mod 400) = 0 Then
                intDays = 29
            Else
                intDays = 28
            End If
        Else
            If (intYear Mod 4) = 0 Then
                intDays = 29
            Else
                intDays = 28
            End If
        End If 

This example also shows how you can mix If…Then…Else statements inside a Select Case statement.

Loops

There will be numerous times when you'll need to perform a certain task several times, so VBA provides several methods of creating loops. Loops can be divided into two categories: iteration loops and logical loops.

Iteration Loops

Iteration loops, which are used to ensure that a specific number of repetitions have occurred, have a definitive starting point and ending point. There are two iteration loops, both similar in style and syntax.

The first type of iteration loop, the For…Next loop, is normally used for counting and is particularly useful with arrays. The syntax of a For…Next loop is shown here.

For counter = start To end [Step step]
    [statements]
    [Exit For]
    [statements]
Next counter 

• counter Required numeric variable that is used as a counter. It can't be a Boolean or member of an array.

• start Required value used as the starting point of the array.

• end Required value used as the ending point of the array.

• step Optional value by which the counter should be incremented during each iteration of the loop. Step value can be a positive or negative value. Default value is 1.

• statements One or more optional statement lines that are executed during each iteration of the loop.

• Exit For Optional statement used to exit the loop prematurely. Code execution resumes at the first line following the Next counter statement.

• Next counter Required statement marking the end of the For…Next loop.

You can omit the counter variable used within the Next statement, but it isn't recommended. By including the counter, you add an extra level of protection against programming errors.

As stated earlier, a For…Next loop ensures that a specific number of repetitions are performed. Suppose you had an array of 26 elements, and you wanted to set each one to its corresponding letter of the alphabet. A For…Next loop provides the perfect means to accomplish this. The following code creates a 26-member array, assigns a letter of the alphabet to each element, and then builds a message box to display those elements.

Sub AlphabetArray()
Dim strABC(1 To 26) as String
Dim intCounter as Integer
Dim strPrompt as String
For intCounter = 1 to 26
    strABC(intCounter) = Chr$(intCounter + 64)
Next intCounter
strPrompt = "The strABC array contains the following values:" & vbCrLf
For intCounter = 1 to 26
    strPrompt = strPrompt & strABC(intCounter)
Next intCounter
MsgBox strPrompt
End Sub

For…Next loops can be nested inside one another, so you can build even more complex iterations. The following example modifies the previous example by building a two-dimensional array and displaying the elements of the array backward:

Dim strABC(100 To 101, 1 To 26) As String
Dim intCounter1 As Integer, intCounter2 As Integer
Dim strPrompt As String
For intCounter1 = 100 To 101
    For intCounter2 = 1 To 26
        strABC(intCounter1, intCounter2) = Chr$(intCounter2 + 64)
    Next intCounter2
Next intCounter1
strPrompt = "The strABC array contains the following values:"
 
For intCounter1 = 100 To 101
    strPrompt = strPrompt & vbCrLf & "Dimension" & Str$(intCounter1) & ": "
    For intCounter2 = 26 To 1 Step -1
        strPrompt = strPrompt & strABC(intCounter1, intCounter2)
    Next
Next intCounter1
MsgBox strPrompt

The other iteration loop, the For Each…Next loop, is used with collections of objects or arrays, ensuring that each member of the group is touched upon. It has syntax very similar to the For…Next loop.

For Each element In group 
    [statements]
    [Exit For]
    [statements]
Next element 

• element Required object or variant variable that is used to point to each member of the group. Array loops require a variant variable regardless of the data type of the array.

• group Required collection of objects or array containing the elements that will be affected by the loop.

• statements One or more optional statement lines that are executed during each iteration of the loop.

• Exit For Optional statement used to exit the loop prematurely. Code execution resumes at the first line following the Next counter statement.

• Next element Required statement marking the end of the For…Next loop.

The For Each…Next loop is a handy method of performing the same action to a collection of objects. (You will learn more about object collections and how to work with them in Chapter 6, Chapter 7, and Chapter 8.) If you wanted, for example, to rename all the worksheets in a workbook, you could use a For Each…Next loop to ask the user for a name for each worksheet, rename it, and continue on to the next one until all of the worksheets were renamed.

Sub RenameAllWorksheets()
Dim myWorksheet As Worksheet
Dim strPrompt As String, strResult As String
Dim intCounter As Integer
 
intCounter = 0
strPrompt = "Please enter the new name for worksheet "
For Each myWorksheet In Application.Worksheets
    strResult = InputBox(strPrompt & myWorksheet.Name)
    myWorksheet.Name = strResult
    intCounter = intCounter + 1
Next myWorksheet
strPrompt = "Total worksheets renamed =" & Str$(intCounter)
MsgBox strPrompt
End Sub

Logical Loops

Logical loops have no predetermined number of iterations. Instead, they rely on a logical expression that tests for a particular condition and then repeat the loop until the condition is either met or cleared, depending upon the type of loop.

Although there are four forms of logical loops, they can be simplified into two styles: those that test the condition before performing an action and those that test the condition after performing an action. Within each style, the two loops differ in that one loops when the condition is true and the other loops when the condition is false.

The Do While…Loop and Do Until…Loop both test the condition before performing the action within the loop. The difference between the two is that Do While loops perform the action when the condition is true and Do Until loops perform the action while the condition is false. To decide which one to use, you need to find the simplest way to express the condition and then pick the loop that will best match the condition.

As you can see in the following code, the syntax for the two loops is straightforward:

Do While condition 
[statement]
[Exit Do]
[statement]
Loop

and

Do Until condition 
[statement]
[Exit Do]
[statement]
 Loop 

• condition A required numeric or string expression that must evaluate to True or False

• statement One or more optional statement lines that are executed during the loop

• Exit Do Optional statement to exit the loop prematurely

• Loop Required statement to mark the end of the Do While or Do Until statement block

The Do While loop tests the condition first, before entering the loop, and executes while the condition is true. This example performs a simple counting loop, similar to a For…Next statement:

Dim intCounter as Integer
intCounter = 1
Do While intCounter <= 10
    intCounter = intCounter + 1
Loop

The following Do Until loop performs the same actions as the Do While loop, but notice how the condition expression has changed:

Dim intCounter as Integer
intCounter = 1
Do Until intCounter = 11
    intCounter = intCounter + 1
Loop

Both examples run a simple counting loop from 1 to 10. In these two examples, it is easier to figure out that the Do While loop is counting from 1 to 10. The Do Until loop appears to be counting to 11, but the action is not performed once the counter reaches 11. It is last performed with a value of 10.

The Do…Loop While and Do…Loop Until loops are similar to each other, just as they are similar to the Do While and Do Until loops mentioned earlier. Both check the conditional expression at the end of the loop, guaranteeing at least one pass through the loop. Again, Do…Loop While loops while the condition is true, and Do…Loop Until loops while the condition is false. The syntax declarations for them are shown here:

Do
[statement]
[Exit Do]
[statement]
Loop While condition 

and

Do
[statement]
[Exit Do]
[statement]
Loop Until condition 

• condition Required expression that must evaluate to True or False

• statement One or more optional statement lines that are executed during the loop

• Exit Do Optional statement to exit the loop prematurely

• Loop Required statement to mark the end of the Do While or Do Until statement block

The following example increments a counter during each pass through the loop and uses a message box to ask the user if they wish to continue:

Sub PermissionLoop()
Dim intCounter As Integer, strPrompt As String
Dim intResult As Integer
 
intCounter = 0
Do
    intCounter = intCounter + 1
    strPrompt = "You have looped" & Str$(intCounter) & " times." _
        & vbCrLf & "Do you wish to continue?"
    intResult = MsgBox(strPrompt, vbYesNo + vbExclamation)
Loop While intResult = vbYes
End Sub

And here is the same example, using the Do…Loop Until statement:

Sub PermissionLoop2()
Dim intCounter As Integer, strPrompt As String
Dim intResult As Integer
 
intCounter = 0
Do
    intCounter = intCounter + 1
    strPrompt = "You have looped" & Str$(intCounter) & " times." _
        & vbCrLf & "Do you wish to continue?"
    intResult = MsgBox(strPrompt, vbYesNo + vbExclamation)
Loop Until intResult = vbNo
End Sub 

GoTo Statement

The GoTo statement forces the procedure to change immediately to another section of the procedure unconditionally. The section of code that is to be branched to needs to be marked with either a line number or a line label, allowing the program to jump either forward or backward within the procedure. Using the GoTo statement is discouraged because it can cause procedures to become more difficult to understand and debug.

The syntax for the GoTo statement is shown here:

GoTo line 

In the preceding code, line is a required line label that must be a line number or a line label that is defined within the same procedure. Line numbers can be any series of digits and must begin in the first column. Line labels can be any series of characters that starts with a letter and ends with a colon (:). Line labels are not case sensitive and must begin in the first column.

Because GoTo statements can lead to unstructured code, using them is strongly discouraged. If used sparingly, they can be very useful. The following section on error handling offers better understanding of when and how to use the GoTo statement and line labels.

---