Restoring the current result of failing user-defined functions

  • Thread starter Thread starter Nacho Nachev
  • Start date Start date
N

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
 
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
 
You will have to checkmark the iteration checkbox in the calculate tab of
Tools=>Options to allow circular references.
 
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
 
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
 
Hi Charles,
This works OK for me when I turn on iteration: what problem do you get?
Click to expand...

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
Click to expand...
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!
 
Back
Top