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
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.