Excel VBA Application Calculation Property

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.

Excel Option	Description	VBA
Automatic	Default option. Recalculation occurs when- Edit a Cell- Open workbook – Add / Delete / Hide / Unhide row – Rename worksheet – Reorder worksheet – 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

Outbound References

https://support.microsoft.com/kb/214395?wa=wsignin1.0

https://msdn.microsoft.com/en-us/library/ff700515%28v=office.14%29.aspx?f=255&MSPPError=-2147217396