10  Loops

Loops are control structures that allow us to execute a block of code repeatedly. Code within loops can be executed either

In VBA, there are four main loop structures:

Most of these structures can be used interchangeably, but each has its own use case.

10.1 Elements of a Loop

  • Initialization. Set the initial value of the iteration variable. For example, i = 1, count = 0, etc.
  • Stop Condition. Define the condition that stops the loop. For example, i <= 10, count < 5, etc.
  • Increment/Decrement. Update the iteration variable to move to the next iteration. For example, i = i + 1, count = count + 1, etc.
What Is Happening Inside the Loop?

Loops add complexity to the program control flow. To better understand the execution sequence:

  • Track your code line by line using Step into... (press F8).
  • Check what each variable holds in the Locals window.

10.2 Common Applications of Loops

In the following, we will cover the different applications of loops in VBA. Consider coming back to these examples after learning the details of each loop structure.

10.2.1 Iterating Over Arrays or Collections

Loops allow you to process each element in an array or collection. For example:

  • Processing each cell in a range.
  • Iterating over a list of numbers.
Option Explicit

Sub LoopOverArrayExample()
    
    'Array of numbers
    Dim numbers(1 To 5) As Integer
    numbers(1) = 10
    numbers(2) = 20
    numbers(3) = 30
    numbers(4) = 40
    numbers(5) = 50
    
    'Iteration variable
    Dim i As Integer
    
    'Initialization and stop condition
    For i = 1 To 5
        
        Debug.Print "Number: " & numbers(i)
    
    Next i 'Increment iteration variable (i = i + 1)
    
End Sub

10.2.2 Calculating Aggregates

Each iteration can update a variable to calculate the aggregate. For example:

  • Summing all numbers in a range.
  • Counting the number of cells with a specific value.
Option Explicit

Sub SummaryStatisticsExample()
    
    'Calculate summary statistics for the numbers in cells A1:A10

    'Worksheet object
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    'Range object
    Dim rng As Range
    Set rng = ws.Range("A1:A10")

    'Iteration variable
    Dim i As Integer

    'Variables to store summary statistics
    Dim sum As Double
    Dim count As Integer
    Dim average As Double


    'Initialization
    sum = 0
    count = 0

    'Stop condition (loop while i <= 10)
    For i = 1 To 10

        'Check if cell is not empty
        If Not IsEmpty(rng.Cells(i, 1)) Then

            'Increment count
            count = count + 1

            'Add cell value to sum
            sum = sum + rng.Cells(i, 1).Value

        End If
    
    Next i 'Increment iteration variable (i = i + 1)

    'Calculate average
    average = sum / count

    Debug.Print "Sum:", sum
    Debug.Print "Count:", count
    Debug.Print "Average:", average

    
End Sub

10.2.3 Data Validation and Input

Loops can repeatedly prompt for user input until a valid value is entered. For example:

  • Repetitively asking for numbers within a range.
  • Repetitively asking for the correct input format of a password or list of arguments.
Option Explicit

Sub DataValidationExample()
    
    'User input
    Dim userInput As Integer
    
    'Stop condition (loop while userInput is not in the range 1 to 100)
    Do
        userInput = InputBox("Enter a number between 1 and 100:")
    Loop While userInput < 1 Or userInput > 100
    
    Debug.Print "Valid Input:", userInput
    
End Sub

10.2.4 Searching and Filtering

Loops can be used to search for specific elements in a collection. For example:

  • Finding the first occurrence of a value in a range.
  • Filtering elements that meet a specific condition.
  • Searching for the maximum or minimum value in a range.
  • Finding the index of a specific element in an array.
Option Explicit

