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


"Nacho Nachev" wrote in message
...
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

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

--
Regards,
Tom Ogilvy



"Nacho Nachev" wrote in message
...

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


"Nacho Nachev" wrote in message
...
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

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


"Tom Ogilvy" wrote in message
...
You will have to checkmark the iteration checkbox in the calculate tab of
Tools=Options to allow circular references.

--
Regards,
Tom Ogilvy



"Nacho Nachev" wrote in message
...

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


"Nacho Nachev" wrote in message
...
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

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" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
You will have to checkmark the iteration checkbox in the calculate tab
of
Tools=Options to allow circular references.

--
Regards,
Tom Ogilvy



"Nacho Nachev" wrote in message
...

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


"Nacho Nachev" wrote in message
...
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

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!

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


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

"Nacho Nachev" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
You will have to checkmark the iteration checkbox in the calculate tab
of
Tools=Options to allow circular references.

--
Regards,
Tom Ogilvy



"Nacho Nachev" wrote in message
...

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


"Nacho Nachev" wrote in message
...
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