Excel VBA For Do While and Do Until Loop




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

excel-vba-for-loop-do-while-loop-and-do-until-loopDownload 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:

For i = 1 To 10
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.

Sub try()
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 :

Sub try()
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.

Sub try()
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.

Sub try()
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.

Sub try()
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.

Sub try()
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.

Sub try()
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:

Application.ScreenUpdating = False
…………..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.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. masyda wrote:

    Nice good.. thank for…

  2. annily75|yaplog!(ヤプログ!)byGMO wrote:

    Excel VBA For Do While and Do Until Loop | Excel & VBA – Databison annily75|yaplog!(ヤプログ!)byGMO http://yaplog.jp/annily75/

  1. Excel Tutorial Series – VBA Macros, Part Two Of Two - Code For Excel And Outlook Blog wrote:

    [...]     Read more about loops at Excel VBA For Do While and Do Until Loop. [...]

  2. How to Create a Dashboard in Excel | da TaB is On wrote:

    [...] understandable code. You can learn more about the VBA For Next and For Each in Next loop here and do until loop [...]

  3. Excel Macro | da TaB is On wrote:

    [...] of the previous articles dealing with this. You can learn more about VBA For Loop here. For the Do While Loop please refer to this [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel