Disable Calculation of Cells when Running a Macro in Excel

Add to Favorites
Author:

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

a04423268900182a4c3e00fa558cb264.jpg

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

53e306a8466e710f82ed04c2ad741125.jpg

Here is the spreadsheet after I run the macro:

57ff98744408cc477e6a9363bb422dc1.png

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:

6db1302530fd0bcc37b66043db165de1.jpg

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.

6c5731adfcdf4ad5d91f03523a7341eb.jpg

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Prevent Excel Alerts and Messages Appearing While Running a Macro in Excel
Tutorial: How to stop an Excel alert window or message box from appearing while running a macro. Thi...
Run a Macro When a Specific Cell Changes in Excel
Tutorial: Run a macro in Excel when a specific cell is changed; this also covers when a cell within...
Get Data from the Worksheet into a Macro in Excel
Tutorial: Here, you'll learn how to get information from the Excel worksheet into a macro so you can...
Put Data into a Worksheet using a Macro in Excel
Tutorial: How to input data into cells in a worksheet from a macro. Once you have data in your macro...
What is a Macro in Excel?
Tutorial: This is the first step to learning about Macros for Excel and how to use them.  What is a...
5 Simple Tips for Making BETTER Macros in Excel!
Tutorial: 5 ways to make better Macros in Excel - fewer errors, smoother coding, less fuss! These 5...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course