Excel – VBA – Do While Loop, Repeat a Block

There are 2 ways a Do While loop can be used in Excel VBA Macro code.

You can tell Excel to check the condition before entering the loop, or tell Excel to enter the loop and then check the condition.

To stop an endless loop, press ESC or CTRL+BREAK.

To force an exit from a Do Until loop you use the line: Exit Do, e.g. If lNum = 7 Then Exit Do

• In the first example (Check_First) the condition (lNum>10) is checked before entering the loop.
• In the second example (Check_After) the condition (lNum>10) is checked after entering the loop.

[tab:Check First]

Sub Check_First()

Dim lCount As Long
Dim lNum As Long

    lCount = 0
    lNum = 11

    Do While lNum > 10
        lNum = lNum - 1
        lCount = lCount + 1
    Loop

MsgBox "The Do While loop made " & lCount & " loop(s)."

End Sub

[tab:Check After]

Sub Check_After()

Dim lCount As Long
Dim lNum As Long

    lCount = 0
    lNum = 9

    Do
        lNum = lNum - 1
        lCount = lCount + 1
    Loop While lNum > 10

MsgBox "The Do While loop made " & lCount & " loop(s)."
End Sub

[tab:END]

Do Loops

Syntax:

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop

Or, you can use this syntax:

Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]