VBA Loops

A loop is a programming construct which iteratively executes code. There are different types of loops which use different techniques to iterate. For loops use a counter, Do loops use a condition, and For Each loops iterate over all the elements within an iterable object such as an array or Collection. Loops can also be created using the GoTo statement or recursive procedure calls. Infinite loops can be created intentionally or accidentally which will iterate forever or until the loop's execution is interrupted by the user.

For Loops

For Loops increment or decrement a counter after each iteration until a specified limit is reach. For loops should be used when the number of each iteration is important and when looping over arrays. Specify a starting value, a limit, and a step value to increment or decrement by. If Step is omitted, the default Step value is one. The control variable can be omitted after the Next keyword but this is not recommended. Explicitly writing the control variable helps improve code readability, especially when there are multiple nested loops.

Public Sub Example()

    Dim i As Long  

    'Prints 0 through 9 - Step value is 1 by default
    For i = 0 To 9
        Debug.Print i
    Next i

    'Prints 0 through 9
    For i = 0 To 9 Step 1
        Debug.Print i
    Next i

    'Prints 9 through 0
    For i = 9 To 0 Step -1
        Debug.Print i
    Next i

    'Prints 0, 2, 4, 6, 8
    For i = 0 To 9 Step 2
        Debug.Print i
    Next i

    'Prints 0 through 9 - Control Variable omitted - Not recommended
    For i = 0 To 9
        Debug.Print i
    Next

End Sub

For Each Loops

For Each Loops are used to loop over iterable objects such as arrays and Collections. Specify a control variable and an iterable object to loop over. Writing the control variable after the Next keyword is optional but it is recommended to write it. Explicitly writing the control variable helps with code readability, especially when there are multiple nested loops. When looping over a Collection, the control variable must be a Variant, Object, or the specific Class type of the objects contained in the Collection.

Note: A For Each loop should always be used to iterate over a Collection. It is inefficient to loop over a Collection using a For loop because accessing each item through the Item method of the Collection object is inefficient. With For Each loops, each item is accessed efficiently via the control variable on each iteration.

Public Sub Example()

    '''Loop over collection of value type variables

    Dim Coll As Collection
    Set Coll = New Collection

    Coll.Add 1
    Coll.Add 2
    Coll.Add 3

    Dim i As Variant

    For Each i In Coll
        Debug.Print i
    Next i

    Set Coll = Nothing

End Sub

Public Sub Example()

    '''Loop over a collection of objects

    Dim Coll As Collection
    Set Coll = New Collection

    Coll.Add New Collection
    Coll.Add New Collection
    Coll.Add New Collection

    Dim C As Collection
   'Dim C As Object 'Also works

    For Each C In Coll
        Debug.Print C.Count
    Next C

    Set Coll = Nothing

End Sub

Do While Loops

Do While Loops iterate as long as a conditional statement evaluates to True. Do loops can be used to provide structure and flexibility to looping. The condition can be placed at the top or bottom of the loop.

If the condition is placed at the top of the loop it will be tested before executing its first iteration.

Public Sub Example()

    'Prints 0 through 9

    Dim i As Long

    Do While i < 10
        Debug.Print i
        i = i + 1
    Loop

End Sub

If the condition is placed at the bottom, the loop will always execute the first iteration before testing the condition.

Public Sub Example()

    'Prints 0 through 9

    Dim i As Long

    Do 
        Debug.Print i
        i = i + 1
    Loop While i < 10

End Sub

Do Until Loops

Do Until Loops iterate until a conditional statement evaluates to True. In other words, a Do Until loop iterates while a conditional statement is False. Do loops can be used to provide structure and flexibility to looping. The condition can be placed at the top or bottom of the loop.

If the condition is placed at the top of the loop it will be tested before executing its first iteration.

Public Sub Example()

    'Prints 0 through 9

    Dim i As Long

    Do Until i = 10
        Debug.Print i
        i = i + 1
    Loop

End Sub

If the condition is placed at the bottom, the loop will always execute the first iteration before testing the condition.

Public Sub Example()

    'Prints 0 through 9

    Dim i As Long

    Do 
        Debug.Print i
        i = i + 1
    Loop Until i = 10

