Module 3 Loops and conditional statements

This module gives an introduction to loops and conditional statements. Loops are used to repeat code and conditional statements are used to redirect code execution based on the conditions. Both are basic building blocks in programming.

A template with VBA code is given in the file vba-template.xlsm (open it and use it while reading the notes). Have a look inside the module TM3_ex in the VBA editor for examples used in the notes and during lectures. Have a look at module TM3_exercises for exercises. Guiding answers for the exercises can be found in the file vba-solution.xlsm .

Learning path diagram

Click/hover the nodes to follow links and see details.

If you like a different learning style there is a lot for videos about VBA online such as course 14-Hour VBA Course. The videos have been pointed out as online supplements in the learning path diagram. However, they are not necessary for the course. Since Excel change as new versions arrive and it work on different operating systems, the look in the screenshots and videos might be a bit different from your version. However, you still should be able to understand it.

3.1 Learning outcomes

By the end of this module, you are expected to be able to:

  • Describe what a conditional statement is.
  • Test a condition built using relational/logical operators.
  • Declare a conditional statement.
  • Describe what a loop is.
  • Declare a loop.
  • Declare nested loops.
  • Exit a loop.

The learning outcomes relate to the overall learning goals number 1, 2, 4, 8, 9-12 and 16 of the course.

3.2 Relational and logical operators

Often you will need to compare a variable with another one. For this you need the relational operators given in Table 3.1 (also called comparison operators).

Table 3.1: Comparison/relational operators.
Operator Description Example
= Equal to. A = B ’ False
<> Not equal to A <> B ’ True
> Greater than. A > B ’ False
< Less than. A < B ’ True
>= Greater than or equal to. A >= B ’ False
<= Less than or equal to. A <= B ’ True
Assume that A = 2 and B=4.

Let us consider an example (try to guess the output before running the procedure):

'' Comparison of two variables
Sub TM3_Comparison()
   Dim intA As Integer
   Dim intB As Integer

   intA = 10
   intB = 20
   
   If intA = intB Then
      MsgBox ("A = B is True")
   Else
      MsgBox ("A = B is False")
   End If
   
   If intA <> intB Then
      MsgBox ("A not equal B is True")
   Else
      MsgBox ("A not equal B is False")
   End If

   If intA > intB Then
      MsgBox ("A greter then B is True")
   Else
      MsgBox ("A greter then B is False")
   End If

   If intA <= intB Then
      MsgBox ("A less than or equal to B is True")
   Else
      MsgBox ("A less than or equal to B is False")
   End If
End Sub

Given two boolean expressions we use logical operators to compare them (see Table 3.2)

Table 3.2: Logical operators.
Operator Description Example
AND If both the conditions are True, then the expression is true. A<>0 AND B<>0 ’ False
OR If any of the two conditions are True, then the expression is true. A<>0 OR B<>0 ’ True
NOT Reverse logical: if the expression is true, then the NOT operator returns false. NOT(A<>0 OR B<>0) ’ False
XOR Logical Exclusion. If exactly one condition is True, the result is True. A<>0 XOR B<>0 ’ True
Assume that A = 0 and B=4.

Let us consider an example (try to guess the output before running the procedure):

