Restoring the current result of failing user-defined functions

[Nacho Nachev]

Hello,

In my project I create user-defined functions in Excel. Some of them do I/O
operations that are not always available. In such case a user-defined
function returns #INVALID value to the result cell.

My question: If there is previously calculated value in the result cell, is
it possible to restore it in case my functions are not able to return a
value at a time? Is there some API or technique that can be used?

I understand that there could be certain complications - for example in case
the UDF is part of a formula.

Thank you for your time!

Nacho

 

[Nacho Nachev]

I discover that I can use Application.Caller property to identify the UDF
caller cell, but when retrieving then I have circular reference error. Any
idea how I can avoid this problem?

Thank you,
Nacho

 

[Tom Ogilvy]

You will have to checkmark the iteration checkbox in the calculate tab of
Tools=>Options to allow circular references.

 

[Nacho Nachev]

This works excellent for me. Unfortunately, this won't work if the UDF if
part of formula expression. For instance:

= Max(MyFunc(A1), 10)

If I find the root Caller for the whole formula expression, this will work
fine for me. Does anybody have any suggestion?

Thank you,
Nacho

 

[Charles Williams]

Hi Nacho,

This works OK for me when I turn on iteration: what problem do you get?

You might want to consider an alternative technique to circular references:
- when your function exits cache the result somewhere (static or global
variable or use Range.ID)
- then if your I/O operation fails return the previous result from the cache

This would avoid the unfortunate side-effects of using circular references.


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Nacho Nachev]

Hi Charles,

This works OK for me when I turn on iteration: what problem do you get?

I am sorry. I have had an error in my test case. It seems that it works fine
for me too, when I turn on iteration.

You might want to consider an alternative technique to circular references:
- when your function exits cache the result somewhere (static or global
variable or use Range.ID)
- then if your I/O operation fails return the previous result from the

cache

Your suggestion has been my original thinking about the problem. However, I
wanted to be sure that there is no other more trivial way that is inherent
to Excel design. Well, at this point I consider that the approach you
suggest is the most resonable. Still I'll elaborate a bit more on the Caller
approach to explore the side effects and see if the will hinder my macros.

Thank you!