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<10 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
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