Control flow: If, Select Case and loops

Branch and loop predictably in VBA, and stop writing the off-by-one and infinite-loop bugs that break real workbooks.

If and Select Case

Option Explicit

Sub Decide(ByVal score As Long)
    Dim band As String

    ' single line form: fine for one action, hard to read with more
    If score < 0 Then band = "invalid" Else band = "ok"

    ' block form: the one to use as soon as there is an ElseIf
    If score >= 90 Then
        band = "A"
    ElseIf score >= 80 Then
        band = "B"
    ElseIf score >= 70 Then
        band = "C"
    Else
        band = "F"
    End If

    ' Select Case is the right tool for one value with several ranges
    Select Case score
        Case Is >= 90: band = "A"
        Case 80 To 89: band = "B"
        Case 70 To 79: band = "C"
        Case 0 To 69:  band = "F"
        Case Else:     band = "invalid"
    End Select

    ' Case can test several values and expressions
    Select Case UCase$(band)
        Case "A", "B": Debug.Print "passing"
        Case "C":      Debug.Print "borderline"
        Case Else:     Debug.Print "failing"
    End Select

    Debug.Print band
End Sub

ElseIf is a single keyword in VBA. Writing Else If opens a nested If that needs its own End If, which is a frequent source of mismatched blocks.
Select Case evaluates the test expression exactly once, which matters when the expression is expensive or has a side effect.
Case Is >= 90 is required for a comparison; Case 80 To 89 is a range; Case 1, 3, 5 is a list. The first matching case wins and the rest are skipped.
VBA has no short-circuit And. Both operands of If x <> 0 And 1 / x > 2 are evaluated, so the division by zero happens. Use nested If statements, or AndAlso-style nesting by hand.

That missing short-circuit is the single most common VBA bug in guarding code: the guard does not guard. Split the condition into two statements whenever the second part depends on the first being safe.

For, For Each, Do

Sub Loops()
    Dim i As Long
    Dim cell As Range
    Dim n As Long

    ' counted loop: bounds are evaluated once, at the start
    For i = 1 To 10 Step 2
        n = n + i
    Next i

    ' For Each is faster and clearer for a collection or a Range
    For Each cell In Sheet1.Range("A1:A10")
        If Len(cell.Value) > 0 Then n = n + 1
    Next cell

    ' deleting while iterating: always count backwards
    Dim r As Long
    For r = 100 To 1 Step -1
        If Sheet1.Cells(r, 1).Value = "" Then Sheet1.Rows(r).Delete
    Next r

    ' Do While: condition first, may run zero times
    i = 0
    Do While i < 5
        i = i + 1
    Loop

    ' Do Until: runs until the condition becomes true
    i = 0
    Do Until i >= 5
        i = i + 1
    Loop

    ' Do ... Loop While: the body always runs at least once
    i = 100
    Do
        i = i - 1
    Loop While i > 90

    ' an explicit exit, with the counter still valid
    For i = 1 To 1000
        If i = 7 Then Exit For
    Next i

    Debug.Print n, i
End Sub

Loop	Condition checked	Minimum runs
`For ... Next`	Counted, before each pass	Zero if the range is empty
`For Each ... Next`	Once per element	Zero
`Do While ... Loop`	Before the body	Zero
`Do ... Loop While`	After the body	One
`Do Until ... Loop`	Before the body, until true	Zero
`Do ... Loop Until`	After the body, until true	One

Deleting rows or items while counting up skips entries, because the next item shifts into the current index. Count backwards, or collect what to delete and remove it afterwards.
For Each over a collection is normally faster than indexing, and over a Range it avoids creating a Range object per cell.
A Next with the wrong variable name is legal in VBA and silently nests loops. Always name the counter in Next so a mistake is a compile error.
There is no Continue For. Use an If around the body, or a labelled block and GoTo for a multi-condition skip.

Guarding and bounding

Function FindRow(ByVal ws As Worksheet, ByVal key As String) As Long
    Const MAX_ROWS As Long = 1000000
    Dim r As Long

    ' find the last used row once, rather than relying on a fixed bound
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow > MAX_ROWS Then lastRow = MAX_ROWS

    For r = 1 To lastRow
        If StrComp(CStr(ws.Cells(r, 1).Value), key, vbTextCompare) = 0 Then
            FindRow = r
            Exit Function
        End If
    Next r

    FindRow = 0                      ' 0 means not found; document that contract
End Function

Sub RetryWithBound()
    Dim attempt As Long
    Do
        attempt = attempt + 1
        If TryOnce() Then Exit Do
    Loop While attempt < 5           ' a bound: never an unbounded Do ... Loop
End Sub

Private Function TryOnce() As Boolean
    TryOnce = (Rnd() < 0.3)
End Function

⚠️

An unbounded Do ... Loop with a condition that never becomes true hangs Excel with no way to interrupt except the Esc key, and if Application.EnableEvents is off you may not even get that. Every retry or wait loop needs a maximum attempt count and an exit path.

FAQ

Why does my ElseIf not compile?

You wrote Else If with a space. That starts a new nested If, so the block needs a second End If. VBA's ElseIf is one word.

How do I skip the rest of a loop iteration?

VBA has no Continue. Wrap the remaining body in If not skip Then ... End If, or use GoTo to a label placed just before Next.

The VBA editor and the language Sub, Function and passing arguments

Last refreshed 2026-09-18.

Control flow: If, Select Case and loops

If and Select Case

For, For Each, Do

Guarding and bounding

FAQ

Related