Sub TM3_Logical()
    If 5 > 4 And 6 > 2 Then
        MsgBox ("5 > 4 And 6 > 2 is True")
    Else
        MsgBox ("5 > 4 And 6 > 2 is False")
    End If
    
    If 1 > 4 Or 1 > 2 Then
        MsgBox ("1 > 4 Or 1 > 2 is True")
    Else
        MsgBox ("1 > 4 Or 1 > 2 is False")
    End If
    
    If 6 > 4 Or 1 > 2 Then
        MsgBox ("6 > 4 Or 1 > 2 is True")
    Else
        MsgBox ("6 > 4 Or 1 > 2 is False")
    End If
    
    If 5 > 4 And Not 6 > 2 Then
        MsgBox ("5 > 4 And Not 6 > 2 is True")
    Else
        MsgBox ("5 > 4 And Not 6 > 2 is False")
    End If
    
    ' If more than two boolean expressions remember parenthesis
    If (5 > 4 Xor 6 > 2) And 7 > 10 Then   ' Xor (exactly one is true)
        MsgBox ("(5 > 4 Xor 6 > 2) And 7 > 10 is True")
    Else
        MsgBox ("(5 > 4 Xor 6 > 2) And 7 > 10 is False")
    End If
    
    If 5 > 4 Xor (6 > 2 And 7 > 10) Then
        MsgBox ("5 > 4 Xor (6 > 2 And 7 > 10) is True")
    Else
        MsgBox ("5 > 4 Xor (6 > 2 And 7 > 10) is False")
    End If
End Sub

Note parentheses have an impact on the result. Remember to use them correctly.

3.3 Loops

Loops are used to repeat pieces of code. There are many types of loops statements but here we will consider For and While loops. The structure of a For loop is:

For i = 1 To 10 
    <code>
Next

Here i is a counter used to repeat the code inside the loop 10 times. In general we do not use a suffix for counter variables (i should have been named intI according to our naming convention). An example on a simple for loop is:

Sub TM3_Loop1()
    Dim i As Integer
    
    For i = 1 To 3
        MsgBox (i) ' What will the output be?
    Next
End Sub

You can use the Step keyword to increment the counter by more than one:

Sub TM3_Loop2()
    Dim i As Integer
    
    For i = 2 To 9 Step 2
        If i <> 4 Then
            MsgBox (i) ' What will the output be?
        End If
    Next
End Sub

You can use Exit For to end a for loop prematurely (jump to the code after the loop):

'' Write the row number in column A and exit after row 10 even though the loop runs to 20
Sub TM3_WriteNumbers1()
   Dim r As Integer
   
   Worksheets("TM3").Activate  ' activate the sheet we want to use
   For r = 6 To 20
      If r > 10 Then
         Exit For
      End If
      Cells(r, 1) = r  ' write to row r, col 1 (A)
   Next
End Sub

Loops may be nested inside each other. For instance if some action needs to be performed for each day and each employee or for each project and each work package of that project.

Sub TM3_NestedLoops()
    Dim i As Integer
    Dim j As Integer
    
    For i = 1 To 2
        For j = 1 To 3
            MsgBox ("(" & i & "," & j & ")")
        Next
    Next
End Sub

The structure of a While loop is:

Do While <condition true>  
    <code>
Loop

Here the loops runs until the condition is not true. While loops are useful when you do not know how many times to do the loop in advance. An example on a simple while loop is:

'' Write the row number in column B and exit after row 10
Sub TM3_WriteNumbers2()
    Dim r As Integer
    
    Worksheets("TM3").Activate
    r = 6
    Do While r < 11
        Cells(r, 2) = r ' write to row r, col 2 (B)
        r = r + 1
    Loop
End Sub

You can use Exit Do to end a while loop prematurely (jump to the code after the loop):

'' Write 2, 4, ... in column C and exit after 21 or if equals 12
Sub TM3_WriteNumbers3()
    Dim i As Integer, r As Integer
    
    Worksheets("TM3").Activate
    r = 6
    i = 2
    Do While i < 21
        Cells(r, 3) = i ' write to row r, col 3 (C)
        If i = 12 Then
            Exit Do
        End If
        r = r + 1
        i = i + 2
    Loop
End Sub

Beware of endless loops. If the stopping criteria is NOT reached when using a while loop, the computer will keep going:

' An endless loop. Do not run if you don't know how to stop
Sub TM3_EndlessLoop()
    Dim i As Integer
    While i >= 0
        i = i + 1
    Wend
End Sub

