Calculation - Controlling

Changing to Manual in your Macros

Start by defining a global variable that will contain the user's calculation mode before the macro is run.

Public glCalculationMode As Long 

Public Sub Macro_Start 
   glCalculationMode = Application.Calculation 
   Application.Calculation = xlCalculation.xlCalculationManual 
End Sub 

If you need to make any changes with automatic formula calculation, change the calculation to Automatic, make the changes and then set it back to Manual

Application.Calculation = xlCalculation.xlCalculationAutomatic 
'so whatever you need with automatic calculation switched on
Application.Calculation = xlCalculation.xlCalculationManual 

Once the macro has finished change the calculation mode back to what it was originally.

Public Sub Macro_Finish 
   Application.Calculation = glCalculationMode 

End Sub