Can I force my UD Functions to recalculate?

[Michelle]

I have a project with lots of functions, which I didn't make 'volatile' (I
realise now, that that was a mistake).

I want the users of the spreadsheet to be able to press a key or take an
action which will make the functions recalculate.

For reasons that are too involved to mention here, I don't want to add any
code to the functions now.

I just want to provide the user with an action they can take to make
everything update.

F9, Tools-options-CalcNow don't work. Any ideas?

Thanks
M

 

[Charles Williams]

If you cannot change the code in your functions your only option is to tell
your users to use
Ctrl/Alt/F9 to force Excel to recalculate every single formula in every open
workbook.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Niek Otten]

Just to add to Charles' reply:

You said it was a mistake not to make the functions volatile. I don't agree.
If your functions process data from your worksheets, the only right thing to
do is to supply that data in the arguments of the function. So don't access
tha data directly from within your function.
Then you don't need Application.Volatile and the functions will be
recalculated if and only if the precendent cells have changed.
It might be different if your functions get data from outside Excel, which
is refreshed without Excel knowing.

 

[Michelle]

Thanks Charles - that works a treat!

M

If you cannot change the code in your functions your only option is to
tell your users to use
Ctrl/Alt/F9 to force Excel to recalculate every single formula in every
open workbook.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com