Toggle Calculation (2003)

Topic

New Reply

Hi,

Is it possible to toggle between manual and automatic calculation with vba?

Regards

Nath

Reply | Quote

Replies

Yes, you can use

Application.Calculation = xlCalculationManual

Application.Calculation = xlCalculationAutomatic

and

Application.Calculation = xlCalculationSemiAutomatic

to set calculation to manual, automatic and automatic except tables, respectively. It can be very useful to set calculation to manual at the beginning of a macro that changes many values that will affect formulas, then set it to automatic again at the end (this will trigger a full recalculation).

Reply | Quote

Thanks Hans..

Reply | Quote

>Is it possible to toggle between manual and automatic calculation with vba?

Thought-provoking question, thanks.

Of greater interest (to me, at any rate), is how to hook the three “Application.Calculation” values into a deep and meaningful relationship with “Application.Volatile”.

My (novel) understanding of “Application.Volatile” is that when set to True it forces recalculation of the cell using the UDF whenever recalculation occurs anywhere in the worksheet (but Help doesn’t make it clear whether they mean Workbook or Spreadsheet).

Inhibiting automatic calculation (Application.Calculation = xlCalculationManual) means that calculation will take place only when I tap the F9 key.
Then if “Application.Volatile=True” I’ll see my UDF results updated, but if “Application.Volatile=False” I’ll see my UDF remain unchanged, unless the input variables change.

In short, one needs a multi-dimensional table to track through the combinations and their benefits.
One dimension would be “testing/production”, since when I’m testing UDFs I don’t necessarily need to see every cell updated.
One dimension would be “Volatile” settings
One dimension would be “Calculation” settings.

In today’s version of XL2003/VBA help (v6.5) the Volatile setting is listed as a Method; I think it ought to be listed as a Property.

Reply | Quote

You should use:
Application.Volatile True
with no equals, hence it is a method not a property.

Reply | Quote

And where does the specific sheet property EnableCalculation fit in to all this???
You can turn calc mode off for specific sheets, and on and off via VBA code.

zeddy

Reply | Quote