Using the For loop in VBA
For loops repeat statements a specific number of times or for each object in a collection.
- For...Next: Use a counter to run statements a specified number of times. This is discussed on this page.
- For Each...Next: Repeat a group of statements for each object in a collection
- Do...Loop: Looping while or until a condition is True
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.
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) Next
The image below shows it included in the Code VBA » VBA » Array menu.
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:
For i = 4 To 0 Step -0.5 Next i
Other pages on loops