Speed of Functions vs. other Functions vs. VBA Macro

Question

Hello,

I was wondering if there any way to measure the processing speed of certain functions against others and/or against Macros.

I some times have complex and long formulas that use functions like LOOKUP, INDEX, OFFSET, associated with MATCH. I was wondering if some of these are more optimized for having less heavy calculations.

Another issue is the fact that some of these functions are quite long because of sequencial conditions that need to checked but require repeating long "sub formulas".

For example, IF([long formula]=[another long formula], 1, IF([long formula]>[another long formula],2, IF([long formula]<[another long formula],3,0))) - obviously not a real example, but within these formulas there could be lookup formulas etc.

One way (when possible) is to have these "sub-formulas" in a named cell, and link to them. Is that a more "processing-efficient" way of doing it?

Another way is to write costume functions with VBA, is that more processing-efficient? - it's easier on the eye and on the writing.

Any ideas about this?

I would be grateful for some insights into this.

Thanks, N

Answer 1

Most people use the following paradigm:

Option Explicit

Public Declare Function QueryPerformanceFrequency Lib "kernel32" _

   (ByRef freq As Currency) As Long

Public Declare Function QueryPerformanceCounter Lib "kernel32" _

   (ByRef cnt As Currency) As Long

Private freq As Currency, df As Double

Sub testit()

Dim st As Currency, et As Currency, dt As Double

QueryPerformanceCounter st

'.... whatever you want to measure ....

QueryPerformanceCounter et

dt = convertmytimer(et - st)

MsgBox Format(dt, "0.000000000") & " sec"

End Sub

Function convertmytimer(ByVal dt As Currency) As Double

If freq = 0 Then QueryPerformanceFrequency freq: df = freq

convertmytimer = dt / df

End Function

But they are fairly naive about it.

For example, if you simply measure Range("A1").Dirty (in Automatic calculation mode) or Range("A1").Calculate (in Manual calculation mode), much, if not most, of what you measure is overhead that is unrelated to measuring the formula in A1.

The source of the overhead is two-fold: (a) VBA overhead due to first-time execution of statements ("delayed binding"); and (b) system overhead in interprocess communication between the VBA and Excel threads.

So it is not uncommon for people to conclude that there is no significant performance difference, simply because the formula execution time is so very small compared to the overhead.

Secondly, if you measure the formula execution many times, you will probably notice a significant variability in the execution time. There are many possible reasons for the variability. But the point is: if you measure only one time, you might not measure the minimum or even the typical time.

There is more to be said. But suffice it to say: performance measuring is as much art as it is science. GIGO.

Answer 2

Thank you Joeu,

What about internal functions to Excel, is there substantial difference between them.

For instance, 

Are array formulas slower than normal ones. Is a If sequence faster than introducing AND/OR comparisons?

=IF(AND(condition 1,condition 2), This, that)

=IF((condition 1)*(condition 2), This, That) Ctrl+Shift+Enter

=IF(condition 1, this, if (condition 2, this, That) 

or

=SUM((range2 condition)*1*(range1)) Ctrl+Shift+Enter

=SUMIF(range2, condition,range1)

Or between OFFSET, INDEX, LOOKUP etc.

I don't know why I quite like array formulas but I am wondering if those are slowing down the calcs.

Thanks

Answer 3

Hi Nsgma365,

Depending on the processing speed and memory of your computer, large array formulas can slow down calculations.

You can refer to the articles below for more information about Excel calculation performance:

Excel performance: Improving calculation performance

Guidelines and examples of array formulas

Regards,

Tina

Answer 4

Hi Nsgma365,

Did you refer to the suggestions? Feel free to let us know the result.

Regards,

Tina