Jon's Programming Blog

I have one Time Card template setup with SUMIFS (used for Excel 2007 and above templates) and one set up with MATCH/INDEX (used for my 2003 and below templates). I tested the calculation speeds for each in Excel 2013 (preview) on Windows 8.

Here’s the VBA test code:

Sub test()

    Dim dTimer As Double
    Dim iLoops As Integer, iTotalIterations As Integer
    Dim wksStage As Worksheet

    Set wksStage = ThisWorkbook.Worksheets("Staging Area")
    iTotalIterations = 1
    dTimer = Timer()

    For iLoops = 0 To iTotalIterations
        wksStage.Calculate
    Next iLoops

    Debug.Print Timer() - dTimer

End Sub

And here’s the results (seconds):

Iterations SUMIFS MATCH/INDEX

1 50 0.20 3.07 0.09 1.30

So where do I go from here? I think I will keep the two separate templates for now, even though I could go to one. The reasons?

• It is much easier to change SUMIFS than the complicated MATCH/INDEX functions.
• I think I could introduce an error in the MATCH/INDEX functions more easily.

I will post the 2003 template for anyone that wishes faster calculation times, but it is pretty fast now, so I don’t know if that would be a huge issue.