6

I have a rather large workbook, and some users prefer to have automatic calculation on, while others prefer having it off.

Some macros that interact with the sheets are attatched to the workbook. To speed up my VBA code, I set

Application.Calculation = xlManual

In the beginning of each workbook.

My question is, how do I revert it back to the way it was in the end of macro?

Right now i'm using

Application.Calculation = xlAutomatic

but that overrides the users choice. It's pretty annoying having to change it back to manual every time the macro has run. (Or the other way around, if leaving it at manual.)

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
johs32
  • 63
  • 1
  • 4

1 Answers1

5

Store the setting before starting, then restore when finished:

Sub calctest()
Dim calcsetting As Integer

    calcsetting = Application.Calculation
    ' Put code here
    Application.Calculation = calcsetting
End Sub
Carol
  • 471
  • 4
  • 7
  • Great, can't believe I couldn't find that anywhere. Thank you! – johs32 Nov 29 '17 at 06:40
  • 2
    @carol @johs32 `calcsetting` should be at least of type `Long` or better of type [`XlCalculation`](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlcalculation-enumeration-excel?f=255&MSPPError=-2147217396) (which also enables IntelliSense for this variable). You can see this if you do a `Debug.Print VarType(Application.Calculation)` it will be `3` which means `Long` (according to [VarType Function](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/vartype-function)). Best Practice: [Avoid Integer and always use Long](https://stackoverflow.com/a/26409520/3219613). – Pᴇʜ Nov 29 '17 at 07:04
  • 4
    You may also want to use error handling so that the calculation is restored, even if your other code fails – ThunderFrame Nov 29 '17 at 07:54