I am looking at this documentation, I understand the For Loops, I understand Counters, Steps, etc. But when I reach the VBA For Each Loop section of the page, I don't quite understand it.

The VBA For Each loop is a scope that defines a list of statements that are to be repeated for all items specified within a certain collection/array of items. The For Each loop, as compared to the For loop, can’t be used to iterate from a range of values specified with a starting and ending value.

It felt like the words were just jumping out at the same time. They gave me an example that was a little more complicated:

Dim x(3) as Long, xIterator as Variant
x(0) = 1: x(1) = 2: x(2) = 3
 
For Each xIterator in x
   Debug.Print x
Next xIterator
'Result: 1,2,3

I want a definition that is easy to understand with an example. Please know that I am new to Excel/VBA

I am looking at this documentation, I understand the For Loops, I understand Counters, Steps, etc. But when I reach the VBA For Each Loop section of the page, I don't quite understand it.

The VBA For Each loop is a scope that defines a list of statements that are to be repeated for all items specified within a certain collection/array of items. The For Each loop, as compared to the For loop, can’t be used to iterate from a range of values specified with a starting and ending value.

It felt like the words were just jumping out at the same time. They gave me an example that was a little more complicated:

Dim x(3) as Long, xIterator as Variant
x(0) = 1: x(1) = 2: x(2) = 3
 
For Each xIterator in x
   Debug.Print x
Next xIterator
'Result: 1,2,3

I want a definition that is easy to understand with an example. Please know that I am new to Excel/VBA

• excel
• vba
• for-loop

• 1 Note Dim x(3) as Long gives you an array with 4 elements [assuming you don't have Option Base 1 at the top of your script] x(0), x(1),x(2) and x(3). Debug.Print xIterator will print 1,2,3 and 0 – CDP1802 Commented Mar 29 at 17:37
• 1 There's advice around here that suggests not using For Each loop for an array, so that's probably a poor example. – BigBen Commented Mar 31 at 14:45

2 Answers 2

Imagine you have a box of toys (a "collection"). The For Each loop is like saying:

"For every toy in this box, I want to do something with it (like inspect it, put it on a shelf, etc.). Once I've done that something with every single toy in the box, I'm done."

You don't tell it how many times to run. It runs once for each item in a collection (like a box of toys, a range of cells in Excel, etc.). The number of times it runs depends on how many items are in the collection.

Let's say you have some numbers in cells A1 to A5 of your Excel sheet, and you want to double the value of each of these cells using VBA. Here's how you could do it with a For Each loop:

Sub DoubleCellValues()

  Dim cell As Range 'Declare a variable to hold each cell
  Dim myRange As Range

  'Define the range you want to loop through (A1:A5)
  Set myRange = Range("A1:A5")

  'For Each cell in the range...
  For Each cell In myRange
    'Double the value of the cell
    cell.Value = cell.Value * 2
  Next cell 'Move to the next cell in the range

End Sub

The For Each loop is designed to easily process every item in a collection (like a range of cells), without you having to worry about keeping track of indexes or counters. It makes your code cleaner and easier to read when you want to do the same thing to every item in a group.

It is also worth noting that the For Each loop only reads the elements of the collection, but does not modify them, unless both the iterator and the elements of the collection are objects.
Please compare the code in the following examples.

   
Sub Test1()
    Dim a As Collection
    Set a = New Collection
    Dim i As Long
    For i = 1 To 5
        a.Add i
    Next i
    Dim e As Variant
    For Each e In a
        e = 2 * e
        Debug.Print e
    Next e
    ' Collection did not change
    For Each e In a
        Debug.Print e
    Next e
    Set a = Nothing
End Sub

Sub Test2a()
    Dim a As Collection
    Set a = New Collection
    Dim i As Long
    For i = 1 To 5
        Cells(i, 1).Value = i
        a.Add Cells(i, 1)
    Next i
    Dim e As Variant   ' Variant!
    For Each e In a
        e = 2 * e
        Debug.Print e
    Next e
    ' Collection did not change
    For Each e In a
        Debug.Print e
    Next e
    Set a = Nothing
End Sub

Sub Test2b()
    Dim a As Collection
    Set a = New Collection
    Dim i As Long
    For i = 1 To 5
        Cells(i, 1).Value = i
        a.Add Cells(i, 1)
    Next i
    Dim e As Range   ' Range!
    For Each e In a
        e = 2 * e
        Debug.Print e
    Next e
    ' Collection did change
    For Each e In a
        Debug.Print e
    Next e
    Set a = Nothing
End Sub