Sub SearchMaxNumberInMatrixExample()

    'Iteration variables
    Dim row As Integer
    Dim col As Integer

    'Matrix dimensions
    Dim numRows As Integer
    Dim numCols As Integer

    'Matrix in a 2D array (cells in a 3x3 range)
    numRows = 3
    numCols = 3

    'Initialization of the maximum number
    'Assuming the first cell has the maximum value
    Dim maxNumber As Double
    maxNumber = Cells(1, 1).Value

    'Loop cells in spreadsheet
    For row = 1 To numRows
        For col = 1 To numCols

            'Check if cell value is greater than the current max
            If Cells(row, col).Value > maxNumber Then
                maxNumber = Cells(row, col).Value
            End If

        Next col
    Next row

    Debug.Print "Max Number:", maxNumber

End Sub

10.2.5 Generating Patterns or Sequences

With loops, the same code block can be executed multiple times to generate patterns or sequences. If the sequence obeys a logic that depends on the iteration number, loops are a good choice. For example:

  • Calculating the factorial of a number (1 * 2 * 3 * … * n).
  • Generating a sequence of prime numbers.
Option Explicit

Sub GeneratePrimeNumbersExample()

    'Iteration variables
    Dim i As Integer
    Dim j As Integer

    'Stop condition (loop while i <= 100)
    For i = 2 To 100

        'Assume i is prime
        Dim isPrime As Boolean
        isPrime = True

        'Check if i is divisible by any number from 2 to i - 1
        For j = 2 To i - 1
            If i Mod j = 0 Then
                isPrime = False
                Exit For
            End If
        Next j

        'If i is prime, print it
        If isPrime Then
            Debug.Print "Prime Number:", i
        End If

    Next i

End Sub

10.2.6 Repeating Actions

Loops can be used to repeat actions or apply the same operation to multiple elements. For example:

  • Sending emails to a list of recipients.
  • Applying a pixel transformation to an image.
Option Explicit

Sub SendEmail(email As String, subject As String, body As String)
    'Code to send an email
    Debug.Print "Sending email to:", email
    Debug.Print "Subject:", subject
    Debug.Print "Body:", body
End Sub

Sub SendEmailsExample()

    'Loop over a list of names and emails in cells A1:B10
    Dim i As Integer

    For i = 1 To 10
        Dim name As String
        Dim email As String

        name = Cells(i, 1)
        email = Cells(i, 2)

        SendEmail email, "Subject", "Hello " & name & ", this is a test email."

    Next i

End Sub

10.3 Looping Statements

In the following sections, we will cover the different loop structures in VBA. These structures allow you to repeat a block of code a specified number of times or until a certain condition is met.

Loops Across Programming Languages

The loop structures in VBA are similar to those in other programming languages. If you learn loops in VBA, you will be able to apply the same concepts in other languages like Python, Java, or C++.

10.4 For…Next Loop (Definite)

When you know in advance how many times you want to execute a block of code, you can use a For loop. In this structure, the loop configuration, namely the initialization, stop condition, and increment, are defined in a single line, at the beginning of the loop. In Figure 10.1, we have the structure of a For loop. Notice that the condition is checked at the beginning of the loop. Then, the loop body is executed if the condition is True and the iteration variable is updated before checking the condition again.

