Disable Calculation of Cells when Running a Macro in Excel

How to stop formulas and functions from updating in Excel when running a macro. This can save you a lot of time if you have a large or complex spreadsheet that relies on lots of formulas or cross-spreadsheet functions.

Sections:

Code

Example

Notes

Code

Disable Calculations

Prevent Excel from updating formulas and functions automatically. This should go at the start of your macro.

Application.Calculation = xlCalculationManual

Enable Calculations

Enable normal formula/function updating once again. This should go at the end of your macro.

Application.Calculation = xlCalculationAutomatic

Example

Here, I will make a small macro that inserts the value "hi" into cell C1. In the same worksheet, I have a few RAND() functions that generate a random decimal number each time the worksheet is recalculated, such as when a value is input into a cell.

Here is the full macro:

Sub insert_value()

Application.Calculation = xlCalculationManual

'Code to run here
Range("C1").Value = "hi"

Application.Calculation = xlCalculationAutomatic
End Sub

Here is the spreadsheet after I run the macro:

You can see the three RAND values on the left.

If you just ran the macro you will notice that the RAND values did update! However, it will have happened so fast that you couldn't have noticed that the calculation did not happen after the value was input into cell C1 but, instead, it happened when calculations were turned back on at the end of the macro.

Note: once you turn the calculations back on, everything will recalculate at that moment.

If you do not want the spreadsheet to recalculate, just remove or comment-out the last line like this:

When I run this code, the RAND values will now not update at all.

Note: if you turn calculations off in a macro, that means that you will need to manually turn them back on or manually calculate them.

Turn Calculations (back) On Manually

Formulas tab > Calculation section > Then click Calculate Now or Calculate Sheet to calculate the worksheet but leave the calculation setting the same or click Calculation Options and then choose the desired calculation setting.

Notes

This is one of those things that is not always needed for your macros. If you have a small worksheet and there are no performance issues when updating formulas and functions, then don't worry about using this.

However, if you notice a lag from when you enter data to when the spreadsheet "catches-up" and updates everything, then this code will help your macros run faster.

In the attached file, the calculation code has been commented-out so you can test it with and without that code.

Make sure to download the attached file to work with these examples in Excel.