Detect any Volatile UDF

[Sandy V]

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

 

[Charles Williams]

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]

Hi Charles,

First of all I regard your excellent site as my first port
of call for anything to do with calculation - thanks for
providing it.

My code is for distribution to others and I have no
knowledge or control over their UDF's, if indeed they have
any. Hence I cannot take the precautions you suggest re
UDF's

[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.

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.

Many thanks,
Sandy
savituk yahoo co uk

 

[Charles Williams]

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

 

[Sandy V]

Hi Charles,

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

I am already saving the original calculation mode.
Currently I restore this just before my code terminates in
the normal way. However there is a potential problem even
with this - setting calculation back to automatic triggers
an immediate recalc, if a UDF error occurs my code
terminates early. I am thinking to put the restore code
into a separate sub, calling it with OnTime to be done
when my normal code has run it's course.

I am unclear as to how to set up an error handler in my
code to trap for errors in unknown UDF's in other WB's.
Establishing an error handler just before anything in my
code that causes a recalc followed by a UDF error get's
lost!

In general I would recommend going to manual for other

versions as well
Ok I will go with this as "circumstances" outside my WB
are unknown, thanks.

Sandy
savituk yahoo co uk

-----Original Message-----
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
your
problems if you add do
not want to do in
message This
is is
an sometimes
and confident
(?)

.