VBA macro calculation vs Excel formula insert

Question

I would like to ask you this query:

I have to conduct a calculation for a table of customers (let's say it's a SumIf formula summing up their payments from another sheet containing singular registered transactions)

I have done it with a macro first inserting a sumif formula in an unused column than copy the entire column values into the main table and than delete the formulas. In the end the data is correct and it doesn't include active formulas running all the time.

Thus my question for a situation like this would be: Which one would be more effective:

The way I have done (Macro used to insert formula, copy the column, delete all)

Or som other way doing the calculations within the macro?

The fastest way to make any calculations is using arrays, because the calculations will be performed on memory instead excel. Then paste your results to the sheet. If not, working with excel I would insert the formulas directly to the column you need and then do a `Range.Value = Range.Value` to leave only values and erase the formulas. — Damian, Jun 06 '19 at 08:11
Additionally we have to keep in mind that VBA is single-threaded, which means it won't take advantage of multiple cores in your CPU, while the worksheet interface will take advantage of multiple cores. — Pᴇʜ, Jun 06 '19 at 08:22
Formulae do not "run all the time". They run when the data change. So if you are not "using" Excel but simply running code on data, then there are far better tools out there. If speed is an issue, turn on manual calculation. The danger with your method is that it will be much harder to see where you got your result from. It is far easier to debug a simple formula than code. — , Jun 06 '19 at 08:25
@Gareth only as long as you don't use any volatile formula, which can cause heavy loads even if data is changed that is not involed in the formula itself. — Pᴇʜ, Jun 06 '19 at 08:26
@Pᴇʜ that makes me wonder... Could really excel perform calculations faster than vba using arrays? For that you would need a real good computer, right? Here at my job I have a i5-2400 3.1GHz and yesterday I tried to make a count if + if formula for 600k rows and it was taking years, then I switched to VBA and it was 1000000 times faster. Any insights? — Damian, Jun 06 '19 at 08:42
@Damian I think it depends on what you are trying to do. I agree that in your case I would use code and hard code the results. The same way I never use VLookup on huge data sets. I throw data into arrays, disconnected recordsets and dictionaries a lot of the time. For me it's a question of whether I am using Excel "as intended", (as a Spreadsheet) or whether I am using it out of necessity (because I don't have access to a SQL server) — , Jun 06 '19 at 08:55
@Pᴇʜ ... You were right... I tried and tested it. Macro using a formula is much faster than using loops. In this file (https://drive.google.com/open?id=1DGYF-Ix7cTOrE9e8kA1z82Sdr1_p3mAt) for 50k calculations, loop takes around `2.5 seconds` while Formula does that in `0.05 second`. I have attached the file with Macro if somebody wants to have a look. Learnt something new :) — Mikku, Jun 06 '19 at 08:57
It might depend on how you insert those formula as well... row by row? entire range? I mean without seeing the code is quite difficult to put in balance if your way would be faster than other way. I agree with Damian regarding using arrays for various simple processing, but I also aknowledge the worksheet formulas might do the same thing, faster with some complex formulas. @Gareth, try vlookup on a huge data set with an approximate match combined with an if statement. (if vlookup = value, then vlookup, else whatever). Ideally data to be sorted. — FAB, Jun 06 '19 at 09:17
Yes, it all depends on the circumstances. a vlookup/if is essentially a database process so I would always use a database if I could. But there are so many variables (amount of data, speed of computer, efficiency of code) that there is no single answer. — , Jun 06 '19 at 09:26
@Damian right, arrays might be even faster than formulas (see my answer). — Pᴇʜ, Jun 06 '19 at 09:57

Pᴇʜ · Accepted Answer · 2019-06-06T13:31:46.000

I put random values into range A1:F50000.

Here is an example for 4 different ways to calculate the sum of each row:

Formula:       211,6254      'Fill formula and replace with values (range)
LoopCells:     3909,2864     'Calculate each cell value (loop)
LoopWsFunc:    3103,0727     'Calculate each cell with worksheet function (loop)
LoopArray:     159,9878      'Calculate in array (read/write) at once

So actually using VBA and arrays is the fastest way (in this test case). But it might depend on the formula you use and how many data there is in the sheet. To be sure to use the fastest method for your specific case, code more than one way and test which one is faster.

Here is the code I used

Option Explicit

Public Sub TestFormula()
    Dim t As New cTimer
    t.StartCounter

    With Range("H1:H50000")
        .Formula = "=SUM(A1:F1)"  'write formula to all cells
        .Value = .Value           'replace formula by values
    End With

    Debug.Print "Formula:", t.TimeElapsed
End Sub


Public Sub TestLoopCells()
    Dim t As New cTimer
    t.StartCounter

    Dim iRow As Long
    For iRow = 1 To 50000   'calculate each cell by looping
        Cells(iRow, "H").Value = Cells(iRow, 1).Value + Cells(iRow, 2).Value + Cells(iRow, 3).Value + Cells(iRow, 4).Value + Cells(iRow, 5).Value + Cells(iRow, 6).Value
    Next iRow

    Debug.Print "LoopCells:", t.TimeElapsed
End Sub


Public Sub TestLoopWsFunc()
    Dim t As New cTimer
    t.StartCounter

    Dim iRow As Long
    For iRow = 1 To 50000   'Calculate each cell with sum function by looping
        Cells(iRow, "H").Value = WorksheetFunction.Sum(Range("A" & iRow & ":G" & iRow))
    Next iRow

    Debug.Print "LoopWsFunc:", t.TimeElapsed
End Sub


Public Sub TestLoopArray()
    Dim t As New cTimer
    t.StartCounter

    Dim InputArr() As Variant  'read data into array
    InputArr = Range("A1:F50000").Value

    ReDim OutputArr(1 To 50000, 1 To 1) As Variant 'generate output array

    Dim iRow As Long
    For iRow = 1 To 50000 'sum within the array
        OutputArr(iRow, 1) = InputArr(iRow, 1) + InputArr(iRow, 2) + InputArr(iRow, 3) + InputArr(iRow, 4) + InputArr(iRow, 5)
    Next iRow

    Range("H1:H50000").Value = OutputArr  'write the result from the array to the cells

    Debug.Print "LoopArray:", t.TimeElapsed
End Sub

Class Module `cTimer` for performance testing

Option Explicit

Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long

Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#

Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
    Low = LI.lowpart
    If Low < 0 Then
        Low = Low + TWO_32
    End If
    LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
    QueryPerformanceFrequency PerfFrequency
    m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
    QueryPerformanceCounter m_CounterStart
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
    QueryPerformanceCounter m_CounterEnd
    crStart = LI2Double(m_CounterStart)
    crStop = LI2Double(m_CounterEnd)
    TimeElapsed = 1000# * (crStop - crStart) / m_crFrequency
End Property

^{Source: VBA Implementation of QueryPerformanceCounter}

This class module looks like more precise than a regular Timer? because with timer I'm getting faster inserting a formula to all the cells at once from the file @Mikku uploaded, but with your class, array is twice faster. — Damian, Jun 06 '19 at 10:13
@Damian Yes, this class is much more precise than the VBA timer. Note that the comparison in the uploaded file would be the same comparison as `Formula` vs `LoopCells` in my example. Formula is faster than a loop through the cells. But arrays are even faster. At least for a simple formula (it might change on complicated formulas and need to be tested on the specific use case). — Pᴇʜ, Jun 06 '19 at 11:11
Yeah I know, I did the array test myself and was taking longer than the formulas, but with your class is not. That's why I asked. Thanks Pᴇʜ! — Damian, Jun 06 '19 at 11:16
@Damian That's actually strange then. Both timers should give approximately the *same* result just with different precision but not with different results. — Pᴇʜ, Jun 06 '19 at 11:19
@Pᴇʜ Thank you kindly I think this answers my question more than I could imagine. And Like others I must save this code of yours for future performance benchmarking. — Marcin Konopka, Jun 06 '19 at 13:21

VBA macro calculation vs Excel formula insert

1 Answers1

Here is the code I used

Class Module cTimer for performance testing

Class Module `cTimer` for performance testing