Commenting on the Good Practice post, John Tolle pointed out that with a collection it was very much quicker to loop using a For each loop than iterating by index, using a for, next loop. I did some simple tests on this, also looking at ranges and arrays, and found he was absolutely right. The test consisted of looping through a collection, range or array with 600, 6000 or 60,000 items, 1000, 100, or 10 times, adding 1 to the value at each step, so there were 600,000 operations in all cases. The results are summarised in the screen shot below:
The lessons from this exercise were:
- Arrays were always faster than the equivalent operations with a collection, and very much faster than a range
- Using a “For Each” loop on an array was 3 times faster than a “For, Next” loop.
- Using a “For Each” loop on a collection was almost as fast as a “For Each” loop with an array.
- Times for a “For, Next” loop with a collection were proportional to the square of the size of the collection. For the largest collection (60,000 items) this form of loop was nearly 2000 times slower than the “For Each” loop.
- Where possible use “For Each” loops, in preference to a “For, Next” loop.
- If a “For, Next” loop is necessary, use an array in preference to a collection.
- Avoid iterating through a range in any application where speed is important.
For those interested in the code, the spreadsheet can be downloaded from CheckLoopSpeed.xls
The macro checkloop is currently set up for 10 iterations of 60,000 cells. If you want to change the parameters you need to change the NumReps value and the UserRng extent (at two locations) in the code. Note that for the loops using a range I have used 1/10 the number of iterations, and multiplied the time by 10, so the NumReps value should be 1/10 the actual number you want.
On re-running I also found that the range times were about twice as fast as previously, but still very much slower than the array times, or the collection times with a for each loop.