Home » Blogs » Vishesh's blog

Array to speed up (Range vs Array)

Vishesh's picture
Submitted by Vishesh on 11 July, 2011 - 16:57
Below two procedures are different ways of populating a range of cells. The one at the bottom using arrays is the faster one. We can use the same to do processing/calculations in arrays and then put the calculated data back to range to speed up the process. Copy these procedures in a general module and run them (F5). 
Sub PopulatingRangeEachCell()
'This procedure populates the range one cell at time
    
    Dim rng         As Range
    Dim rngCell     As Range
    Dim lngCnt      As Long
    Dim stTime      As Date
 
    stTime = Now
 
    Set rng = Sheet1.Range("A1:A100000")
    For Each rngCell In rng
        lngCnt = lngCnt + 1
        rngCell.Value = lngCnt
    Next rngCell
 
    Set rng = Nothing
    Set rngCell = Nothing
 
    MsgBox "Start Time: " & Format(stTime, "hh:mm:ss") & vbCrLf & _
            "End Time: " & Format(Now, "hh:mm:ss") & vbCrLf & _
            DateDiff("s", stTime, Now) & " seconds", vbInformation, "Time Taken"
End Sub
 
Sub PopulatingRangeUsingArray()
'This procedure assigns range to array
'Then populates array and
'then assign array back to range

    Dim rng         As Range
    Dim lngCnt      As Long
    Dim stTime      As Date
    Dim arr
 
    stTime = Now
 
    Set rng = Sheet1.Range("A1:A100000")
    arr = rng
    For lngCnt = LBound(arr, 1) To UBound(arr, 1)
        arr(lngCnt, 1) = lngCnt
    Next lngCnt
 
    rng = arr
 
    Erase arr
    Set rng = Nothing
 
    MsgBox "Start Time: " & Format(stTime, "hh:mm:ss") & vbCrLf & _
            "End Time: " & Format(Now, "hh:mm:ss") & vbCrLf & _
            DateDiff("s", stTime, Now) & " seconds", vbInformation, "Time Taken"
End Sub
»