Tutorial Details
Downloadable Files: Excel File
Introduction to Programming Macros in Excel
First Steps
Getting and Inputting Data
Adding Logic to Macros
UDF- User Defined Functions
Speeding Up Macros

Disable Calculation of Cells when Running a Macro in Excel

Add to Favorites

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.






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



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.



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.

Question? Ask it in our Excel Forum

Downloadable Files: Excel File