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
- Testing conditions
- Loop structures
- Exit Procedure
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
strList = strList & "," & strItem
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
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"
str4Scale = "3.7"
str4Scale = "3.3"
More on the Select Case syntax is described here.
Boolean expressionsConditions require a Boolean expression: an expression that evaluates to either True or False. This value can be obtained from multiple sources:
- a Boolean variable with a value previously assigned,
- a comparison, e.g.
strRole = "Admin"or
bytAge > 21,
- a standard function that returns a value, for example when reading input from a file the
- an object property, e.g. ActiveWorkbook.HasPassword
- a custom function you wrote, e.g. FileExists
- a combination of the above choices using logical 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 (>=).
Very often, your Boolean expression is complex:
it is composed of multiple (boolean) subexpressions combined using logical operators
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'
'Do action 2'
Logical operator precedence order
The result of evaluation of a complex Boolean expression also depends on precedence order.
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:
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.
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
intTotal = intTotal + 1
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
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'
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
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
Early exit from a loop
It is possible to exit a loop early by using the following instruction, usable if you are looking for a value and the loop can end once found. The next example shows the exit in a for-loop.
booValueFound = False
For i = 1 To 5
If booValueFound = True Then Exit For
MsgBox i 'Instructions'
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
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'.
On Error GoTo HandleError
result = 1 / 0
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.