VBA Course: Loops

While

Loops make it possible to repeat instructions a number of times, which can save a lot of time.

The following code puts sequential numbers into each of the cells in column A (from row 1 to 12):

Sub while_loop()

    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 ...

Imagine if we had to number hundreds of cells instead of just 12 ... Now you understand why loops can be useful.

Here is an example of an empty While loop:

Sub while_loop()

    While [condition]
        'Instructions
    Wend

End Sub

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

And here is the repetitive macro introduced above, converted into a While loop:

Sub while_loop()

    Dim num As Integer
    num = 1 'Starting number (in this case, this is both the row number and the number that will be placed in each cell)

    While num <= 12 'As long as the num variable is <= 12, the instructions will loop
        Cells(num, 1) = num 'Numbering
        num = num + 1 'The number is increased by 1 each time the instructions loop
    Wend
	
End Sub

Using this loop macro, all we would have to do if we wanted to number 500 lines instead of just 12 would be to replace 12 with 500 ...

Do Loop

This is another way to write a loop command that works the same way as While Wend (as long as the condition is true, the instructions contained within the While command will loop):

Sub do_while_loop()

    Do While [condition]
        'Instructions
    Loop

End Sub

In this case, the conditions can also be placed at the end of the Do Loop loop, which means that the instructions will definitely be executed at least once:

Sub do_while_loop()

    Do
        'Instructions
    Loop While [condition]

End Sub

Rather than repeating the loop as long as the condition is true, it is also possible to exit the loop when the condition is true by replacing While with Until:

Sub do_while_loop()

    Do Until [condition]
        'Instructions
    Loop

End Sub

For

Sub for_loop()

    For i = 1 To 5
        'Instructions
    Next

End Sub

The For loop will be repeated here 5 times.

At each repetition of the loop, the variable i is automatically incremented by 1:

Sub for_loop()

    For i = 1 To 5
        MsgBox i
    Next

End Sub

Early exit from a loop

It's possible to exit a For loop early by using the following instruction:

Exit For 'Exit a For loop

Here is an example of this:

Sub for_loop()

    Dim max_loops As Integer
    max_loops = Range("A1") 'In A1: we have defined a limit to the number of repetitions

    For i = 1 To 7 'Number of loops expected: 7
        If i > max_loops Then 'If A1 is empty or contains a number < 7, decrease the number of loops
            Exit For 'If the condition is true, we exit the For loop
        End If
    
        MsgBox i
    Next

End Sub

The other Exit instructions:

Exit Do 'Exit a Do Loop loop

Exit Sub 'Exit a procedure

Exit Function 'Exit a function