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.
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 SubFor 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.
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 SubA 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?
You May Also Like
Conditionals in VBA
Learn how VBA evaluates Boolean expressions to branch code using If...Then...Else, ElseIf chains, and Select Case.
Arrays in VBA
Store and process many values under one name with fixed and dynamic arrays, ReDim, and fast range-to-array techniques.
Subs and Functions in VBA
Understand VBA's two procedure types—action-performing Subs and value-returning Functions—plus arguments, scope, and UDFs.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics