10  Loops

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

• a specific number of times (definite loop), or
• until a certain condition is met (indefinite loop).

In VBA, there are 5 loop structures:

• For … Next
• Do While … Loop (Do … Loop While)
• Do Until … Loop (Do … Loop Until)
• For Each … Next

10.1 Elements of a loop

• Initialization
• Stop condition
• Increment/decrement

Warning

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

1. Iterating Over Arrays or Collections
2. Calculating Aggregates (e.g., sums, averages, counts)
3. Data Validation and Input
4. Searching and Filtering
5. Generating Patterns or Sequences
6. Repeating Actions

10.3 Do Until…Loop (Do … Loop Until) Statement (Indefinite 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 While loop:
  • Do Until loops until the stop condition evaluates to True
  • Do While loops as long as the condition evaluates to True.

Option Explicit

Sub DoUntilLoopExample()
    
    Debug.Print vbCrLf & "# Loops - Example `Do Until ... Loop`"
    
    '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.3.0.1 Entering the loop before checking the stop condition

In the example below, notice that count starts with 6 (i.e., the stop condition is True) but the code block within the loop is executed nevertheless.

Option Explicit

Sub DoLoopUntilExample()
    
    Debug.Print vbCrLf & "# Loops - Example `Do ... Loop Until`"
    
    '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 > 5
End Sub

10.4 Exercises

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

10.5 Do While

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

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:

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

Option Explicit

Sub DoWhileLoopExample()

    Debug.Print vbCrLf & "# Loops - Example `Do While ... Loop`"
    
    '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
        
        MsgBox "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

In the example below, notice count starts with 5 (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.

Option Explicit

Sub DoLoopWhileExample()

    Debug.Print vbCrLf & "# Loops - Example `Do ... Loop While`"
    
    'Iteration variable
    Dim count As Integer
    
    'Initialization
    count = 5
    

    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

This is useful when you want to ensure that the loop runs at least once. For example, when you need to validate user input before entering the loop.

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

10.6.2 Exercises

10.6.2.1 Print Two Different Numbers Within a Range

Write a Sub named PrintTwoRandomNumbers that takes two integer parameters, minRange and maxRange. It generates and prints two different random numbers within the specified range in the Immediate Window. Ensure the two generated numbers are different.

Hint

Use function WorksheetFunction.RandBetween to generate the numbers.

Sub PrintTwoRandomNumbers(minRange As Integer, maxRange As Integer)
    
    'This code will generated random sequences
    Rnd -1
    Randomize 400 'Change this number to get new sequences
    
    'Add code here!
    
End Sub

Sub TestPrintTwoRandomNumbers()
    
    'Your answer has to print two different numbers within 
    'the ranges below in the immediate window
    
    Call PrintTwoRandomNumbers(1, 10)
    Call PrintTwoRandomNumbers(50, 100)
    Call PrintTwoRandomNumbers(20, 10)

End Sub

10.6.2.2 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.6.2.3 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.6.3 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.7 For…Next Loop (Definite)

• Used to repeat a block of code a specified number of times.
• Useful when you know in advance how many times you want to execute a set of statements.

10.7.1 Example 1

In this example, the loop will run 5 times, printing the iteration number.

Option Explicit

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

End Sub

10.7.2 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
    
    'Stop condition (Loop while 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.7.3 Nested Loops

• Loops can be nested within one another.
• Nested loops are useful to perform multiple iterations based on different conditions.

Option Explicit

Sub NestedLoopExample()

    Debug.Print vbCrLf & "# Loops - Example nested `For ... Next`"

    '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.7.4 Exercises

10.7.4.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.7.4.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.7.4.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.7.5 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.7.6 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.7.7 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.7.8 Print a Triangle Pattern

Write a Sub named PrintTrianglePattern that prints a triangle pattern of asterisks (*) in the Immediate Window.

The Sub takes as input - Integer n: Height of the triangle - String side: “left” or “rigth” triangle

And prints the triangle pattern of n lines aligned to the left or right (see examples below in the TestPrintTrianglePattern Sub).

Option Explicit

Sub PrintTrianglePattern(n As Integer, side As String)
    'Add code here!
End Sub

Sub TestPrintTrianglePattern()

    Call PrintTrianglePattern(4, "left")
    
    'Result:
    '*
    '**
    '***
    '****
    
    Call PrintTrianglePattern(4, "right")
    
    'Result:
    '   *
    '  **
    ' ***
    '****

End Sub

10.7.9 Print an X Pattern

Write a Sub named PrintXPattern that takes an integer n as input and prints an “X” pattern of asterisks (*) in the Immediate Window.

Option Explicit

Sub PrintXPattern(n As Integer)
    'Add code here!
End Sub

'VBA Sub to test PrintXPattern function

Sub TestPrintXPattern()

    PrintXPattern (5)
    'Expected result:
    '*   *
    ' * *
    '  *
    ' * *
    '*   *

End Sub

10.7.10 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(ByVal 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.7.11 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.7.12 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.8 For Each…Next Loop

• Used to iterate through elements of a collection.
• A collection is an iterable object (e.g., array, Range).
• Useful to process each item in a collection *without knowing its size in advance**.

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.9 Loops and Continue

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.10 Exit Do and Exit For Statements

• You reak out of 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.

Option Explicit

Sub ExitDoForExample()
    
    Debug.Print vbCrLf & "# Loops - Example using `Exit`"
    
    '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

Sub DoWhileLoopExampleWithExit()

Debug.Print vbCrLf & "# Loops - Example `Do While ... Loop` with Exit"

    '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