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.
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.
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
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
Exit For 'Exit from the For loopLet'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:
Exit Do 'Exit from the Do Loop
Exit Sub 'Exit from the procedure
Exit Function 'Exit from the function