How to retain cell value after VBA function #ARG error.

[psw]

Hi to everyone, i need your help. I have a problem.

I have VBA function, which connects to Oracle database and returns value from some select, e.g. select sum(inv_value) from sales.

1. User in location 1 connects to database and recalculates spreadsheet. Values are OK.

2. Then user sends this spreasheet to another location, where there is no possibility to connect to that database.

3. Then user2 in location 2 has to input some values manually, in cells without any formula's. And then, when user2 accidentally runs recalculating of sheet or cell formula(s) my VBA function returns #ARG error.

Is there any way to retain original value of cell, which has a formula, when functions returns #ARG error (because of no connection) ??? Thank you in advance (and sorry for my terrible english).

Piotr

[psw]

Any clues ?

[Ken Puls]

I think you have a bit of an issue on your hands here. I'm not aware of any way to pass the original value of the cell to your function. The problem, therefore, means that you have to stop the cells from calculating at all.

You could turn calculation to manual, and force recalculation of specific cells via macro, maybe, or potentially someone could write a class module to watch specific cells. The former is probably the easier method though.

[mdmackillop]

Hi Piotr
Welcome to VBAX, and no problems cwith you English.
I'm not fully comprehending your question, but could you attach a Values only copy of the spreadsheet (ie formulae removed) within your workbook?
Regards
MD

[psw]

No, i cannot. The reason is, that after manual input a spreadsheet returns to location 1 and formulaes are recalculated based on manually entered values:

location 1: recalculates sales formulas
location 2: manually enters forecast/budget of sales
location 1: recalculates profit formulas

Piotr