Control flow in VBA

Left unregulated, a program proceeds through its statements from beginning to end. Some very simple programs can be written with only this unidirectional flow. However, much of the power and utility of any programming language comes from the ability to change execution order with control statements and loops.

Control structures allow you to regulate the flow of your program's execution. Using control structures, you can write Visual Basic code that makes decisions or that repeats actions. Other control structures let you guarantee disposal of a resource or run a series of statements on the same object reference.

You can place control statements inside other control statements, for example an If...Then...Else block within a For...Next loop. A control statement placed inside another control statement is said to be nested.

Control structures in Visual Basic can be nested to as many levels as you want. It is a good practice to make nested structures more readable by indenting the body of each one. The automatic indenting feature ('tidy') included in Code VBA automatically does this.

Decision structures

Visual Basic lets you test conditions and perform different operations depending on the results of that test. You can test for a condition being true or false (If Then Else), for various values of an expression (Select Case), or for various exceptions generated when you execute a series of statements (GoTo, Exit).

If Then Else

In it's most basic form your code shows different behaviour depending on a condition being True or False based, for example when you are making a common separated list: 


If Len(strList) = 0 Then
    strList = strItem
Else
    strList = strList & "," & strItem
End If

Variations on the If Then Else syntax are described here.

A fast way to insert the If Then code structures From the Code VBA toolbar, select If » If Then

Select Case

The Select Case structure runs one of several groups of statements, depending on the value of an expression. It is a practical alternative for If Then Else structures when you evaluate a single expression that has several possible values. 


Dim strLetterGrade As String
Dim str4Scale As String
Select Case strLetterGrade
Case "A", "A+"
    str4Scale = "4.0"
Case "A-"
    str4Scale = "3.7"
Case "B+"
    str4Scale = "3.3"
'etcetera'
End Select

More on the Select Case syntax is described here.

Testing conditions

Both with the If Then Else above and Do Loop below, your decision to do this or that or to continue or stop will be specified in the condition part of the statement.

Boolean expressions

Conditions require a Boolean expression: an expression that evaluates to either True or False. This value can be obtained from multiple sources:

Comparison operators

Comparison operators generally specify how to compare two values or expressions and when to return either True or False as the result of their evaluation.

In case of Strings, either equal (=) or not equal (<>) can be tested. As an example, "a" <> "b" returns True. For a more nuanced comparison, e.g. if you consider "Mark" and "mark" to be sufficiently the same value, see function StrComp.

Comparison of numerical values adds the operators: less than (<), less than or equal to (<=), greater than (>) and greater than or equal to (>=).

Logical operators

Very often, your Boolean expression is complex: it is composed of multiple (boolean) subexpressions combined using logical operators And, Or and Not.

Below gives the truth values that are the result of applying the logical operators on combinations of True and False conditions:

  • Not True => False (and the reverse)
  • True And True => True (False for all other combinations)
  • False Or False => False (True for all other combinations)

It is easy to make mistakes with complex conditions. Take for example below code. What combinations of True/False values for conditions A and B will result in action 1? 


If A And Not B Then
    'Do action 1'
Else
    'Do action 2'
End If

Logical operator precedence order

The result of evaluation of a complex Boolean expression also depends on precedence order. For example, A And B Or C differs for some values of A, B and C from A And (B Or C). Try A and B False and C True in the Immediate window


?False And False Or True '-> True'
?False And (False Or True)  '-> False'

By default, logical operators are evaluated in the following order of precedence: Not, And, Or.

In the above code example, the first line, first A And B is evaluated, returning False Parentheses can be used to overwrite the order and to evaluate certain parts of the expression. Operations inside parentheses are always performed before those outside.

Loop Structures

Loops make it possible to repeat instructions a number of times. In VBA, there are three types of loop to choose from:

  • Do Loops: repeat a process up to a certain condition, where the condition can be at the begin or at the end of the loop
  • For Each loops: repeat for all items in a collection
  • For loops: repeat on a specified range of values. Also possible is starting at the end with Reversed for.

Sometimes certain conditions are met before the end of the loop, then you can use the Exit statement.

The first loop we'll look at is the Do While loop.

Do While ... loop

As long as the Condition is true, the instructions in the loop will continue to be executed.


intTotal = 1
Do While intTotal < 5
    'Instructions'
    MsgBox (intTotal)
    intTotal = intTotal + 1
Loop

In MS Access lopping through the records of a table would look like below


Dim rst As Recordset: Set rst = CurrentDb.OpenRecordset(Name:="tblOptions")
Do While Not rst.EOF
    'do something'
    rst.MoveNext
Loop

For i= To

With the For loop the loop is done from the startnumber to the endnumber, the variable i is automatically incremented by 1.


Dim i As Integer
For i = 1 To 5  'from startnumber to endnumber'
    MsgBox i    
Next

On the place of the endnumber it is possible to use a variable end number eg. with the .Count function.  Suppose we want to do an instruction for all the rows in a Worksheet. 


Dim i As Integer
Dim maxRows As Integer 
maxRows = ActiveSheet.UsedRange.Rows.Count
For i = 1 To maxRows
    MsgBox i    
Next

Reversed For Loop in VBA

In our previous examples we have only seen For loops in which the loop counter increases with 1 with each step. This is the most common use, but sometimes you need to deviate. You also can have a For Loop whose counter changes with another value than 1, or that moves from a higher value to a lower value. For this you use the 'Step' keyword. The example below shows how the Step feature can be used productively for closing all forms in MS Access. If in the below case we use 0 To Forms.Count - 1 an error occurs halfway, because with each iteration the number of open forms in the Forms collection decreases with 1.


Dim intLoop As Integer
For intLoop = (Forms.Count - 1) To 0 Step -1
    DoCmd.Close acForm, Forms(intLoop).Name
Next intLoop

Break out of loop

It is possible to exit a loop early by using the following instruction, for example if you are looking for a value and the loop can end once found. The next example shows the VBA break for loop.


Dim booValueFound As Boolean: booValueFound = False
Dim LookForValue As Integer: LookForValue = 3
Dim i As Integer
For i = 1 To 5
    booValueFound = (i = LookForValue)
    If booValueFound = True Then Exit For
    Debug.Print i    
Loop

For Each Loop

For Each... can be used for iterating a collection of objects. Here you don’t have to worry about the loop counter, you simply pass a collection of objects and the loop itself identifies the objects and iterates them.

    
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim ws As Worksheet
For Each ws In wb.Worksheets
            'Instructions'
Next ws

GoTo

The GoTo statement makes processing continue at to a specified line within the current procedure. The required line argument can be a line label or line number.

A common use of GoTo is with error handling: On Error GoTo HandleError in program makes a message appear whenever a run-time error occurs such as here 'Division by zero'.


Sub Demo()
    On Error GoTo HandleError
    Dim result
    result = 1 / 0
HandleExit:
    Exit Sub
HandleError:
    MsgBox Err.description
    Resume HandleExit
End Sub

Warning: Too many GoTo statements can make code difficult to read and debug. Use structured control statements above (Do...Loop, For...Next, If...Then...Else, Select Case) whenever possible.