End Sub

While Wend Loops

While Wend Loops iterate until a conditional statement evaluates to True. While...Wend loops do not provide the structure and flexibility that Do loops provide. Therefore, Do loops should always be preferred over While...Wend loops.

Public Sub Example()

    'Prints 0 through 9

    Dim i As Long

    While i < 10
        Debug.Print i
        i = i + 1
    Wend

End Sub

GoTo Loops

The GoTo statement can be used to construct a loop by using a condition and a label or line number. Although loops can be constructed using the GoTo statement it is not recommended. Formal looping constructs should be preferred over loops created using GoTo.

Public Sub Example()

    'Prints 0 through 9

    Dim i As Long

TopLoop:
    Debug.Print i
    i = i + 1
    If i < 10 Then GoTo TopLoop

End Sub

Exiting Loops Early

At times it is necessary to break out of a loop before it completes all iterations. To exit from a loop early use the Exit statement.

To exit a For Next loop or a For Each loop early use Exit For.

Public Sub Example()

    'Prints 0 through 5 and then exits loop

    Dim i As Long

    For i = 0 To 9

        Debug.Print i

        If i = 5 Then
            Exit For
        End If

    Next i

End Sub

Public Sub Example()

    'Prints 1, 2 then exits loop

    Dim Coll As Collection
    Dim i As Variant

    Set Coll = New Collection
    Coll.Add 1
    Coll.Add 2
    Coll.Add 3

    For Each i In Coll
 
        Debug.Print i

        If i = 2 Then
            Exit For
        End If

    Next i

    Set coll = Nothing

End Sub

To Exit a Do While or Do Until loop early use Exit Do.

Public Sub Example()

    'Prints 0 through 9

    Dim i As Long

    Do While True

        Debug.Print i

        i = i + 1

        If i > 9 Then
            Exit Do
        End If

    Loop

End Sub

Public Sub Example()

    'Prints 0 through 9

    Dim i As Long

    Do Until False

        Debug.Print i

        i = i + 1

        If i > 9 Then
            Exit Do
        End If

    Loop

End Sub

To exit a While Wend loop or a loop constructed using the GoTo statement, use the GoTo statement with a label or line number outside of the loop.

Public Sub Example()

    'Prints 0 through 4

    Dim i As Long

    While i < 10
        Debug.Print i
        i = i + 1
        If i = 5 Then
            GoTo ExitWhileWendLoop
        End If
    Wend
ExitWhileWendLoop:

End Sub

Public Sub Example1()

    'Prints 0 through 4

    Dim i As Long

    TopLoop:
    Debug.Print i
    i = i + 1
    If i = 5 Then GoTo ExitLoopEarly
    If i < 9 Then GoTo TopLoop
ExitLoopEarly:

End Sub

Nested Loops

Loops can be nested inside each other. Any type of loop can be nested inside any type of loop.

Public Sub Example()

    Dim i As Long
    Dim j As Long
    Dim k As Long

    Dim c As Long

    For i = 1 To 10
        For j = 1 To 10
            For k = 1 To 10
                c = c + 1
            Next k
        Next j
    Next i

    Debug.Print c

End Sub

Recursion

Recursion is a loop in and of itself. A recursive procedure will call itself until a base case is reached (or until stack space is exhausted).

Public Sub Example()

    Recurse 0

End Sub

Public Function Recurse(i As Long)

    'Prints i through 9

    If i > 9 Then Exit Function    
    Debug.Print i
    Recurse i + 1

End Function

Infinite Loops

Infinite loops continue to iterate until the user manually intervenes. Infinite loops can be bugs created accidentally which can cause a program to get stuck, or they can be created intentionally to serve a purpose. Some examples of use cases for infinite loops might be REPLs (Read Evaluate Print Loops), games, monitoring programs, servers, and operating systems. These programs by their nature need to run continuously unless the user manually intervenes.

Public Sub Example1()
    Do While True
        'True is always True.
        Stop
    Loop
End Sub

Public Sub Example2()        
    Do While 1 = 1
        '1 = 1 is always True.
        Stop
    Loop
End Sub

Public Sub Example3()
    Do Until False
        'False is never True.
        Stop
    Loop
End Sub