0

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?

TehDrunkSailor
  • 448
  • 2
  • 10

1 Answers1

0

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.

Variatus
  • 14,092
  • 2
  • 12
  • 29
  • Note also that in `For Each Cell in Rng`, there is no order specified (nor documented) and the cells could be referenced in any order. – Paul Ogilvie Jul 25 '21 at 08:17
  • @PaulOgilvie https://stackoverflow.com/a/16866876/11683 – GSerg Jul 25 '21 at 11:19
  • @Variatus I understand the point that you are making about minimizing the number of interactions between VBA and the Sheet, but my question is about pure VBA: explicitly `Collections`. I did a test iteration using both of the methods through a `Collection` with 70,000 items. Each item was added to a total. The `For Each` was approximately 1000 times faster than the `For` loop (22ms vs 22s to run, respectively). – TehDrunkSailor Jul 25 '21 at 11:58
  • @TehDrunkenSailor For your test to have a bearing on my analysis we should look at what the collection consisted of and the "items" that were added. The difference you found is about the difference I would expect between reading a cell and adding its value to a total or reading an array. So, if you tested a collection of cells then your result would prove that `For Each Cell In Range.Cells` creates an array of cell values (seems strange). It wouldn't prove that the collection is accessed faster. – Variatus Jul 26 '21 at 02:39
  • @Variatus Apologies, I will be clearer this time. My test did not interact with Excel in any way; it was pure VBA. I created a collection containing 70,000 items (the numbers 1 through 70,000) in VBA. (Not a collection of cells.) Then, I iterated over each member of the collection and added the value of the member to a total. I did this iteration using both a `For Each` and a `For` loop. Since both loops were performing the exact same task, I want to know why one is able to complete faster than the other. – TehDrunkSailor Jul 26 '21 at 04:18
  • @GSerg, thanks for the link, but note that that is just empirical determined and not formally documented. Could change the next release. – Paul Ogilvie Jul 26 '21 at 05:44
  • @TehDrukungenSailor It isn't exactly the same calculation. It isn't possible to set up a collection of numbers. So, I presume you set up an array and went through the elements of the array with For, like `Total = Total + Coll(i)`. I repeated that with Each `Total = Total + Arr` where Arr is a member of the collection. I admit, the difference is stunning (14 secs against 0.004 seconds) but the explanation is in the difference of handling the array that holds the numbers. IMHO it doesn't support a generalized conclusion that collections are faster. Interesting nevertheless! – Variatus Jul 26 '21 at 08:42