UDF return previous value

[hobgoblinvasya]

hey everyone, my question is pretty straightforward:

is there a way for a udf to return the cell's original value instead of an error.

ie. the UDF in the cell returned a value from a database. now if i move the database and reopen this sheet, excel will try to recalculate and if it doesn't find the database it will return an error. instead of that i just want excel to return whatever was in the cell, before it had to be recalculated.

is there any way to achieve this behaviour?

tnx in advance

 

[hobgoblinvasya]

Well...

You could do a BeforeSave event...

Take all of the values that are currently in the sheet and do a copy/paste special/values in to a second sheet called LastData or something like that.

Then use an IF statement with a check for the error.

=IF(ISERROR(Your UDF),Reference from LastData Sheet,Your UDF)

But their logic confuses me...

hm.... thats actually in interesting idea - to recreate the values on another sheet AT WORKBOOK_SAVE. haven't thought of that. :D will try to play with it some more but looks promising.

thanks a bunch dude. now thats why you are excel mvps and im a simple noobie