VBA – Turn Automatic Calculations Off (or On)

Associated Files Download Links

Whenever you update a cell, Excel goes through a process to recalculate the workbook. When working directly within Excel you want this to happen 99.9% of the time (the exception being if you are working with an extremely large workbook). However, this can really slow down your VBA code. It’s a good practice to set your calculations to manual at the beginning of macros and restore calculations at the end of macros. If you need to recalculate the workbook you can manually tell Excel to calculate.

Turn Off Automatic Calculations

You can turn off automatic calculation with a macro by setting it to xlmanual. Use the following piece of VBA code:

Turn Automatic Calculations Back On

To turn back on automatic calculation with the setting xlAutomatic:

I recommend disabling Automatic calculations at the very beginning of your procedure and re-enabling Automatic Calculations at the end. It will look like this:

Disable Automatic Calculations Macro Example

vba disable automatic calculations

Manual Calculation

When Automatic calculations are disabled, you can use the Calculate command to force Excel to recalculate:

You can also tell Excel to recalculate only an individual worksheet:

You can also tell VBA to recalculate just a range (click to read our article about VBA calculation methods)

Here is how this might look inside a macro:

VBA Settings – Speed Up Code

If your goal is to speed up your code, you should also consider adjusting these other settings:

Disabling Screenupdating can make a huge difference in speed:

Turning off the Status Bar will also make a small difference:

If your workbook contains events you should also disable events at the start of your procedures (to speed up code and to prevent endless loops!):

Last, your VBA code can be slowed down when Excel tries to re-calculate page breaks (Note: not all procedures will be impacted).  To turn off DisplayPageBreaks use this line of code:

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

 

Learn More!


<<Return to VBA Examples