For vs For Each – which is faster?

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?

for vs for each which is faster

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).

Results

Rows (x)ForFor Each
1000,0000,000
10000,0200,012
100000,1600,113
1000001,8551,328
100000019,55113,113

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 : )

Tomasz Płociński

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

One thought on “For vs For Each – which is faster?”

Leave a Reply

Your email address will not be published. Required fields are marked *