Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 37 ยท Control Flow and Procedures

For...Next and For Each Loops in VBA

Repeat work in VBA with For...Next (Step, counting down), nested loops, Exit For and For Each over arrays and collections, with loop-speed tips.

Loops repeat a block of code. VBA has two families: counted loops (For...Next, For Each) and condition loops (Do...Loop). Use a For loop when you know how many times to repeat or which items to visit.

For...Next

VB
Sub CountUp()
    Dim i As Long
    For i = 1 To 5
        Debug.Print i
    Next i
End Sub

This prints 1 to 5. i is the counter; it starts at 1, the body runs, Next i adds 1, and the loop ends once i would exceed 5. After the loop i holds 6.

Step

VB
For i = 0 To 100 Step 25      ' 0, 25, 50, 75, 100
For i = 10 To 1 Step -1       ' 10, 9, ... 1
For x = 0 To 1 Step 0.25      ' decimals allowed (use Double, beware rounding)

Counting down needs a negative Step: For i = 10 To 1 does nothing.

Rules worth remembering

  • The start, end and step are evaluated once before the first pass. Changing the end variable inside the loop does not change the number of iterations.
  • Do not change the counter inside the body (it works but confuses everyone).
  • Next does not need the counter name, but writing Next i makes nested loops readable.
  • Use Long for counters (see Variables).

Exit For

Leave a loop early when the job is done:

VB
Dim i As Long, found As Long
For i = 1 To 1000
    If Cells(i, 1).Value = "Total" Then
        found = i
        Exit For
    End If
Next i

VBA has no Continue. Skip the rest of an iteration with an If around the rest of the body, or a GoTo label placed just before Next.

Nested loops

VB
Dim r As Long, c As Long
For r = 1 To 3
    For c = 1 To 3
        Debug.Print r & " x " & c & " = " & r * c
    Next c
Next r

The inner loop completes for every iteration of the outer one: 3 x 3 = 9 lines. Each loop needs its own counter variable, and the Next statements must close in reverse order (inner first).

For Each

For Each visits every element of an array or collection without managing an index. The loop variable must be a Variant, an Object, or the exact object type:

VB
Dim fruit As Variant
For Each fruit In Array("apple", "pear", "plum")
    Debug.Print UCase(fruit)
Next fruit

Dim cell As Range
For Each cell In Range("A1:A10")
    If cell.Value < 0 Then cell.Font.Color = vbRed
Next cell

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next ws

Use For Each for ranges, collections and sheets, and For i = LBound To UBound when you need the index or want to change array elements (assigning to the loop variable in For Each does not change the array).

Speed tips

Loops that touch the worksheet are slow because each access crosses from VBA into Excel. Three habits help a lot:

  1. Read a range into an array, loop in memory, write back (Arrays).
  2. Turn off screen updating while the macro runs: Application.ScreenUpdating = False, and always switch it back on.
  3. Avoid Select and Activate inside loops.

Stand-in: loops in JavaScript

This JavaScript stand-in (not VBA) shows Step, a countdown, Exit For and an inner loop. Notice how the end value is fixed before the loop starts, like VBA.

JS Stand-in: Step, Exit For and nesting

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

No output.

Practice: sum and average

VB
Function AverageOf(values As Variant) As Double
    Dim v As Variant, total As Double, n As Long
    For Each v In values
        total = total + v
        n = n + 1
    Next v
    If n > 0 Then AverageOf = total / n
End Function

AverageOf(Array(4, 8, 12)) returns 8. The If n > 0 guard avoids dividing by zero for an empty list.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How many times does For i = 1 To 10 Step 3 run?

    i takes the values 1, 4, 7 and 10: four iterations.
  2. 2. What does For i = 10 To 1 do (no Step)?

    The default step is +1, so the start is already past the end.
  3. 3. How do you leave a For loop early?

    Exit For leaves the innermost For loop.
  4. 4. Which loop is best for visiting every cell of a Range?

    For Each iterates the cells of a range directly.

Summary

  • For i = a To b [Step s] ... Next i repeats a counted number of times.
  • Use a negative Step to count down; Exit For leaves early; VBA has no Continue.
  • For Each visits arrays, ranges and collections.
  • Read ranges into arrays and disable ScreenUpdating for speed.

Next: Do Loops