The VBA For Loop and the Do While loops are the two most important loops in the VBA language. They can be used to carry out a specific task in an iterative manner till such time that a condition is met (or the VBA loop is terminated).
Download the worksheet with examples of the for, do while and do until loops in VBA
The VBA For Loop
The VBA For Loop essentially iterates from the starting value all the way till the end value. Essentially when you write something like:
Next i
you are telling Excel to undergo 10 loops. In each of the successive loops, the counter i will be incremented by 1. So by the time the For Loop ends, i could have reached a value of 10. Between the For…..Next identifiers, you can write specific pieces of code that will get repeated every time. By using the for loop in this manner, you ensure that each of the steps required to go from the initial value to the final value are executed.
Dim i, j As Integer
j = 1
For i = 1 To 10
j = j + 1
Next i
MsgBox j
End Sub
The second form of the For Loop is :
Dim i As Integer
i = 1
For each cel in range(cells(1,1), cells(10,1))
cel.value = “Hello World”
Next cel
End Sub
In this case, the for loop works by iterating through an ‘array’ of objects (more often used for a range of cells which essentially is an ‘array’ of cells). This form of the For loop in VBA is typically used when a particular action is to be performed on each of the constituent elements of the array. In this example, we use the for loop to write “Hello World” to the first 10 cells of the first column.
The VBA Do Loop
The Do loop in VBA is typically used to carry out an iteration till a particular condition is met. Unlike the for loop in VBA, the do loop does not need to iterate through each of the steps from an initial value all the way to the final value. The Do loop will simply check whether a particular condition has been met at the end of each iteration. It stops executing the moment the condition is met. Just bear in this loop will stop executing whenever the first condition is met irrespective of the fact that there may remain other successive values in the loop that could have caused it to go on.
Dim i As Integer
Do
i = i + 1
Loop While (i < 3)
MsgBox i
End Sub
Another interesting this to note is that the Do….Until and Do…..While Loops will iterate atleast once since the condition to be met is placed at the end of the loop. The loop will go through the entire code once and only then have a chance to check if the condition to terminate has been met or not.
Dim i As Integer
Do
i = i + 1
Loop Until (i > 3)
MsgBox i
End Sub
There’s another variant to the two Do….Loops described above. This one differs from the rest in the sense that since the while condition is placed right at the top, the loop has the chance to check for the condition before the first iteration takes place. Therefore, this Do…Loop, unlike its other variants, may not execute even once.
Dim i As Integer
i = 0
Do While (i < 3)
i = i + 1
Loop
MsgBox i
End Sub
How to stop, skip and move out of a VBA For Loop, Do While Loop and Do Until Loop
This one is simple.
Exit the For Loop in VBA
Use the statement “Exit For” for move out of a For loop in VBA.
Dim i, j As Integer
j = 1
For i = 1 To 10
j = j + 1
if (j=5) then Exit For
Next i
MsgBox j
End Sub
Exit the Do While Loop in VBA
Use the statement “Exit Do” for move out of a Do While loop in VBA.
Dim i As Integer
Do
i = i + 1
if (i=2) then Exit Do
Loop Until (i > 3)
MsgBox i
End Sub
Tip: Use the “Exit Sub” statement to move out of the sub procedure itself.
How to speed up the execution of the VBA For and Do…While loops
To improve the speed of execution of the VBA code you can use:
…………..VBA Code here………….
…………..VBA Code here………….
Application.ScreenUpdating = True
The application.screenupdating directive turns on and off the updating of the cells in the worksheet thus allowing it to utilize all the resources towards the execution of the code. You may also want to my post on how to speed up and improve the execution of vba code in excel.
[...] Read more about loops at Excel VBA For Do While and Do Until Loop. [...]
June 29th, 2009 at 7:22 pm