Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 37 ยท Control Flow and Procedures

Do...Loop and While...Wend in VBA

Use VBA Do While and Do Until loops, test conditions at the start or the end, exit with Exit Do, avoid infinite loops and use While...Wend and GoTo safely.

Use a Do loop when you do not know in advance how many repetitions you need: "keep going until the user types a valid answer" or "process rows until the first empty cell". It repeats while (or until) a condition holds.

Four forms

VB
Do While condition       ' test first, run while True
    ...
Loop

Do Until condition       ' test first, run until True
    ...
Loop

Do                       ' run once, then test: body always runs at least once
    ...
Loop While condition

Do
    ...
Loop Until condition

While continues when the condition is True; Until continues when it is False. Pick the one that reads naturally.

VB
Sub CountDown()
    Dim n As Long
    n = 3
    Do While n > 0
        Debug.Print n
        n = n - 1
    Loop
    Debug.Print "Liftoff"
End Sub

prints 3, 2, 1, Liftoff.

Test at the start or at the end?

Form Minimum runs
Do While ... Loop / Do Until ... Loop 0
Do ... Loop While / Do ... Loop Until 1

Use the end-tested form for "ask at least once" tasks such as input validation:

VB
Dim s As String
Do
    s = InputBox("Enter a number:")
Loop Until IsNumeric(s) Or s = ""

Processing rows until a blank cell

A classic Excel task: walk down a column until the data ends.

VB
Dim r As Long, total As Double
r = 2                                    ' row 1 is the header
Do While Cells(r, 1).Value <> ""
    total = total + Cells(r, 3).Value
    r = r + 1
Loop
Debug.Print "Rows: " & (r - 2) & ", total: " & total

This stops at the first empty cell in column A. If rows can have gaps, find the last used row instead (the Excel chapter shows how) and use a For loop.

Exit Do

Exit Do leaves the innermost Do loop immediately:

VB
Dim tries As Long
Do
    tries = tries + 1
    If tries > 5 Then Exit Do
    ' ... try something ...
Loop Until success

An Exit Do plus a counter is the standard safety net against loops that never end.

Avoiding infinite loops

If the condition never changes the loop never ends and Excel freezes. Typical causes: forgetting to update the variable (n = n - 1), a condition that can never become False (Do While True without Exit Do), or a comparison that never matches. If it happens, press Ctrl+Break (or Esc) to interrupt; the editor will stop on a line of the loop and you can click Reset.

Checklist for every Do loop:

  1. What changes each pass?
  2. Will the condition eventually flip?
  3. Is there a cap or an Exit Do?

While...Wend

An older loop, equivalent to Do While ... Loop but with no Exit statement:

VB
While n < 10
    n = n * 2
Wend

Prefer Do loops because Exit Do works and the syntax is consistent.

GoTo and labels

VBA supports GoTo label. Used wisely it exits nested loops at once, but it makes flow harder to follow, so keep it for error handling (On Error GoTo) and breaking out of deep nesting:

VB
For r = 1 To 100
    For c = 1 To 100
        If Cells(r, c).Value = "X" Then GoTo Done
    Next c
Next r
Done:
Debug.Print "stopped at " & r & ", " & c

Stand-in: loop forms in JavaScript

The JavaScript stand-in (not VBA) shows While versus Until, the at-least-once loop and a safety cap.

JS Stand-in: Do While, Do Until and a safety cap

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which loop always runs its body at least once?

    When the test sits after Loop, the body runs once before the first test.
  2. 2. What does Do Until x = 5 do?

    Until continues until the condition becomes True, i.e. while it is False.
  3. 3. How do you leave a Do loop early?

    Exit Do leaves the innermost Do loop.
  4. 4. What interrupts a runaway macro?

    Ctrl+Break breaks into the running code so you can fix the loop.

Summary

  • Do While/Do Until test before; Loop While/Loop Until test after.
  • Always change something inside the loop and consider a cap with Exit Do.
  • Use Do for unknown counts (rows until blank, retry until valid).
  • While...Wend and GoTo exist but are best avoided.

Next: Procedures and Functions