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
For Each when iterating object collections:
Dim sheet As Worksheet For Each sheet In ActiveWorkbook.Worksheets Debug.Print sheet.Name Next
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
For Each [item] In [collection] [statements] Next [item]
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