Some time ago, when I was learning about looping I read that “You should avoid For Each loop when it is not necessary” or something like that. From that time I was trying to use only For loops. You can imagine my surprise when some guy with over 50k reputation points on StackOverflow said to me encouraging to use For Each, because it is faster! So which is faster – For vs For Each?
So why not check it? 🙂
As always I wrote a simply code to found out if this is true.
Option Explicit Sub main() Dim clock As Double Dim i As Long Dim colA As Range, cel As Range clock = Timer With ThisWorkbook.Sheets(1) For i = 1 To x .Cells(i, 1) = 1 Next Debug.Print Round(Timer - clock, 3) clock = 0 clock = Timer Set colA = .Range(.Cells(1, 1), .Cells(x, 1)) For Each cel In colA cel = 1 Next Debug.Print Round(Timer - clock, 3) 'MsgBox "Done" End With End Sub
Where x stand for rows (see table).
|Rows (x)||For||For Each|
We can see from the table, that all in all he was right! In that case – looping through range cells – is faster. Until 100 000 rows we can’t really notice any significant time difference between loops, but above that amount, with 1 000 000 rows, it is over 6 seconds. We can easily call it: a huge gap!
Numbers not lying, to be honest For Each is faster in every case than simple For. Keep in mind, that it is just simple looping and filling cells with number 1. In more complicated cases the time difference can be much, much bigger.
However, remember about that You can’t or it is more complicated to use only one type of loop. In some cases will be easier and faster to use For Each loop and in some more accurate method for further functions will be For loop. Sometimes bigger simplicity in code is more important than their speed. Everything depends, especially when the difference is not that big.
Of course I am not telling You to take my word for it, make your own comparison For vs For each and check out which is faster for yourself : )