VBA Flow control structures For Each loop


Example

The For Each loop construct is ideal for iterating all elements of a collection.

Public Sub IterateCollection(ByVal items As Collection)

    'For Each iterator must always be variant
    Dim element As Variant

    For Each element In items
        'assumes element can be converted to a string
        Debug.Print element
    Next

End Sub

Use For Each when iterating object collections:

Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
    Debug.Print sheet.Name
Next

Avoid For Each when iterating arrays; a For loop will offer significantly better performance with arrays. Conversely, a For Each loop will offer better performance when iterating a Collection.

Syntax

For Each [item] In [collection]
    [statements]
Next [item]

The Next keyword may optionally be followed by the iterator variable; this can help clarify nested loops, although there are better ways to clarify nested code, such as extracting the inner loop into its own procedure.

Dim book As Workbook
For Each book In Application.Workbooks

    Debug.Print book.FullName

    Dim sheet As Worksheet
    For Each sheet In ActiveWorkbook.Worksheets
        Debug.Print sheet.Name
    Next sheet
Next book