Compare efficiency of excel formulas

Question

Is there any way to directly compare for calculation efficiency side by side two different formulas for achieving the same goal in excel?

Say I want to know which is more processor intensive (or if there's no difference) between, say,:

=countif(a1,b1) vs. =a1=b1

or

=vlookup(a1,B:C,2,0) vs. =index(C:C,match(A1,B:B,0)

how would I go about establishing this? Other than just copying the formula down 20 000 rows, and setting a stopwatch as I hit f9.

Answer 1

Something like this should work:

Public Function speedtest()
t = Now()
For i = 1 To 200000
    'c = Application.WorksheetFunction.CountIf(ActiveSheet.Range("A1"), ActiveSheet.Range("B1"))
    c = (ActiveSheet.Range("A1") = ActiveSheet.Range("B1"))
Next i
t1 = Now()
dt = Format(t1 - t, "ss")
ms = MsgBox(dt & " seconds", vbOKOnly)
End Function

The formula to be tested is inside the for loop. One is commented using ', so you can comment and un-comment to test one or the other