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
