This tutorial explains Excel Application Calculation Property and difference among xlCalculationAutomatic, xlCalculationManual, xlCalculationsemiautomatic
Excel VBA Application Calculation Property
In Excel worksheet, there are three options under Workbook Calculation.
The options in Workbook Calculation uses Calculation Property in Excel VBA, which means if you execute the corresponding VBA code, the Excel options change accordingly.
|Automatic||Default option. Recalculation occurs when- Edit a Cell- Open workbook
Add / Delete / Hide / Unhide row
Change Calculation Mode from Manual to Automatic / rerun xlCalculationAutomatic in VBA
Modify Named Range
Types of Cells that will recalculate
Cells affected directly by the above actions
Cells indirectly affected by the above actions such as formula referencing to the affected Cells
Cells that contain volatile Functions recalculate every time when any of the above action is performed
|Application.Calculation = xlCalculationAutomatic|
|Automatic except for data tables||See the above description for Automatic. For “data tables”, it refers to the Function under
Data > What-If Analysis > Data Table
|Application.Calculation = xlCalculationsemiautomatic|
|Manual||Do not auto calculate until clicking on the cells that contain a formula- Able to force recalculate of workbook by pressing CTRL+ALT+F9- SHIFT+F9 to recalculate active worksheet||Application.Calculation = xlCalculationManual|
Performance issue with Application Calculation Property = Automatic (xlCalculationAutomatic)
It is a common practice to temporarily turn Calculation Property from Automatic (xlCalculationAutomatic) to Manual (xlCalculationManual) in VBA to improve performance.
For example, A1 = B1+B2+B3, when your Sub Procedure changes B1 value, A1 recalculates. When Sub Procedures changes B2, A1 recalculates again, so A1 recalculates 3 times for the Sub Procedure. It is a waste of resources to recalculate A1 for three times because we don’t need to know the updated A1 value for each change in B1, B2, B3, we just want to final value of A1.
Improve performance of recalculation
To be more efficient, we can turn Calculation Property from Automatic (xlCalculationAutomatic) to Manual (xlCalculationManual) at the beginning of Sub Procedure, and turn back to Automatic in the end. You will notice a significant difference in the run time if you deal with mass data.
Your code should be written like this:
Public Sub recal() Application.Calculation = xlCalculationManual 'Your code Application.Calculation = xlCalculationAutomatic End Sub
When you turn xlCalculationAutomatic on again, all formula in the workbook are recalculated, this will make sure that everything in your Sub are recalculated.
Note carefully that all other currently opened documents will change the mode at the same time, that means all opened workbook will recalculate.
However, if your Sub Procedure is based on recalculation, you will not be able to use this trick.
When it takes a lot of time to open a workbook
When you are on Automatic mode, your workbook auto calculates when open and it may take a lot of time. You can turn the Automatic mode to Manual by inserting VBA code in Workbook_Open Event.
Private Sub Workbook_Open() Application.Calculation = xlManual Application.CalculateBeforeSave = False End Sub
When you close the workbook, you may want to automatically switch back to Automatic Mode using Workbook_BeforeClose Event. The below code will recalculate all values before save so it may save you some time opening the workbook but you can’t save the time when closing the workbook, unless you really don’t want any recalculation.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.Calculation = xlAutomatic Application.CalculateBeforeSave = True End Sub
If you want to selectively recalculate formula in specific worksheet, consider using Worksheet_Change Event in the target worksheet. The performance could be slow if you got a lot of formula to recalculate, but I think it is the best possible solution for recalculation of selective sheets. Note that ActiveSheet.Calculate is same as pressing F9 to recalculate.
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Calculate End Sub