New Post has been published on Access-Excel.Tips
New Post has been published on http://access-excel.tips/excel-vba-application-calculation-xlcalculationautomatic/
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
– Add / Delete / Hide / Unhide row
– Rename worksheet
– Reorder worksheet
– Change Calculation Mode from Manual to Automatic
– 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.
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