An endless loop can be hard to stop depending on the operating system you use. Therefore always “save” before you “run” the code. Make sure the stopping criterion will be reached. You may try to stop the program using a shortcut ( try Ctrl + Break or Ctrl + Alt + Delete. try ⌘., ⌃ + Esc or ⌘⌥ + Esc.)

Finally, the For Each loop has to be mentioned:

Sub TM3_ForEach()
   Dim rngC As Range
   Dim i As Integer
   
   Worksheets("TM3").Activate
   i = 1
   For Each rngC In Range("D6:E9")
      rngC = i
      i = i + 1
   Next
End Sub

The loop is used for running trough a set of objects (we will have a closer look at objects in Section 5.3). Here rngC is used to run through all the cells in the range and set values. Note a range is scanned left-down.

3.4 Conditional statements

Conditional statements are used to redirect code execution based on the conditions. If the condition is met then the code is executed. The general layout of an if-then-else conditional statement is:

If <condition> Then
 <code>
ElseIf <condition> Then
 <code>
ElseIf <condition> Then
 <code>
Else
 <code>
End If

If <condition> Then <code> Else <code>  ' single line form

You can drop the ElseIf and Else code chunks. Other conditional statements exists but in general you can formulate them using an if-then-else statement. Let us try to use a conditional statement to separate persons in two groups:

'' Seperate persons into two groups (names are written in 2 columns)
Sub TM3_SeparatePersons1()
    Dim r As Integer
    
    Worksheets("TM3_Separate1").Activate ' activate the correct sheet
    For r = 2 To 12 ' scan rows 2-12
        If Cells(r, 2) = "Professor" Then
            Cells(r, 3) = Cells(r, 1)  ' output in row C
        Else
            Cells(r, 4) = Cells(r, 1)  ' output in row D
        End If
    Next
End Sub

We use variable r to store the row number we want to write to and then an if statement to separate professors from others. The output will be:

Separate into two groups.

Figure 3.1: Separate into two groups.

If you want to separate both professors and associate professors from others, you may modify the if statement and use an ElseIf:

'' Seperate persons into 3 groups (names are written in 3 columns)
Sub TM3_SeparatePersons2()
    Dim r As Integer
    
    Worksheets("TM3_Separate2").Activate ' activate the correct sheet
    For r = 2 To 12
        If Cells(r, 2) = "Professor" Then
            Cells(r, 3) = Cells(r, 1)
        ElseIf Cells(r, 2) = "Associate Professor" Then
            Cells(r, 4) = Cells(r, 1)
        Else
            Cells(r, 5) = Cells(r, 1)
        End If
    Next
End Sub

Finally, let us try to separate into five groups:

'' Seperate persons into 5 groups (names are written in 5 columns)
Sub TM3_SeparatePersons3()
    Dim r As Integer
    
    Worksheets("TM3_Separate3").Activate ' activate the correct sheet
    For r = 2 To 12
        If Cells(r, 2) = "Professor" Then
            Cells(r, 3) = Cells(r, 1)
        ElseIf Sheet1.Cells(r, 2) = "Associate Professor" Then
            Cells(r, 4) = Cells(r, 1)
        ElseIf Sheet1.Cells(r, 2) = "Post Doc" Then
            Cells(r, 5) = Cells(r, 1)
        ElseIf Sheet1.Cells(r, 2) = "PhD student" Then
            Cells(r, 6) = Cells(r, 1)
        Else
            Cells(r, 7) = Cells(r, 1)
        End If
    Next
End Sub

Here the output will be:

Separate into five groups.

Figure 3.2: Separate into five groups.

3.5 Example - Find Jen

Consider column A in Figure 3.2. Assume we want to check if Jen is in a name and output her position. We can use a for loop for this:

'' Find cell with Jen using a For loop
Sub TM3_FindJen1()
    Dim r As Integer
    
    Worksheets("TM3_Separate1").Activate ' activate the correct sheet
    For r = 2 To 12
        If InStr(Cells(r, 1), "Jen ") > 0 Then  ' InStr returns first char position at which match is found (0 if no match)
            MsgBox "Jen is a " & Cells(r, 2) & " (Row " & r & ")"
            Exit For   ' exit the loop
        End If
    Next
