VBA Course: Loops
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):
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
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:
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:
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
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 ...
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):
Do While [condition]
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:
Loop While [condition]
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:
Do Until [condition]
For i = 1 To 5
The For loop will be repeated here 5 times.
At each repetition of the loop, the variable i is automatically incremented by 1:
For i = 1 To 5
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:
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
The other Exit instructions:
Exit Do 'Exit a Do Loop loop
Exit Sub 'Exit a procedure
Exit Function 'Exit a function