CODEWITHSUNDEEP
vbaexcelloopsfor-loop
сами J.D.
сами J.D.

Reputation: 513

Is it more optimal to process Range values in for loop or with array

I want to know what is better to populate the cells in a Range:

Here is my code:

' Count number of rows and columns we have
rowCounter = CountRowsFunction
colCounter = CountColsFunction

' Do operations needed
For i = 2 To rowCounter
    originalSheet.Cells(i, colCounter + 1).Value = Round(DateDiff("n", originalSheet.Cells(i, ColumnsIndex(2)).Value, originalSheet.Cells(i, ColumnsIndex(3)).Value) / 60, 2)
Next i

In this case, I'm accessing the cells directly. But, I believe this may cause unnecessary calls to the spreadhseet. Thanks

Upvotes: 1

Views: 123

Answers (2)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

The usage of an array to set multiple cell values is vastly superior. In the example below the range A1:MZ390 (152100 cells) are set with two methods:

  1. Set all cells to 1; iterate cells and set each cell value to 2
  2. Set all cells to 1; assign range to array; iterate array and set all array values to 2; assign back to range

Method 2 takes less than a second and method 1 takes > 4 seconds on my PC.

In the example it iterates the array, but you can use less code lines and just do varData = 2 - but it is unlikely that people want to set a bunch of cell values to a constant.

Option Explicit

Sub Test()

    Dim dt1 As Date, dt2 As Date
    Dim lngX As Long, lngY As Long
    Dim varData As Variant
    Dim ws As Worksheet
    Dim rng As Range

    'set ws
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    'for loop method - without screen updating
    Application.ScreenUpdating = False
    ws.Cells.Delete
    Set rng = ws.Range("A1:MZ390")

    dt1 = Now
    rng.Value = 1
    For lngY = 1 To rng.Rows.Count
        For lngX = 1 To rng.Columns.Count
            rng.Cells(lngY, lngX).Value = 2
        Next lngX
    Next lngY
    dt2 = Now
    Application.ScreenUpdating = True
    Debug.Print "For loop (without screen updating) took: " & Format(dt2 - dt1, "s") & " seconds"

    'array method
    ws.Cells.Delete
    Set rng = ws.Range("A1:MZ390")

    dt1 = Now
    rng.Value = 1
    varData = rng.Value
    For lngX = 1 To UBound(varData, 1)
        For lngY = 1 To UBound(varData, 2)
            varData(lngX, lngY) = 2
        Next lngY
    Next lngX
    rng.Value = varData
    dt2 = Now
    Debug.Print "Array method took: " & Format(dt2 - dt1, "s") & " seconds"

End Sub

Upvotes: 3

Nathan_Sav
Nathan_Sav

Reputation: 8531

Arrays are more efficient I believe, I use them when using a lot of data.

You'd use something like this, add a break point at the start of the loop, and use the locals window to view the array a.

Dim a() As Variant
a = Range("a1:a10").Value

For i = 1 To UBound(a)

Next i

Upvotes: 1

Related Questions