Hi Sandy,

I do not know of a way of detecting volatile UDFs.

Since you are using XL97 you will solve a number of your problems if you add
both an error handler and an ISEMPTY handler to your UDFs: for details and
examples see
http://www.DecisionModels.com/calcsecretsj.htm

Another approach you might consider is to set a global flag in your code,
and then check the flag in each UDF.

But IMHO the best and simplest approach is to control
application.calculation in your code: not sure why you do not want to do
this.


regards
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

"Sandy V" wrote in message
...

If a UDF in any open workbook contains
Application.Volatile, my code breaks off to complete the
UDF when my code does anything that causes a recalc, eg
putting a value in a cell (even into ThisWorkbook which
does not contain any UDF).

This causes problems:
- Sometimes when doing the UDF my code terminates. This is
more serious than a normal failure - code does not get
reset leaving unwanted global variables.
(Comments in this NG suggest this occurs when there is an
error in the UDF. That's certainly true, but sometimes and
inconsistently occurs even without any apparent error.)
- A lot of UDF's could slow my code, not so serious but
inconvenient.

Temporarily setting calculation to manual prevents
problems. However I neither need nor want to do this
except when absolutely necessary. But I do need to cater
for the unknown possibility of volatile UDF's lurking.

These problems occur in XL97, I gather (this NG) later
versions are less susceptible, if I could be confident(?)
these do not present an issue I would code for different
versions.

So what I would like to do is test for the existence of
anything volatile before setting calculation to manual.
But how?

I've thought of early in my code to put a dummy cell value
into ThisWorkbook, and try to:
- Detect if my code has broken off to do any UDF, or
- Detect if Excel is doing a recalc (apart from the dummy
cell input).
However I don't think it's possible to detect either.

Any other ideas or suggestions!

TIA for any advice,
Sandy