Types of Loops in VBA
|For … Next||Fixed||This loop repeats a group of statements for a given number of times.|
|For Each ... Next||Fixed||This loop repeats a group of statements for each element in an array or collection.|
|Do While||Indefinite||This loop executes an action if the condition is True, and repeats the action until the condition becomes False.|
|Do Until||Indefinite||This loop executes an action if the condition is False, and repeats the action until the condition becomes True.|
|Do ... Loop While||Indefinite||This loop executes an action once, and repeats the action while the condition is True until it becomes False.|
|Do ... Loop Until||Indefinite||This loop executes an action once, and repeats the action while the condition is False until it becomes True.|
|While ... Wend||Indefinite||This loop executes a series of statements as long as a given condition is True and continues to perform it until the condition becomes False.|
In the table above you can see two types of loops Fixed and Indefinite.
Fixed (or definite) loops
In fixed-iteration (or definite) loops, the number of iterations is known before you start the execution of the iteration of the loop.
Indefinite loops repeat a flexible number of times, the number of iterations is not known before you start the execution of the iteration of the loop, but depends on when a certain condition becomes true.
It is a simple and effective way to repeat an action for a specified number of times.
For counter = start To end [ Step step ] [ statements ] [ Exit For ] [ statements ] Next [ counter ]
Note: the square brackets indicate the optional parts of loop.
The For…Next statement syntax has these parts :
Numeric variable used as a loop counter.
Initial value of counter.
Final value of counter.
Amount counter is changed each time through the loop. If not specified, step defaults to one.
One or more statements between For and Next that are executed the specified number of times.
Provides a way to exit a For loop early. It can be used only in a For…Next or For Each…Next loop.
When loop reaches the Next statement, it increments counter by 1 or by the specified
step size and loops back to the For statement.
Note: It is the good practice to specify the
counter variable in the Next statement to make code clear but not compulsory.
Alt + F11 keys to open the Visual Basic Editor (VBE). We'll use the Immediate window to see the output of loops so open the Immediate window if it is not visible in VBE by pressing
Ctrl + G button or by clicking the
View > Immediate Window from the top menu.
In a simple For…Next loop, you first specify a counter variable and the starting and ending values for it:
Dim c As Integer For c = 1 to 10
c is the counter variable,
1 is the starting value, and
10 is the ending value. Because VBA by default increases the counter variable by 1 with each iteration of the loop, the counter variable in this example will count 1, 2, 3, and so on up to 10. Once the loop iterates enough times so the value in counter is 11, the looping ends and execution continues in the line below the loop's last statement.
You can also use the
Step keyword to specify a different increment, either positive or negative.
Dim c As Integer For c = 1 To 10 Step 2
Now VBA increases the counter variable by 2 with each iteration of the loop, the counter variable in above example will count 1, 3, 5, 7 and 9.
After specifying the previous statements, you specify whatever actions you want carried out within the loop, followed by the
We'll use the
Debug.Print statement as action to show the output in Immediate Window.
Debug.Print helps to analyze the changes in the values of variables created in the VBA program. It shows the output of the immediate window when we run the program.
Debug.Print c Next c
This code displays (on Immediate window) 1, 2, 3 and so on up to 10 and 1, 3, 5, 7 and 9 if you used the
Step 2 keyword.
Sub The_For_Next_Loop() Dim c As Integer For c = 1 To 10 Debug.Print c Next c End Sub
Using Step Value in For Loop
Here's another example that uses a
Step value of 2
Sub The_For_Next_Loop() Dim c As Integer For c = 1 To 10 Step 2 Debug.Print c Next c End Sub
Exiting a For Loop
Sometimes you need to exit a For loop prematurely. This is possible with the
Exit For statement. It immediately takes you out of the loop. For example:
Sub The_For_Next_Loop() Dim c As Integer For c = 1 To 10 If c = 7 Then Exit For Debug.Print c Next c End Sub