UDF recalculation and speed

[Andrew Fergus]

Hi

I'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line

Application.Volatile

to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box :

Sub DropDown4_Change()

Application.CalculateFull

End Sub

but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event.

So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box?

And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?

TIA, Andrew

 

[Richard Schollar]

Andrew

I expect this has been mentioned before (didn't see it on a brief trawl of the above posts - but could well have missed it!) but you should pass in (as an argument to the function) any external data on which the function relies (in this case your month number), so that when Excel builds its dependency tree for formula recalculation, it knows that your UDF needs to be recalc'ed if one of its arguments have changed. This should improve performance, as it also avoids multiple recalculations (as a result of an improved dependency tree).

Richard

 

[Andrew Fergus]

Hi Richard

I saw that mentioned in the link from Kris but I am of two minds on that one (or am I?). If I include it as a dependant object, then whenever the user changes the date it will update the regional reports automatically which is what I originally asked for, but it will slow down the workbook (and keep in mind the regional reports will be used less than 10% of the time). But if I don't and the user has to click a button to activate the recalc, then according to the link Kris provided, this would be the fastest way of recalcing the UDF values. I think I also read that you can avoid mulitple recalcs by declaring the UDF as a private function. I shall put all this to the test when I get back to work tomorrow (it is now 9.30pm here).

Cheers, Andrew