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