Performance difference between looping range vs looping array

by

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 🙂

More Related Contents:

  1. Expanding column cells for each column cell
  2. Excel-VBA – Insert new first column in datafield array without loops or API calls
  3. Creating an Array from a Range in VBA
  4. How do I slice an array in Excel VBA?
  5. Check if a value is in an array or not with Excel VBA
  6. Matching values in string array
  7. Deleting Elements in an Array if Element is a Certain value VBA
  8. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  9. How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
  10. Return Index of an Element in an Array Excel VBA
  11. Writing an array to a range. Only getting first value of array
  12. Excel VBA: Range to String Array in 1 step
  13. Faster way of using Sumifs()
  14. How to initialize a multidimensional array variable in vba for excel
  15. VBA using ubound on a multidimensional array
  16. Turn Excel range into VBA string
  17. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  18. Why am I having issues assigning a Range to an Array of Variants
  19. Subscript out of range error with an array – no idea why?
  20. VBA Excel large data manipulation taking forever
  21. Is indexing vectors in MATLAB inefficient?
  22. Create Chart from Array data and not range
  23. Excel VBA Quickest way to sort an array of numbers in descending order?
  24. Simple VBA array join not working
  25. Excel VBA – How to Redim a 2D array?
  26. ReDim Preserve “Subscript Out of Range”
  27. How can I call a macro which contains a loop but only run one iteration of that loop?
  28. Sorting a multidimensionnal array in VBA
  29. How to keep associative array order?
  30. VBA – Returning array from Property Get

Leave a Comment