Loops in VBA

Analogy categoryAnalogies

Repeating Work with Loops

Loops let a macro repeat a block of statements many times without duplicating code. VBA offers counter-driven For...Next loops, collection-driven For Each loops, and condition-driven Do loops, letting you iterate a fixed number of times, over every item in a collection, or until some state changes.

🏏

Cricket analogy: A bowler repeats the same run-up and delivery action ball after ball for a six-ball over, and a loop repeats its statement block a set number of times just like counting down an over.

For...Next Loops

For counter = start To end Step increment ... Next runs a fixed number of times, incrementing the counter automatically. Step can be negative to count down, or any value to skip. Use Exit For to leave early. For...Next is ideal when you know the iteration count in advance, such as walking rows 1 to 100.

🏏

Cricket analogy: Counting deliveries from ball 1 to ball 6 in an over, one at a time, is a For loop with Step 1; a negative step is like reviewing the over backward from the last ball.

vba

Sub SumColumn()
    Dim i As Long
    Dim total As Double
    total = 0

    For i = 1 To 100
        If IsNumeric(Cells(i, 1).Value) Then
            total = total + Cells(i, 1).Value
        End If
    Next i

    Range("C1").Value = total
End Sub

For Each for Collections

For Each element In collection iterates over every member of a collection or array without an index, binding each item to a variable in turn. It is the natural choice for looping over Worksheets, cells in a Range, or a Dictionary's items. You cannot rely on a numeric position, and Exit For still works to break out early.

🏏

Cricket analogy: Shaking hands with each member of the team lineup, one player at a time, is For Each—you touch every element without caring about jersey numbers.

For Each over a Range visits cells in row-major order (left to right, then down). When you only need to read or write each cell, For Each ws In Worksheets or For Each cell In rng is often faster and clearer than computing indices with Cells(row, col).

Do Loops: While and Until

Do While condition ... Loop repeats while the condition stays True; Do Until condition ... Loop repeats until it becomes True. Placing the test at the bottom (Do ... Loop While) guarantees the body runs at least once. Do loops shine when you do not know the count ahead of time—reading rows until a blank cell, for instance. Use Exit Do to break out.

🏏

Cricket analogy: A team keeps batting until it loses all ten wickets, an unknown number of balls governed by a condition, exactly a Do Until wickets = 10 loop.

vba

Sub CountUntilBlank()
    Dim r As Long
    r = 1

    Do While Cells(r, 1).Value <> ""
        Cells(r, 2).Value = r        ' write the row number alongside
        r = r + 1
    Loop

    MsgBox "Processed " & (r - 1) & " rows"
End Sub

A Do loop whose condition never becomes False creates an infinite loop that freezes Excel—press Ctrl+Break to interrupt it. Always ensure something inside the loop changes the tested value (such as incrementing a counter or advancing a row), and never modify a For...Next counter variable by hand inside the loop.

For...Next runs a known number of times using a counter with an optional Step, including negative steps.
For Each iterates every item in a collection or array without needing a numeric index.
Do While repeats while a condition is True; Do Until repeats until it becomes True.
Testing at the bottom (Do...Loop While/Until) guarantees the body runs at least once.
Exit For and Exit Do break out of a loop early.
Guarantee loop progress to avoid infinite loops, and use Ctrl+Break to interrupt a hung macro.

Practice what you learned

1. Which loop is best when you do not know the iteration count in advance?

2. What does Step -1 do in a For...Next loop?

3. Which construct iterates a collection without using an index?

4. How do you guarantee a Do loop's body runs at least once?

5. How do you break out of a For loop early?

Was this page helpful?

Conditionals in VBA

Subs and Functions in VBA

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Snowflake Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Topics covered

#Programming#VBAStudyNotes#LoopsInVBA#Loops#VBA#Repeating#Work#StudyNotes#SkillVeris#ExamPrep

Repeating Work with Loops

For...Next Loops

For Each for Collections

Do Loops: While and Until

Practice what you learned

You May Also Like

Related Reading

Related Study Notes in Programming

Topics covered