In VBA, it's known that the For Each loop will iterate through a Collection faster than the For loop, with the difference in time between the two looping methods increasing exponentially(?) as a function of the Collection size. (This assumes that iteration is "in order" over the Collection members of course.)
Why is it faster though? How can the For Each loop access items faster than via their indices in a Collection?
I faced the same problem recently with a collection of instances of some class, and, indeed, for each is further faster than for. I ran some tests to know why and what I found is that the extra delay using for is due find the specified item among all itens in collection.
The test: I create a simple class containing just an integer as public field and nothing more. Then, was created 100,000 instances of this class and added in a collection
Dim mClass As cls
Dim col As New Collection
For i = 1 To 100000
Set mClass = New cls
mClass.a = 1
Call col.Add(mClass)
Set mClass = Nothing
Next i
then I measured the time to run each one of the following block of code
1) For each (time ~0.95s)
For Each obj In col
obj.a = 1
Next
2a) For through all elements of collection (time ~95s)
For i = 1 To 100000
Set mClass = col(i)
mClass.a = 1
Set mClass= Nothing
Next i
2b) For only the first element (time ~0.15s)
For i = 1 To 100000
Set mClass = col(1)
mClass.a = 1
Set mClass = Nothing
Next i
2c) For only the last element(time ~180s)
For i = 1 To 100000
Set mClass = col(100000)
mClass.a = 1
Set mClass = Nothing
Next i
3) allocating and deallocating memory (time ~0.72s)
For i = 1 To 100000
Set mClass = New cls
mClass.a = 1
Set mClass = Nothing
Next i
4) using another instance out of collection (time ~0.05s)
Dim mclass2 As cls
Set mclass2 = New cls
For i = 1 To 100000
Set mClass = mclass2
mClass.a = 1
Set mClass = Nothing
Next i
The time it takes works more in relative basis than absolute one and give us an idea where time is spended.
Your presumption that For Each is faster than For 1 To is wrong. Both loop through a set of numbers which they can do at identical speed.
In the For Each variant the number identifies objects in a collection, such as Sheets(1), Sheets(2), Sheets(3) etc. These sheets aren't loaded or even accessed in the loop. They are just referenced.
By comparison, For i = 1 to 3: Set Ws = Sheets(i) would just create references to the sheets.
Accordingly, a difference develops from what you do with the referenced objects, not from the way you reference them. For Each often appears as the somewhat simpler code. But if you wish to refer to ActiveSheet.Cells(3) you do need to know whether this will be C1 or A3 and the apparent greater ease of coding comes at the cost of transparency. I treat it as a matter of taste.
Dim Arr As Variant
Dim R As Long
Arr = Range("A1:A20")
For R = 1 To UBound(Arr)
Debug.Print Arr(R, 1)
Next R
is much faster than
Dim Rng As Range
Dim Cell As Range
Set Rng = Range("A1:A20")
For Each Cell in Rng
Debug.Print Cell.Value
Next Cell
But this is because the second code references the sheet 20 times against the first snippet's once. Perhaps it's this difference that you have been reading about.
