Hi Sandy,


[control calculation]
not sure why you do not want to do this
There are various minor reasons which would take a
convoluted explanation to describe. But one reason is an
unpredicted failure of my code would leave the user's
calculation mode changed.


I would:
- save the current calculation mode
- trap errors with an on error handler
- restore calculation mode if neccessary in the error handler

However for XL97 I will follow your advice and set
calculation to manual.
Could I also ask you to confirm either way if same is
necessary or recommended for later versions.


Excel 97 has a particular problem with UDFs with errors so it is worse than
other versions.
In general I would recommend going to manual for other versions as well, but
it depends on the circumstances.


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

Many thanks,
Sandy
savituk yahoo co uk

-----Original Message-----
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



.