How to control calculation of specific cells

  • Thread starter Thread starter bda75
  • Start date Start date
B

bda75

My purpose is to develop a UDF to link legacy spreadsheets content to a
central database.

I am trying to develop a UDF function that writes its arguments to a
database.
ex: DBWRITE(DSN,stored_proc_name, arg1, arg2, ...). The function would
return 'OK' or an error msg depending on how the transaction completes.

In order to minimize the traffic to the db, I would like to _delay_ the
calculation of the cells containing this UDF until the user clicks on a
'submit' button. But at the same time, I would like this to only affect
the formulas where the UDF arguments have changed, and all other cells
should continue to calculate normally.

Any idea on how this could be achieved ?
 
Some ideas...

The Calculate method applies also to the Range object (and to
individual sheets).

You can have the spreadsheet on manual calculation. Then the
Worksheet_Change event procedure can use something like:

Range("A2:B18,H3:H9").Calculate

And then the Submit button calls

Application.Calculate
-or-
ActiveSheet.Calculate

Does this help?
Kostis Vezerides
 
Back
Top