Performance tradeoffs of complex worksheet functions vs VBA? when to switch to a UDF?

[Keith R]

Although I also feel fairly comfortable with VBA, I'm currently working on
a project using just formulas. However, as my workbook grows (gets more
data), it is slowing exponentially because of the formulas I'm using (I'll
paste examples below).

Are there any specific worksheet functions that can be replaced with VBA to
improve performance, or will I be looking at the same amount of calculation
time regardless of whether I use worksheet functions vs UDFs or other VBA
code? If there are particular functions that can be sped up in code, is
there a list anywhere so I know which ones to look out for?


For each day that data is added, I add (autofill) a new line that contains
each of the following formulas (note some are array formulas):

=TEXT(MONTH(A16),"00")&TEXT(DAY(A16),"00")&TEXT(RIGHT(YEAR(A16),2),"00")

=(SUM(IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)=A16,INDIRECT(B16 &
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),"")))

=AVERAGE(IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)=A16,INDIRECT(B16 &
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),""))

=SUM(IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F14000"),0)=A16,1,0))

=SUM((IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)=A16,1,0))*(IF(INDIRECT(B16
& "!G2:G140")-INDIRECT(B16 & "!F2:F140")<=0.00347222222222222,1,0)))
(and about 6 others just like the one above, but with different comparison
values at the end; 5 min, 10 min, 15 min, etc)

=SMALL((IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)=A16,INDIRECT(B16 &
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),"")),COUNT(IF(ROUNDDOWN(INDIRECT(B16
& "!F2:F140"),0)=A16,INDIRECT(B16 & "!G2:G140")-INDIRECT(B16 &
"!F2:F140"),""))*0.8)

=STDEV(IF(ROUNDDOWN(INDIRECT(B16 & "!F2:F140"),0)=A16,INDIRECT(B16 &
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),""))

I also have some array formulas on another sheet (one for each day of the
month, plus about 15 others) but I don't add more of those each time, they
are static and only update their values (when new data is added to the
workbook)

I only have about a month of data in so far, and it takes up to 15 seconds
to update the workbook every time I change anything. Other than buying a
faster computer, would UDF's help from a performance perspective? any other
ideas? My user isn't particularly computer-savy, so I'm trying to avoid
turning autocalculation on/off.

Many thanks,
Keith R
XL97

 

[Peter Atherton]

Keith

You can use Excel functions in your code.

e.g application.worksheetfunction.rounddown(c,2)

However, if the function is available in VB then the VB
function takes precedence.

The code will enter the values as text and so reduce the
size of the workbook and speed up loading.

You can create your own functions but these will always be
slower than Excels, which are created in C. VB is not as
efficient as the C family.

I suppose you have already switched the calculation to
manual with F9 to calculate this will prevent Excel
working while you are entering data

Regards
Peter

 

[Harald Staff]

Hi Keith

An UDF will be slower than a built-in function. But it may not recalculate on /any/ entry
into Excel, just when its own parametres change.

What happens is that everything recalculates whenever something is entered. This thing
=TEXT(MONTH(A16),"00")&TEXT(DAY(A16),"00")&TEXT(RIGHT(YEAR(A16),2),"00")
only needt to calculate when A16 is changed, and maybe that only happens once, on first
entry. You might consider an UDF for that reason, or you might replace the formula with
the result value once calculated, manually or by a macro. Or you might have a macro do the
whole job on "A cell" entries, totally functionless at the end.

Those are the considerations (or at least mine in such situations . How to solve them
is a question of style, taste and user friendliness. While macros may produce the coolest
results, they often depend on a fixed geography and that the user sees through and feel
comfortable with the "may contain virus" nonsense on file open.

 

[Keith R]

Thank you to all who have replied so far;

it sounds like I can mess with autocalculation settings, which I want to
avoid, or I can use a UDF linked to the source cell (all in column A) so
that XL doesn't update everything, every time a cell is changed.

So here's my followup question; given that I have some fairly complex
formulas (see original posting if desired), can I do either of the
following?

(1) is there any built-in way to force a formula link to the cell in column
A, in my existing formulas, so that calculation _only_ occurs when that
cell changes, or,

(2) is there a way to build a "pass through" UDF, so that I could nest my
existing formula into the UDF and have the same effect? e.g.,

=MyUDFName(A17, (SUM(IF(ROUNDDOWN(INDIRECT(B16 & _
"!F2:F140"),0)=A16,INDIRECT(B16 & _
"!G2:G140")-INDIRECT(B16 & "!F2:F140"),"")))

I'm leaning toward option 2, but I'm not sure how that would work, given
that when I call functions from VBA I always have to use the long version,
e.g. 'Excel.worksheetfunction.functionname' instead of the formula as
written, above.

I'd still like to avoid recreating the contents of every single formula in
VBA (including as a UDF) if possible...

Many thanks,
Keith R
XL97

 

[Harald Staff]

Hi Keith

Yes. In theory it's like this, paste in a module:

Function MyUdf(dblA As Double) As Double
'demo only:
Debug.Print Now, dblA 'to display recalculation
MyUdf = Application.RoundDown(dblA / 3, 1)
End Function

And in a sheet, cell B1
=MyUDF(A1)
fill down to B100. Now watch the immediate window in the VB editor while
entering numbers in the A column -only the entered row is calculated.

I don't have time to invesatigate your original formula, but I hope you can
get it to work along these lines.
Array formulas may be far more tricky I guess...