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

 

[Kenneth Hobson]

Move your code to set each cell to a VBA module. It would first check that the file exists using Dir().

 

[hobgoblinvasya]

hey tnx for quick reply !

i do thta already, but lets say a person filled out the spreadsheet will products and info was filled correctly, because that person has access to data. then he sends it to another person, who doesnt. what i want is the data to stick there, and if its impossible to recalculate, then just use the one that was put by the 1st person...

 

[Kenneth Hobson]

If you do it that way then I am not sure why you need a UDF. Of course one can use Functions in Subs even though they aren't used as UDF's. Have you looked into using Dir()?

Posting your code might help us help you.

 

[hobgoblinvasya]

what im trying to achieve it to mimic vlookup function. i cant use the regular one since it increases the file size from 500k to 4mb (since the source file is over 8000 rows and 30 columns). i dont want that, but if i uncheck the save external values in excel options, and send this file to someone, excel puts #N/A to all the lookup formulae.

what i need is a UDF function that does what the vlookup does, BUT keeps the previous value in the cell in case it fails to fetch a new one.

hope this is a bit clearer. if not i might try to upload a simple worksheet with the function i have so far and a sample data sheet.

 

[hobgoblinvasya]

hm.... still no answer?

is it because noone knows how do to it or because it is impossible to do? if so, another question... is there any way to store a second value in a cell, maybe in one of its properties.

ex: cell.value will have "Test" and cell.secondvalue will have "This is a testing cell".

basically what i need is to store a text value of the formula in the cell, WITHOUT removing the formula. as in the cell would calculate itself, if the calculation is correct it shows a value and stores a copy of it somewhere in its other properties.

i can't pput it in a hidden cell, since the code is executed in a UDF, hence its impossible to modify other cells.

thanks in advance

 

[BAlGaInTl]

You said that an authorized user creates the sheet and then sends out a copy.

Why not make another macro that the authorized user can utilize to create the copy. Then, it would be a simple copy/paste-special/values operation.

You could prompt it to save the file in the same location as the original with info appended to it.

You could even probably set it up to automatically mail it as an attachment...

The possibilities are endless.

 

[tusharm]

Does the recipient of the workbook get a 'update links?' question when they try to open the workbook? If so, one way out might be for them to respond 'no' since in that case Excel will retain the current values.

Another approach would be to replace the formulas in the worksheet with the current values before sending out the workbook. Select and copy the cells of interest. Then, Edit | Paste Special... | Values

hey tnx for quick reply !

i do thta already, but lets say a person filled out the spreadsheet will products and info was filled correctly, because that person has access to data. then he sends it to another person, who doesnt. what i want is the data to stick there, and if its impossible to recalculate, then just use the one that was put by the 1st person...

 

[hobgoblinvasya]

the answer to your first question is yes... he has to click on update values.

as far as the second suggestion, they dont want it grrrrrr.... i already proposed it to them and they didnt like it. so im stuck here at figuring out making it work by saving the formula result as a static value somewhere so i could reference to it in case the formula return an error.

 

[BAlGaInTl]

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