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


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext