Loops in VBA

Types of Loops in VBA

Loop	Type	Description
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
  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.

For…Next

It is a simple and effective way to repeat an action for a specified number of times.

Syntax:

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 :

counter
Numeric variable used as a loop counter.

start
Initial value of counter.

end
Final value of counter.

step
Amount counter is changed each time through the loop. If not specified, step defaults to one.

statements
One or more statements between For and Next that are executed the specified number of times.

Exit For
Provides a way to exit a For loop early. It can be used only in a For…Next or For Each…Next loop.

Next
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.

Examples

Press 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

Here, 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 Next keyword. 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