I have created a few functions in VBA in XL XP, all of which work
beautifully, but only if I click on the cell containing it, then in the
edit window at the top and press enter to run it. I have calculation
set to automatic in Tools/Options. It won't even run if I press F9!
Naturally, I would like the functions to calculate automatically--just
as my other Excel functions do.

Can anyone help shed light on what is going on here?

Much thanks,

Paul.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

often you must include the line
application.volatile
as the top line in the udf.

"paulsza" wrote in message
...
I have created a few functions in VBA in XL XP, all of which work
beautifully, but only if I click on the cell containing it, then in the
edit window at the top and press enter to run it. I have calculation
set to automatic in Tools/Options. It won't even run if I press F9!
Naturally, I would like the functions to calculate automatically--just
as my other Excel functions do.

Can anyone help shed light on what is going on here?

Much thanks,

Paul.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

do
Ctrl+Alt+F9

Excel recalculates only cells that need to be recalculated. It determines
if your function needs recalculation by checking if any cells it depends on
have changed. It identifies these by looking at the arguments to the
function. If you functions don't use cell references, then you need to make
them volatile so they are recalculated on every calculation. As the first
line of the function, put in

Application.Volatile

If you can avoid this by having dependencies in the arguments, this will be
much better.

--
Regards,
Tom Ogilvy

paulsza wrote in message
...
I have created a few functions in VBA in XL XP, all of which work
beautifully, but only if I click on the cell containing it, then in the
edit window at the top and press enter to run it. I have calculation
set to automatic in Tools/Options. It won't even run if I press F9!
Naturally, I would like the functions to calculate automatically--just
as my other Excel functions do.

Can anyone help shed light on what is going on here?

Much thanks,

Paul.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/