Performance difference between looping range vs looping array

Question

Will there be any difference in performance (speed) when looping through a range Vs assigning the same range to an Array and looping an Array?

score 7 · Accepted Answer · answered Oct 23 '15 at 13:38

7

Looping through an array is way faster than looking through a range.

See my test below:

Option Explicit

Const strRANGE_ADDRESS As String = "A1:A100000"

Sub LoopRangeAddOne()

    Dim r As Range
    Dim lStart As Double
    Dim lEnd As Double

    lStart = Timer

    For Each r In Range(strRANGE_ADDRESS)
        r.Value = r.Value + 1
    Next r

    lEnd = Timer

    Debug.Print "Duration = " & (lEnd - lStart) & " seconds"

End Sub

Sub LoopArrayAddOne()

    Dim varArray As Variant
    Dim var As Variant
    Dim lStart As Double
    Dim lEnd As Double

    lStart = Timer

    varArray = Range(strRANGE_ADDRESS).Value
    For Each var In varArray
        var = var + 1
    Next var
    Range(strRANGE_ADDRESS).Value = varArray

    lEnd = Timer

    Debug.Print "Duration = " & (lEnd - lStart) & " seconds"

End Sub

Results:

LoopRangeAddOne Duration = 2.2734375 seconds

LoopArrayAddOne Duration = 0.08203125 seconds

Which makes looping through an array 96.39% faster than through a loop.

Hope this helps :)

answered Oct 23 '15 at 13:38

The more values you have, the bigger the difference will be. Make sure you are efficient with your loops still (ex. exiting as soon as possible). – Kyle Oct 23 '15 at 15:29
@tech1234 - I would be interested to see what happens to the timing of the range method if you turn of ScreenUpdating, make Calculation manual, and do it on a sheet that isn’t the active sheet. – Sercho Dec 28 '17 at 23:16
@Sercho This is what I got. Notice that there is a difference because of a new system. So I am rerunning the tests. New system no change LoopRangeAddOne Duration = 3.1171875 seconds LoopArrayAddOne Duration = 0.0859375 seconds Turning all off new system LoopRangeAddOne Duration = 0.5703125 seconds LoopArrayAddOne Duration = 0.078125 seconds – Dec 30 '17 at 03:43
@tech1234, thanks for the update! Very interesting. Still quicker using the arrays, but just not as quick. I only asked because I have run into trouble with VBA Subs exceeding the LOC limit, requiring me to use Ranges to transfer Arrays between procedures (not elegant, but suffices what I needed at the time). – Sercho Dec 30 '17 at 04:21
2

@Sercho you have to put them in a scale and see what you need. I do not like turning off the calculations because if not handled correctly can leave the workbook as manual (and that is a tricky one) But yes there is a limit to the array. I think that using the arrays of data we separate the concerns data and spreadsheet. Of course to address the limit problem a several arrays approach could be used. – Dec 30 '17 at 04:25

score 0 · Answer 2 · edited Oct 20 '19 at 15:51

0

Just be aware that if range is filtered (has some filter on it) the corresponding array that you get by Range(strRANGE_ADDRESS).Value will only contain the visible values the filter shows!

This is a very nasty peculiarity of Excel.

Excel filters are a plague; if the Excel filter is set then Range.Find will only search in the filtered area. This can be OK if that's what we want to do but if we just want to keep the filter and search through the entire range the reality gets ugly very quickly.

edited Oct 20 '19 at 15:51

d219

2,707
5
31
36

answered Oct 20 '19 at 12:52

freshmeat

1

Welcome to SO! When you answer one question check that you are answering the question. In your case, you are making a comment rather that giving any answer. – David García Bodego Oct 20 '19 at 13:12

Performance difference between looping range vs looping array

2 Answers2

Linked