[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous|Disable-Screen-Updating) | Next

---

It’s a good practice to set Excel calculations to manual at the beginning of a procedure and restore calculations at the end of the macro.

1. Description

Excel uses automatic calculations by default, and only a few users (including power users) turn it off.

Excel can slow down when working with large workbooks that make several and/or complex calculations. Whenever a cell value is updated, Excel goes through a process to recalculate the whole workbook. When working directly within Excel, you may want this to happen 99.9% of the time (except if you work with an extremely large workbook).

When using VBA to change cells, especially in an iterative manner (like a for-loop), Excel can slow way down. This happens because every change triggers another complex calculation in the background before the next iteration is executed.

Setting the calculation property of the Application object to "xlCalculationManual" prevents calculations while executing or running a macro, so you can wait until a set of actions have been completed and instruct calculations to update at the end once rather than after every update.

2. How to use

It is a property of the Application object. Use:

• Application.Calculation = xlCalculationManual at the beginning of your code
• Application.Calculation = xlCalculationAutomatic before the ending of your code

It is better to store the pre-program property value to change the calculation style back to the one the user initially had. This would be part of best practices for UX (user experience) since the user will never even know the property was changed. Generally, setting it back to xlCalculationAutomatic after your program finishes (or aborts from an error) is a safe bet.

Example:

Sub Stop_Calculations ()
    Application.Calculation = xlCalculationManual
        '… your code here...
    Application.Calculation = xlCalculationAutomatic
End Sub

2.1. Manual calculation

In VBA, it is possible to calculate the entire workbook (or all open workbooks) or be more selective in what is manually calculated.

2.1.1. Calculate the whole Workbook (or WorkBooks)

When Automatic calculations are disabled, it is possible to use the Calculate command to force Excel to recalculate (all open Workbooks):

Calculate

or

Application.Calculate

2.1.2. Calculate an individual Workbook

It is not possible to calculate a single entire workbook via VBA (unless it is the only one opened). The Workbook object does not have a .Calculate method. You will need to either calculate all open workbooks or calculate all the sheets on a specific workbook.

2.1.3. Calculate one Sheet

.Calculate is a VBA method that applies to multiple objects, including the Sheet (and Worksheet) objects. We can tell Excel to recalculate only an individual worksheet:

Worksheets("sheet1").Calculate

2.1.4. Calculate one Range

We can get more granular with calculations: instead of an entire sheet, we can calculate a range. Here are some examples:

• Rows:     Rows(1).Calculate
  

• Columns:  Columns("A").Calculate or Columns(1).Calculate
  

• Cells:    Cells("A1").Calculate or Cells(1,1).Calculate
  

• Ranges:   Range("A1:D5").Calculate
  

This is a good way to hone in on a few formulas to reduce recalculation times.

2.1.5. Sequence-Sensitive Connections

When using manual calculations, knowing how your formulas are connected is important.

Whenever a cell value is changed, Excel must follow the “calculation tree” to recalculate all dependent cells. If Formula A flows into Formula B, calculate Formula A first. Otherwise, Formula B will not have the most updated data for its calculation. Calculating them out of order will cause Formula B to produce erroneous output. This is precisely why Excel defaults to automatic calculations.

In very complex workbooks, this sequence sensitivity is why many programmers calculate the entire workbook. It still allows for changes to occur without calculation, but when calculations are made, it is assumed that all calculations are updated simultaneously. Let Excel handle the updates for the entire workbook if you’re unsure which formulas are connected to which data.

3. Performance Test

4. Results

5. Conclusions

I recommend disabling Automatic calculations at the beginning of your procedure and re-enabling Automatic Calculations at the end.

Points to consider:

• In case of calculations are set to manual, it is better to make sure the workbook is calculated before saving: Application.CalculateBeforeSave = True
• Changing this property at the application level will impact all open workbooks unless you’ve initialized a second instance of Excel (which is uncommon).

---

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous|Disable-Screen-Updating) | Next