Speeding up macros – calculation

Stop Excel calculating

By Neale Blackwood

One technique to speed up macros is to turn off Excel’s automatic calculation at the beginning of your macro and then turn it back on at the end of your code. If you then need the spreadsheet to calculate during your code there is a command that will perform a calculation. The commands to turn calculation off and on are:

Turn off (start of your code)

Application.Calculation = xlCalculationManual

Turn on (end of your code before the End Sub)

Application.Calculation = xlCalculationAutomatic

The command to calculate during your code is

Application.Calculate

You can use this command as many times as you want in your code. It’s the same as pressing F9 to calculate when using Excel normally.

Warning: If you turn off calculation and your macro crashes (and you don’t have built-in error handling) then the automatic calculation will be turned off when you return to the spreadsheet. You will have to turn it back on manually through the Tools menu and Options or the Formulas ribbon.