VBA-Lesson 8.1. Loops

Loops repeat instructions several times, and can save a lot of time.

The following code inserts a sequence of numbers into each cell of column A (from 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 insert consecutive numbers into hundreds of cells instead of just 12... Now you understand why loops are very useful.

WHILE

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 statements in the loop will continue to execute (but be careful not to create an infinite loop).

Below is the looping macro that was considered first, converted into a While loop:

Sub while_loop()

     Dim num As Integer
     num = 1 'The starting number (in this case, it is the line number and the value to be inserted)

     While num <= 12 'As long as the variable num is <= 12, the instructions will be executed
        Cells(num, 1) = num 'Numbering
        num = num + 1 'The number is incremented by 1 on each pass of the loop
     Wend
       
End Sub

Using this loop in a macro, all we would have to do if we needed 400 tapes instead of 12 is to replace 12 with 400.

DO LOOP

There is another way to write a loop that works like a While Wend (it executes as long as a certain condition contained in the While command is true):

Sub do_while_loop()

     Do While [condition]
         'Instructions
     Loop

End Sub

The following version shows that the condition can be written at the end of the Do Loop cycle, which means that the instructions will be executed exactly at least once:

Sub do_while_loop()

     Do
         'Instructions
     Loop While [condition]

End Sub

Instead of repeating the loop while the condition is fulfilled, we can also exit the loop by condition if we replace 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 repeat here 5 times.

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

Sub for_loop()

     For i = 1 To 5
         MsgBox i
     Next

End Sub

Early exit from the cycle

It is possible to exit the For loop early by using the following instruction:

Exit For 'Exit from the For loop

Let's consider an example that will clearly show this:

Sub for_loop()
     Dim max_loops As Integer
     max_loops = Range("A1") 'In A1 : we specified the limit of number of repetitions

     For i = 1 To 7 'Expected number of cycles: 7
        If i > max_loops Then 'If A1 is empty or contains a number < 7, reduce the number of iterations
            Exit For 'If the condition is TRUE, exit from the For loop
        End If
   
        MsgBox i
     Next

End Sub

Other Exit operators: