Speed up Excel VBA macros by turning calculations off and on

In large Excel workbooks when you are executing VB macros that change cells in sheets, you can speed up the process significantly by turning workbook calculations off at the begining of your subroutine, and then back on again at the end. Here are some subs that can do this. Simply call the “Calc_Off” sub at the beginning of your code, and the “Calc_On” sub at end end:

Sub Calc_Off()
  Application.Calculation = xlCalculationManual
End Sub

Sub Calc_On()
  Application.Calculation = xlCalculationAutomatic
End Sub

If you prefer, you can manually turn automatic calculations on and off from Excel's user interface.  From the File menu > Options > Formulas >  (Automatic or Manual calculation options).

Leave a Reply

Your email address will not be published. Required fields are marked *