Using the For loop in VBA

for loop

For loops repeat statements a specific number of times or for each object in a collection.


The For...Next loop uses a counter to run statements a specified number of times. When you select it from the Code VBA menu the basic code structure below is inserted. Obviously you will be changing it to suite your purpose.

For i = 0 To 9
Next i
It's not necessary to include the counter variable name after the Next statement. In the preceding example, the counter variable name was included for readability, especially when you have nested loops.

For Next loop example for arrays

A common example of the For Next loop is when processing arrays - below. LBound and UBound are functions that return the first and last index number of the array, so here it is stated to loop over the elements from the first to the last number of the array.

Dim arr() As Variant: arr = Array(1, "B", 3)
Dim iArrayForNext As Long
For iArrayForNext = LBound(arr) To UBound(arr)
    Debug.Print arr(iArrayForNext)

The image below shows it included in the Code VBA » VBA » Array menu.

for loop

Using the Step keyword with the For Next loop

VBA adds extra flexibility to the For loop with the Step keyword. Using Step you can change both the size and the direction of the 'steps' in the loop. The code below the value of i decreases with each step half a point: 4, 3.5, 3, ... 1, 0.5, 0

For i = 4 To 0 Step -0.5
Next i