Excel VBA- Codes speed test

By Ajeet Kumar

EXCEL VBA: COMPARISON AMONG RANGE VS ARRAY BASED CALCULATION

You can carry out same task in different ways in Excel VBA. Suppose you want to add the values of columns A and B to column C. To do this, you can use different methods as shown below. But the time taken to finish the task will be quite different based on technique used. Note that the data range must be large enough to feel the differences in computation time to be significant.

Method 1: Computation Without Using Loop

Sub pR1C1Fastest()

    Dim lngSTART As Long

    Dim lngEND As Long

    lngSTART = Timer

        Range("C2:C900000") = "=RC[-1]+RC[-2]"

    lngEND = Timer

    MsgBox lngEND - lngSTART

End Sub

Method 2: Computation Using Loop in An Array

Sub pArrayFaster()

    Dim varData

    Dim rngData As Range

    Dim lngr As Long

    Dim lngSTART As Long

    Dim lngEND As Long

    lngSTART = Timer

    Set rngData = Range("A2:B900000")

    varData = rngData

    For lngr = LBound(varData) To UBound(varData)

        varData(lngr, 1) = varData(lngr, 1) + varData(lngr, 2)

    Next

    Range("C2:C900000") = varData

    lngEND = Timer

    MsgBox lngEND - lngSTART

End Sub

Method 3: Computation Without Using Loop: In fact, it is Method1 but the formulas are converted into values.

Sub pR1C1Fast()

    Dim lngSTART As Long

    Dim lngEND As Long

    lngSTART = Timer

        Range("C2:C900000") = "=RC[-1]+RC[-2]"

        Range("C2:C900000").Value = Range("C2:C900000").Value

    lngEND = Timer

    MsgBox lngEND - lngSTART

End Sub

Method 4: Computation Using Loop on cells of sheet

Sub pRangeUseTooSlow()

    Dim lngr As Long

    Dim lngSTART As Long

    Dim lngEND As Long

    lngSTART = Timer

    For lngr = 2 To 900000

        Range("C" & lngr) = Range("A" & lngr) + Range("B" & lngr)

    Next

    lngEND = Timer

    MsgBox lngEND - lngSTART

End Sub

Method 5: Computation Using Dictionary Object