How to control calculation of specific cells
 

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 ?

How to control calculation of specific cells
 

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



bda75 wrote:
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 ?