UDFs: A simple question.

[Mike Mertes]

I've noticed that UDFs won't auto-calculate, and I can't figure out why.
Even F9 and Shift+F9 will not force manual recalculation. The only way I can
get them to rerun the code in the UDF is to select the cell, click in the
formula bar as if I were going to edit the formula (or use F2), and enter
the formula again.

This leaves me with one simple question:
How do I get my UDFs to autocalculate, just as normal Excel functions do?

Thanks so much for all your help!
Mike Mertes

 

[Ron de Bruin]

Hi Mike

Ctrl-Alt-F9 will do a full recalc

You can add this line to your function(on top)
Application.Volatile

But it will not make Excel faster

 

[Bernie Deitrick]

Mike,

You can put

Application.Volatile

as the first line of your UDF, which will make it voltile (re-calced when
Excel calcs). Or, reference a cell in your functions parameter list to force
a dependency.

Also, Ctrl-Alt-F9 forces a complete re-calc.

HTH,
Bernie
MS Excel MVP

 

[Jerry W. Lewis]

Do you pass all cell dependencies as arguments, so that Excel can figure
out when a recalc is needed?

Jerry