Analyst Cave

VBA Do While Loop – Using Do While…Loop in VBA

In this VBA Do While / VBA Do Until tutorial I will demonstrate how to use conditional loops i.e. loops that run While or Until a condition is met. Do While loops should not be used to iterate through arrays or collections. Instead they are met to be run only if a specific condition needs to be met during execution (e.g. run my code while my condition is met). If you want to learn how to iterate through VBA Arrays or VBA Collections read my VBA For loop tutorial.

VBA Do While

Below is an example of a VBA Do While loop. The loop will run While the condition i in the statement below is true.

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9
Do While i < 10
   MsgBox i
   i = i + 1
Loop

Or you can push the While statement to the end of the loop:

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9
Do 
   MsgBox i
   i = i + 1
Loop While i < 10

Why push the While statement to the back. This makes sense if you don’t want the condition to be tested in the first run of the loop.

Do Until Loop

Will loop until the condition in the Until statement is met (true).

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10
Do Until i > 10
   MsgBox i
   i = i + 1
Loop

Or you can push the Until statement to the end of the loop:

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10,11
Do 
   MsgBox i
   i = i + 1
Loop Until i > 10
The For loop and For Each loop should be used in different situations! Be sure to read this post

.

Exit Do While / Do Until loop

Sometimes it makes sense to leave a VBA Do While or a VBA Do Until loop earlier than the condition specified. We can do this using the Exit Do statement. Below usage:

Do While ...
  '...
  Exit Do 'Exit the VBA Do While loop immediately
  '...
Loop

See this example below of how Exit Do is used:

i = 0
Do While i < 10
  
  If i Mod 5  Then
     Exit Do 'Exit the VBA Do While loop if i modulo 5 equals 0
  End If

  Debug.Print n
  i = i + 1
Loop
Exit mobile version