End Sub

We scan all rows for Jen and return her position. If we found her, then we exit the for loop (no need to search further). What happens if we search for "Jen" and not "Jen "?

Search for Jen.

Figure 3.3: Search for Jen.

The same can be done using a while loop:

'' Find cell with Jen using a While loop
Sub TM3_FindJen2()
    Dim r As Integer
    
    r = 2
    Do While InStr(Cells(r, 1), "Jen ") = 0
        r = r + 1
    Loop
    MsgBox "Jen is a " & Cells(r, 2) & " (Row " & r & ")"
End Sub

Beware of endless looping here. What happens if Jen is not present in column A? A more error safe while loop is:

'' Find cell with Jen using a While loop and better stopping criteria
Sub TM3_FindJen3()
    Dim r As Integer
    
    r = 2
    Do While InStr(Cells(r, 1), "Jen ") = 0 And r < 13
        r = r + 1
    Loop
    If (r = 13) Then
        MsgBox ("Jen not found")
    Else
        MsgBox "Jen is a " & Cells(r, 2) & " (Cell A" & r & ")"
    End If
End Sub

3.6 Example - A distance matrix

Assume that you have a set of \(n=10\) locations:

Table 3.3: A set of locations
Location number \(x\)-coordinate \(y\)-coordinate
1 6 1
2 1 5
3 6 3
4 7 4
5 4 6
6 4 7
7 5 2
8 1 4
9 4 2
10 6 5

The euclidean distance \(d\) between location \(l_1 = (x_1, y_1)\) and \(l_2 = (x_2, y_2)\) are: \[d(1,2)=\sqrt{(x_1-x_2)^2 + (y_1-y_2)^2}.\] In VBA the function becomes:

'' Calculate distance between two points
'
' @param x1 x-coordinate of first point.
' @param y1 y-coordinate of first point.
' @param x2 x-coordinate of second point.
' @param y2 y-coordinate of second point.
Function TM3_Distance(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double
    Dim x As Double
    Dim y As Double
    
    x = x1 - x2
    y = y1 - y2
    TM3_Distance = Sqr((x * x) + (y * y))
End Function

Assume that you want to calculate the distance matrix \(D\) where entry \((i,j)\) contains the distance between location \(i\) and location \(j\).

Consider the locations in columns B and C:

Distance matrix worksheet.

Figure 3.4: Distance matrix worksheet.

We want to fill out the cells F1:P11 with the distances. This have been done using procedure:

'' Create a distance matrix starting in column 6
'
' @pre Assume that coordinates are stored in column B and C starting from row 2
'      and that number of points are stored in E1.
Public Sub TM3_MakeDistanceMatrix()
    Dim n As Integer
    Dim i As Integer
    Dim j As Integer
      
    n = Range("E1")
    For i = 1 To n  ' add row and column headers
        Cells(i + 1, 6) = Cells(1 + i, 1) ' row equals i+1
        Cells(1, 6 + i) = Cells(1 + i, 1) ' column equals i+6
    Next
    
    ' add distances
    For i = 1 To n
        For j = 1 To n
           Cells(i + 1, j + 6) = TM3_Distance(Cells(i + 1, 2), Cells(i + 1, 3), Cells(j + 1, 2), Cells(j + 1, 3))
        Next
    Next
End Sub

First, row and column headers are written to the cells. Next, we use a nested for loop to calculate the distances and output them to the cells. Note we in fact calculate the same distance two times (the distance from \(i\) to \(j\) equals the distance from \(j\) to \(i\)). Since we have symmetric distances there is no need to do this and it can be avoided by letting the inner loop in the nested loops be dependent on the outer loop:

'' Create a symetric distance matrix with only the upper right part filled starting in column 6.
'
' @pre Assume that coordinates are stored in column B and C starting from row 2
'      and that number of points are stored in E1.
Public Sub TM3_MakeSymetricDistanceMatrix()
    Dim n As Integer
    Dim i As Integer
    Dim j As Integer
      
    n = Range("E1")
    For i = 1 To n
        Cells(i + 1, 6) = Cells(1 + i, 1)
        Cells(1, 6 + i) = Cells(1 + i, 1)
    Next
    
    For i = 1 To n
        For j = i + 1 To n
           Cells(i + 1, j + 6) = TM3_Distance(Cells(i + 1, 2), Cells(i + 1, 3), Cells(j + 1, 2), Cells(j + 1, 3))
        Next
    Next
End Sub
Distance matrix worksheet with symmetric distances.

Figure 3.5: Distance matrix worksheet with symmetric distances.

3.7 Recap

  • Loops are used to repeat pieces of code.

  • For loops (repeat a number of times):

    For i = 1 To 10 
    <code>
Next

  • While loops (repeat until a condition is met):

    Do While <condition> ' repeat while true     
    <code>
Loop

  • Use Exit for and Exit Do to break a For and a Do While loop before it ends (jump to the code after the loop).

  • Loops may be nested inside each other:

    For i = 1 To 2     
  For j = 1 To 3  
      MsgBox ("(" & i & "," & j & ")")
  Next
Next

  • Conditional Statements are used to make decisions based on the conditions. If the condition is met then the code is executed.

  • An if-then-else statement:

    If <condition> Then
 <code>
ElseIf <condition> Then
 <code>
Else
 <code>
End If

    You may drop the ElseIf and Else code chunks.

You may also have a look at the slides for this module .

3.8 Exercises

Below you will find a set of exercises. Always have a look at the exercises before you meet in your study group and try to solve them yourself. Are you stuck, see the help page. Some of the solutions to each exercise can be seen by pressing the button at each question. Beware, you will not learn by giving up too early. Put some effort into finding a solution!

A template with VBA code is given in the file vba-template.xlsm (open it and use it while reading the notes). Have a look inside the module TM3_ex in the VBA editor for examples used in the notes and during lectures. Have a look at module TM3_exercises for exercises. Guiding answers for the exercises can be found in the file vba-solution.xlsm .

3.8.1 Exercise - Loops

Use the sheet TM3 for output.

  1. Create a for loop that writes numbers 1 to 4 in rows 25 to 28 in column A.
  2. Create a do while loop that writes numbers 1 to 4 in rows 25 to 28 in column B.
  3. Create a loop that writes numbers 1 to 4 in rows 27 to 30 in column C.
  4. Create a loop that writes numbers -1 to -4 in rows 25 to 28 in column D.
  5. Create a loop that writes numbers 1 to 4 in rows 28 to 31 in column E, except if the number is 3 then the output should to a string missing.
  6. Create a do while loop that writes numbers i = 1, 2, … in column F (starting in row 25) until i/5 + 3 = 8. Hint: you may use a Exit Do to quit the loop.
  7. Create a sub that runs all the loops.

3.8.2 Exercise - Conditional statements

Consider worksheet TM3_Numbers, which contains a set of numbers.

  1. Create a procedure with the following features
    • Make a copy of the numbers with the upper left cell starting in G1.
    • Scan all the numbers and remove (clear the cell) all the negative numbers (you may use a For Each loop).
    • Highlight all the numbers above 20 (using e.g. rngC.Interior.ColorIndex = 37).
    • Add a button to worksheet TM3_Numbers that run the procedure.
  2. Create a procedure with the following features:
    • Scan the numbers and find
      • the sum of all non-negative numbers,
      • the mean of all negative numbers.
    • Use a message box to display the sum and mean calculated.
    • Add a button to worksheet TM3_Numbers that run the procedure.

This exercise is a slightly modified version an exam assignment (reexam 2022-A5).