VBA Course: Loops

Loops allow you to repeat instructions a certain number of times, saving you from having to write endless macros and saving you a considerable amount of time.

The following code numbers the cells in column A (from row 1 to 12):

Sub example()

    Cells(1, 1) = 1
    Cells(2, 1) = 2
    Cells(3, 1) = 3
    Cells(4, 1) = 4
    Cells(5, 1) = 5
    Cells(6, 1) = 6
    Cells(7, 1) = 7
    Cells(8, 1) = 8
    Cells(9, 1) = 9
    Cells(10, 1) = 10
    Cells(11, 1) = 11
    Cells(12, 1) = 12

End Sub

This code is very repetitive...

Now, imagine if you had to number thousands of rows... You probably understand the importance of creating loops.

Here's the Do loop:

Sub example()

    Do While [CONDITION]
        'Instructions
    Loop

End Sub

As long as the condition is true, the instructions are executed in a loop (be careful not to create an infinite loop).

Here's the repetitive macro above using the Do loop:

Sub example()

    Dim number As Integer

    number = 1 'Starting number

    Do While number <= 12 'While the variable "number" is <= 12, the loop is repeated
        Cells(number, 1) = number 'Numbering
        number = number + 1 'The number is increased by 1 at each loop
    Loop

End Sub

With this loop, if we want to number 500 rows, we just need to replace 12 with 500...

Do Loop

In the previous example, you saw the Do loop in the following form:

Sub example()

    Do While [CONDITION]
        'Instructions
    Loop

End Sub

With Do, the condition can also be placed at the end of the loop, which means that the instructions will always be executed at least once:

Sub example()

    Do
        'Instructions
    Loop While [CONDITION]

End Sub

Instead of repeating the loop while the condition is true, it's possible to exit the loop when the condition is true by replacing While with Until:

Sub example()

    Do Until [CONDITION]
        'Instructions
    Loop

End Sub

For Next

Sub example()
    
    Dim i As Integer

    For i = 1 To 5
        'Instructions
    Next

End Sub

The For loop is repeated here 5 times.

With each iteration of the loop, the variable i is automatically incremented by 1:

Sub example()
    
    Dim i As Integer

    For i = 1 To 5
        MsgBox i 'Outputs the values: 1 / 2 / 3 / 4 / 5
    Next

End Sub

If necessary, you can modify the increment (default is 1) of the loop by adding Step:

Sub example()
    
    Dim i As Integer

    For i = 10 To 0 Step -2
        MsgBox i 'Outputs the values: 10 / 8 / 6 / 4 / 2 / 0
    Next

End Sub

For Each Next

The For Each loop allows you to iterate over each element in a collection of items, for example, iterate over each cell in a range of cells:

Sub example()

    Dim cell As Range
    
    For Each cell In Range("A1:B3")
        cell = cell.Address
    Next

End Sub

Iterate over each sheet in the workbook:

Sub example()

    Dim sheet As Worksheet
    
    For Each sheet In Worksheets
        MsgBox sheet.Name
    Next

End Sub

Iterate over each item in an array:

Sub example()

    Dim myArray(2) As String
    
    myArray(0) = "A"
    myArray(1) = "B"
    myArray(2) = "C"
    
    For Each value In myArray
        MsgBox value
    Next

End Sub

Exiting a loop

You can exit a For loop prematurely using the following statement:

Exit For 'Exit a For loop

In this example, the goal is to return the number of the first row containing the value 1. When this goal is achieved, the number is displayed, and the loop is terminated (as there's no need to continue searching through the remaining rows):

Sub example()
    
    Dim i As Integer

    'Loop for a maximum of 100 rows
    For i = 1 To 100

        'If the cell equals 1
        If Cells(i, 1) = 1 Then 'If the goal is achieved
            MsgBox "The cell was found in row " & i & "!"
            Exit For 'Exit the For loop
        End If

    Next

End Sub

The other Exit statements:

Exit Do 'Exit a Do loop

Exit Sub 'Exit a Sub procedure

Exit Function 'Exit a Function