graph LR
  Start((Start))-->For
  For --> LoopStart[Initialize<br>iteration variable<br>Define Stop Condition and <br>Configure Increment]
  LoopStart-->Condition{Condition}
  Condition-->|True|Statement1["`**Loop Body**
  Statement 1
  Statement 2
  ...
  Statement N`"]
  Statement1 --> ForNext["Update iteration
                          variable"]
  ForNext --> Condition
  Condition{Condition} ---->|False| End((End))
Figure 10.1: Structure of a For loop. The loop is configured at the beginning, with the initialization, stop condition, and increment. The condition is checked at the beginning of the loop. Then, the loop body is executed if the condition is True. If the condition is False, the loop is exited. After executing the loop body, the iteration variable is updated before checking the condition again.

In Listing 10.1, we have a For loop that prints the numbers from 1 to 5.

Listing 10.1: A For loop that prints the numbers from 1 to 5. First, the iteration variable i is initialized to 1. Then, the stop condition is defined as i <= 5 and i is set to increment by 1 in each iteration (keyword Step 1). The loop body prints the value of i and increments i by 1. The loop stops when i is equal to 5. 
Option Explicit

Sub ForLoopIncrementExample()
      
    'Iteration variable
    Dim i As Integer
    
    'Initialization: i = 1
    'Stop condition: i <= 5
    'Increment: i = i + 1
    For i = 1 To 5 Step 1
        
        Debug.Print "Iteration " & i
        
    'Update iteration variable by 1 (i = i + 1)
    Next i
End Sub

10.4.1 Using Step in For Loop

The Step keyword allows you to specify the increment or decrement value. For example, in the code below, Step 2 will increment the loop variable by 2 each time:

Option Explicit

Sub ForLoopIncrementWithStepExample()
    
    'Iteration variable
    Dim i As Integer
    
    'Stop condition (loop while i <= 10)
    For i = 1 To 10 Step 2
        
        Debug.Print "Iteration " & i
    
    'Update iteration variable according to step value (i = i + Step)
    Next i

End Sub

If you want to decrement the loop variable, you can use a negative value for Step.

In the example below, the loop will decrement by 1 each time:

Option Explicit

Sub ForLoopDecrementExample()

    'Iteration variable
    Dim i As Integer
    
    'Initialization: i = 5
    'Stop condition: i >= 1
    'Decrement: i = i - 1
    For i = 5 To 1 Step -1
        
        Debug.Print "Iteration " & i
        
    'Update iteration variable according with Step value (i = i + Step)
    Next i

End Sub

10.4.2 Nested Loops

Nested loops are loops within loops. They are useful when you need to perform multiple iterations based on different conditions.

When using nested loops, the inner loop will complete all its iterations before the outer loop moves to the next iteration. For example, in the code below, the inner loop will run 3 times for each iteration of the outer loop.

The output will be:

Outer Loop: 1, Inner Loop: 1
Outer Loop: 1, Inner Loop: 2
Outer Loop: 2, Inner Loop: 1
Outer Loop: 2, Inner Loop: 2
Outer Loop: 3, Inner Loop: 1
Outer Loop: 3, Inner Loop: 2
Option Explicit

Sub NestedLoopExample()

    'Iteration variables
    Dim i As Integer, j As Integer
    
    'Outer loop run while i <= 3 (stop condition)
    For i = 1 To 3
    
        'Inner loop run while j <= 2 (stop condition)
        For j = 1 To 2
            
            Debug.Print "Outer Loop: " & i & ", Inner Loop: " & j
            
        Next j 'Update iteration variable (j = j + 1)
    
    Next i 'Update iteration variable (i = i + 1)

End Sub

10.5 Exercises (For Loop)

10.5.1 Cumulative Sum of Numbers

Write a function named CumulativeSumNumbers that takes an integer n as input and returns the sum of all numbers from 1 to n.

Option Explicit

Function CumulativeSumNumbers(n As Integer) As Integer
    'Add code here!
End Function

Sub TestSumNumbers()

    Debug.Assert CumulativeSumNumbers(5) = 15
    Debug.Assert CumulativeSumNumbers(10) = 55
    Debug.Assert CumulativeSumNumbers(1) = 1

End Sub

10.5.2 Factorial of a Number

Write a function named Factorial that takes an integer n as input and returns the factorial of n.

Option Explicit

Function Factorial(n As Integer) As Long
    'Add code here!
End Function

Sub TestFactorial()

    Debug.Assert Factorial(5) = 120
    Debug.Assert Factorial(0) = 1
    Debug.Assert Factorial(1) = 1

End Sub

10.5.3 Calculate the Power of a Number

Write a function named Power that takes two doubles, x and y, as input and returns x raised to the power of y.

Option Explicit

Function Power(x As Double, y As Double) As Double
    'Add code here!
End Function


Sub TestPower()

    Debug.Assert Power(2, 3) = 8
    Debug.Assert Power(5, 0) = 1
    Debug.Assert Power(3, -2) = 0.111111111

End Sub

10.5.4 Check Prime Number

Write a function named IsPrime that takes an integer n as input and returns True if n is a prime number, otherwise returns False.

Option Explicit

Function IsPrime(n As Integer) As Boolean
    'Add code here!
End Function

Sub TestIsPrime()

    Debug.Assert IsPrime(7) = True
    Debug.Assert IsPrime(4) = False
    Debug.Assert IsPrime(17) = True

End Sub

10.5.5 Average of Numbers

Write a function named AvgNumbers that takes an integer n as input and returns the average of all numbers from 1 to n.

Option Explicit

Function AvgNumbers(n As Integer) As Double
    'Add code here!
End Function

Sub TestAvgNumbers()
    Debug.Assert AvgNumbers(5) = 3
    Debug.Assert AvgNumbers(10) = 5.5
    Debug.Assert AvgNumbers(1) = 1
End Sub

10.5.6 Sum of Odd Numbers

Write a function named SumOddNumbers that takes an integer n as input and returns the sum of all odd numbers from 1 to n.

Function SumOddNumbers(n As Integer) As Double
    'Add code here!
End Function

Sub TestSumOddNumbers()

    Debug.Assert SumOddNumbers(5) = 9
    Debug.Assert SumOddNumbers(10) = 25
    Debug.Assert SumOddNumbers(1) = 1

End Sub

10.5.9 Reverse a String

Write a VBA function named ReverseString that takes a string str as input and returns a new string with the characters of str reversed.

Hint
  • Use Len to get the size of the string.
  • Use Mid to extract substrings.
Option Explicit

Function ReverseString(str As String) As String
    'Add code here!
End Function

'VBA Sub to test ReverseString function
Sub TestReverseString()

    Debug.Assert ReverseString("Hello, World!") = "!dlroW ,olleH"
    Debug.Assert ReverseString("") = ""
    Debug.Assert ReverseString("12345") = "54321"
    Debug.Assert ReverseString("a") = "a"

End Sub

10.5.10 Count Vowels in a String

Write a VBA function named CountVowels that takes a string str as input and returns the count of vowels (A, E, I, O, U) in the string.

Option Explicit

Function CountVowels(ByVal str As String) As Integer
    'Add code here!
End Function

'VBA Sub to test CountVowels function
Sub TestCountVowels()

    Debug.Assert CountVowels("Hello, World!") = 4
    Debug.Assert CountVowels("") = 0
    Debug.Assert CountVowels("AEIOUaeiou") = 10
    Debug.Assert CountVowels("12345") = 0

End Sub

10.5.11 Remove Spaces from a String

Write a VBA function named RemoveSpaces that takes a string str as input and returns a new string with all spaces removed.

Option Explicit

Function RemoveSpaces(ByVal str As String) As String
    'Add code here!
End Function

'VBA Sub to test RemoveSpaces function
Sub TestRemoveSpaces()

    Debug.Assert RemoveSpaces("Hello, World!") = "Hello,World!"
    Debug.Assert RemoveSpaces("") = ""
    Debug.Assert RemoveSpaces("Remove spaces") = "Removespaces"
    Debug.Assert RemoveSpaces("123 45") = "12345"

End Sub

10.6 Do While … Loop (Do … Loop While) (Indefinite)

This loop allows to repeat a block of code until a certain condition is met. It can be used when the number of iterations is not known in advance. It is the opposite of the Do Until loop (see Section 10.8).

  • Do While loops as long as the condition evaluates to True.
  • Do Until loops until the stop condition evaluates to True.

In Figure 10.2, we have the structure of a Do While loop. Notice that the condition is checked at the beginning of the loop. Then, the loop body is executed if the condition is True.

graph LR
  Start((Start)) --> LoopStart[Initialize<br>iteration variable]
  LoopStart --> While[Do While]
  While-->Condition{Condition}
  Condition--True-->Statement1["`**Loop Body**
  Statement 1
  Statement 2
  ...
  Statement N
  _(Update iteration variable)_`"]
  Statement1--->Condition
  Condition ---->|False| End((End))
Figure 10.2: Strucutre of a Do While loop. Notice that the condition is checked at the beginning of the loop. Then, the loop body is executed if the condition is True. If the condition is False, the loop is exited. Inside the loop, the iteration variable is updated before checking the condition again.

In Listing 10.2, we have a do-while loop that prints the numbers from 1 to 5.

Listing 10.2: A do-while loop that prints the numbers from 1 to 5. First, the iteration variable n is initialized to 1. Then, the loop body is executed while n is less than 5. The loop body prints the value of n and increments n by 1. The loop stops when n is equal to 5. 
Option Explicit

Sub DoWhileLoopExample()
    
    'Iteration variable
    Dim count As Integer
        
    'Initialization
    count = 1
    
    'Stop condition (stop if evaluates to False; loop as long as
    'count is lower or equal than 5)
    Do While count <= 5
        
        Debug.Print "Count: " & count

        'Increment iteration variable (count = count + 1)
        count = count + 1
    
    'Send the control flow back to check the stop condition
    Loop
    
End Sub

10.6.1 Entering the Loop Before Checking the Stop Condition

Sometimes you want to ensure that the loop runs at least once. In this case, you can use the Do … Loop While structure. In Figure 10.3, we have the structure of a Do … Loop While loop. In Listing 10.3, we have a Do … Loop While loop that prints the number 6. Notice that count starts with 6 (i.e., the stop condition is True) but the code block within the loop is executed nevertheless. This happens because the condition is checked at the end of the loop.

graph LR
  Start((Start)) --> LoopStart[Initialize<br>iteration variable]
  LoopStart --> While[Do]
  While-->Statement1["`**Loop Body**
  Statement 1
  Statement 2
  ...
  Statement N
  _(Update iteration variable)_`"]
  Statement1--->Condition -->|True| LoopStart
  Condition{While<br>Condition} -->|False| End((End))
Figure 10.3: In a Do … Loop While loop, the loop body is executed before checking the stop condition. This allows the loop to run at least once, even if the stop condition is already met.
Listing 10.3: Example of a Do … Loop While loop that prints the number 6. The iteration variable count is initialized to 6. The loop body prints the value of count and increments count by 1. The loop stops when count is equal to 5. Notice that the loop body is executed at least once, even if the stop condition is already met. 
Option Explicit

Sub DoLoopWhileExample()
    
    'Iteration variable
    Dim count As Integer
    
    'Initialization
    count = 6
    

    Do 'Execute block first, check stop condition later
    
        Debug.Print "Count: " & count
        count = count + 1 'Increment of iteration variable
    
    'Stop condition (loop as long as count is lower or equal than 5)
    Loop While count <= 5
    
End Sub

Checking the condition at the end is especially useful when you need to validate user input before entering the loop. For example, in the code below, the user is prompted to enter a number between 1 and 100. The loop will keep asking for input until a valid number is entered.

Option Explicit

Sub DoLoopWhileDataValidationExample()
    
    Dim userInput As Integer

    Do
        userInput = InputBox("Enter a number between 1 and 100:")
    Loop While userInput < 1 Or userInput > 100
    
    Debug.Print "Valid Input:", userInput
    
End Sub

If we didn’t use the Do … Loop While structure, the code would look like this:

Option Explicit

Sub WithoutDoLoopWhileDataValidationExample()
    
    Dim userInput As Integer
    
    userInput = InputBox("Enter a number between 1 and 100:")
    
    Do While userInput < 1 Or userInput > 100
        userInput = InputBox("Enter a number between 1 and 100:")
    Loop
    
    Debug.Print "Valid Input:", userInput
    
End Sub

Notice that in the second example, the code block is not executed before checking the stop condition. So we need to repeat the InputBox code twice.

10.7 Exercises (Do While)

10.7.1 Euro Cup Group Stage Matches Generator

The UEFA European Football Championship, commonly known as the Euro Cup, is a football competition held every four years.

In the Euro Cup, teams are distributed across different groups based on their past performance. For example, in Euro Cup 2024, 24 teams are divided into 6 groups of 4 teams each. In Table 10.1, we have a list of teams in Column A that need to be randomly assigned matches against other teams in the same group.

Table 10.1: Teams in Group A of the Euro Cup 2024.
A B
1 Team Name Opponent
2 Germany
3 Switzerland
4 Hungary
5 Scotland

The rules to assign matches are as follows:

  1. A team cannot play against itself.
  2. Each team must play against a different team in the same group.

Create a Sub named GenerateEuroCupMatches that assigns matches to teams in a group. The teams listed in Column A must be randomly assigned an opponent from the same group in Column B.

10.7.2 Best Student Finder

In Table 10.2, we have the scores of students in different subjects. The table contains the student ID in Column A, and the scores in Columns B, C, and D for Programming, Probability, and Core Topics, respectively.

Table 10.2: Matrix of student scores in different subjects.
A B C D
1 ID Programming Probability Calculus
2 s01 85 75 90
3 s02 70 80 85
4 s03 75 70 80
5 Best Grade:
6 Best Student:

Aiming to analyze the performance of students in each subject to select next year’s TAs, develop a Subroutine called FindBestStudentsPerSubject that determines the best grade in each subject and the student who obtained that grade. For example, in Table 10.2, the best grade in Programming is 85, and the student who obtained that grade is 1. Therefore, the output in B5 should be 85, and the output in B6 should be s01.A

10.7.4 Find a Random Number Less Than 50

Write a function named FindRandomNumber that repeatedly generates random numbers until it finds a number less than 50.

Option Explicit

Function FindRandomNumber()
    'This code will generated random sequences
    Rnd -1
    Randomize 400 'Change this number to get new sequences
    
    'Add code here!
    
End Function

Sub TestFindRandomNumber()

    Debug.Assert FindRandomNumber < 50

End Sub

10.7.5 Generate a Random Prime Number

Write a Sub named GenerateRandomPrime that repeatedly generates random numbers until it finds a prime number.

Stop when a prime number is found, and print how many iterations it took to find the prime number.

Hint

Create a function IsPrime to be used inside the loop.

Option Explicit

Sub GenerateRandomPrime()
    'This code will generated random sequences
    Rnd -1
    Randomize 400 'Change this number to get new sequences
    
    'Add code here!
    
End Sub

'VBA Sub to test GenerateRandomPrime function
Sub TestGenerateRandomPrime()
    GenerateRandomPrime
    
    '> Result with Randomize 45:
    'Random Prime Number: 67 / #Iterations: 1
    
    '> Result with Randomize 7:
    'Random Prime Number: 7 / #Iterations: 2
    
    '> Result with Randomize 400:
    'Random Prime Number: 53 / #Iterations: 6 

End Sub

10.7.6 Find the Largest Digit in a Number

Write a function named LargestDigit that takes an integer n as input and returns the largest digit in n.

Option Explicit

Function LargestDigit(n As Long) As Integer
    'Add code here!
End Function

Sub TestLargestDigit()

    Debug.Assert LargestDigit(12345) = 5
    Debug.Assert LargestDigit(987654321) = 9
    Debug.Assert LargestDigit(0) = 0

End Sub

10.8 Do Until…Loop (Do … Loop Until) (Indefinite loop)

The Do Until loop allows you to repeat a block of code until a certain condition is met. It can be used when the number of iterations is not known in advance. It is the opposite of the Do While loop:

  • Do Until loops until the stop condition evaluates to True. Therefore, the loop body is executed if the condition is False.
  • Do While loops as long as the condition evaluates to True. Therefore, the loop body is executed if the condition is True.
When to Use Do Until

Whichever loop you choose (Do While or Do Until) depends on the condition you want to check. If you want to check if a condition is True, use Do While. If you want to check if a condition is False, use Do Until. It will depend on the logic of your program, what is more readable or intuitive, and what makes more sense in the context of your code.

graph LR
  Start((Start)) -->LoopStart[Initialize<br>iteration variable]
  LoopStart --> Until[Do Until]
  Until-->Condition{Condition}
  Condition--False-->Statement1["`**Loop Body**
  Statement 1
  Statement 2
  ...
  Statement N
  _(Update iteration variable)_`"]
  Statement1--->Condition
  Condition ---->|True| End((End))
Figure 10.4: Structure of a Do Until loop. The stop condition is checked at the beginning of the loop. The loop body is executed if the condition is False. If the condition is True, the loop is exited. Inside the loop, the iteration variable is updated before checking the condition again.
Listing 10.4: A Do Until loop that prints the numbers from 1 to 4. First, the iteration variable n is initialized to 1. Then, the loop body is executed until n is greater than or equal to 5. The loop body prints the value of n and increments n by 1. The loop stops when n is equal to 5. 
Option Explicit

Sub DoUntilLoopExample()


    'Iteration variable
    Dim count As Integer
    
    'Initialization
    count = 1
    
    'Stop condition (stop if evaluates to True)
    'Loops until count is greater than 5
    Do Until count >= 5
    
        Debug.Print "Count: " & count
        
        'Increment iteration variable (count = count + 1)
        count = count + 1
    
    'Send control flow back to check the stop condition
    Loop
    
End Sub

10.8.1 Entering the Loop Before Checking the Stop Condition

Sometimes you want to ensure that the loop runs at least once. In this case, you can use the Do … Loop Until structure. In Figure 10.5, we have the structure of a Do … Loop Until loop. In Listing 10.5, we have a Do … Loop Until loop that prints the number 6

graph LR
  Start((Start)) --> LoopStart[Initialize<br>iteration variable]
  LoopStart --> Do
  Do -->Statement1["`**Loop Body**
  Statement 1
  Statement 2
  ...
  Statement N
  _(Update iteration variable)_`"]
  Statement1-->Condition -->|False| Do
  Condition{Until<br>Condition} -->|True| End((End))
Figure 10.5: In a Do … Loop Until loop, the loop body is executed before checking the stop condition. This allows the loop to run at least once, even if the stop condition is already met.
Listing 10.5: A Do … Loop Until loop that prints the number 6. First, the iteration variable n is initialized to 6. Then, the loop body is executed until n is greater than or equal to 6. Notice that the loop body is executed before checking the stop condition (6 is already greater than or equal to 6). This is why the loop runs only once. 
Option Explicit

Sub DoLoopUntilExample()
    
    'Iteration variable
    Dim count As Integer
    
    'Initialization
    count = 6
    
    Do 'Execute block first, check stop condition later
        Debug.Print "Count: " & count
        
        'Increment iteration variable (count = count + 1)
        count = count + 1
    
    'Stop condition (loop as long as count is lower or equal than 5)
    Loop Until count >= 6
End Sub

10.9 Exercises (Do Until)

Repeat the exercises for the Do While loop using Do Until.

10.10 For Each…Next Loop

The For Each loop is used to iterate through elements of a collection. A collection is an iterable object (e.g., array, Range). With the For Each loop, you can process each item in a collection without knowing its size in advance.

In Figure 10.6, we have the structure of a For Each loop. Notice that the loop is initialized with a collection and the loop body is executed for each element in the collection, stopping when all elements have been processed.

graph LR
  Start((Start))-->LoopStart["Initialize<br>collection"]
  LoopStart-->For[For Each]
  For-->Condition{"Next element 
  in collection?"}
  Condition--->|True|Statement1["`**Loop Body**
  Statement 1
  Statement 2
  ...
  Statement N`"]
  Statement1--->Condition
  Condition---->|False| End((End))
Figure 10.6: Structure of a For Each loop. The loop is initialized with a collection. The loop body is executed for each element in the collection. The loop stops when all elements have been processed.

In Listing 10.6, we have a For Each loop that prints the elements of a collection.

Listing 10.6: A For Each loop that prints the elements of a collection. First, a collection named fruits is created and populated with three elements. The loop body prints each element of the collection. The loop stops when all elements have been processed. 
Option Explicit

Sub ForEachLoopExample()
    
    'Creating a collection
    Dim fruits As Collection
    Set fruits = New Collection
    
    'Populating (adding elements to) the collection
    fruits.Add "Apple"
    fruits.Add "Banana"
    fruits.Add "Cherry"
    
    'Indexes = [  1  ,    2  ,    3  ]
    ' Fruits = [Apple, Banana, Cherry]
    
    'Iteration variable (Variant because collection can hold any type)
    Dim fruit As Variant
    
    'Initialization and stop condition
    For Each fruit In fruits
    
        Debug.Print "Fruit: " & fruit
        
    Next fruit 'Increment (go to the next element in collection)
    
End Sub

10.11 Loops and Continue

Sometimes you need to skip part of the code in a loop and continue to the next iteration. The “continue” behavior:

  • ends the current iteration in a loop, and
  • continues to the next iteration.

Using continue, you can skip code for certain iterations.

In VBA, the behavior of “continue” can be achieved using the GoTo statement.

Option Explicit

Sub ContinueExample()

    'Iteration variable
    Dim i As Integer
    
    'Initialization and stop condition
    For i = 1 To 5
        
        'Skipping iterations 2 and 4
        If i = 2 Or i = 4 Then
            Debug.Print "Skipping " & i
            GoTo SkipIteration
        End If
        
        'This part of the code is skipped (if "continue" condition is True)
        Debug.Print "Iteration " & i

'Control flow jumps here after GoTo statement
SkipIteration:
    
    Next i 'Increment
    
End Sub

10.12 Exit Do and Exit For Statements

Sometimes you need to exit a loop before it completes all iterations. To prematurely exit loops when a condition is met you can:

  • Use Exit Do statement in Do ... Loop
  • Use Exit For statement in For ... Next

In the example below, the For loop is supposed to go over 10 iterations but it is finished sooner.

Listing 10.7: A For loop supposed to go over 10 iterations but it is finished after 5 iterations. The loop body prints the value of i. The loop stops when i is equal to 5. The Exit For statement breaks out of the loop. The code after the loop is executed. 
Option Explicit

Sub ExitDoForExample()
    
    'Iteration variable
    Dim i As Integer
    
    'Initialization and stop condition
    For i = 1 To 10
            
        'Premature termination condition
        If i = 5 Then
            Exit For 'Break out of the loop!
        End If
        
        Debug.Print "Iteration " & i
    
    Next i 'Increment (i = i + 1)
    
    '`Exit For` makes the flow jump to the first code line after the loop
    Debug.Print "Finished execution."
    
End Sub
Listing 10.8: A Do While loop that prints the numbers from 1 to 5. The loop is supposed to run indefinitely, but it is terminated when count is greater than 5. The loop body prints the value of count and increments count by 1. The loop stops when count is equal to 5. 
Sub DoWhileLoopExampleWithExit()

    'Iteration variable
    Dim count As Integer
    
    'Initialization
    count = 1
    
    'Infinite loop
    Do While True
                
        Debug.Print "Count: " & count
        
        'Increment iteration variable (count = count + 1)
        count = count + 1
        
        'Stop condition (stop if evaluates to True)
        If count > 5 Then
            Exit Do
        End If
        
    Loop
    
    Debug.Print "Finished program."

End Sub