Forces a full calculation of the data in all open workbooks.
Syntax
Application.CalculateFull
Also, see below for similar methods
Calculate
calculates only new, changed and volatile formulas.
CalculateFull
calculates all formulas regardless. As a general rule, this will therefore be slower.
CalculateFullRebuild
calculates all formulas and rebuilds the entire calculation dependency tree. This will be the slowest of all.
Examples
To calculate | Follow this example |
---|---|
All open workbooks | Application.Calculate Calculate |
A specific worksheet | ActiveSheet.CalculateWorksheets(1).Calculate Sheets(“Sheet1”).Calculate |
A specified range | Worksheets(1).Rows(2).Calculate Sheets(“Sheet1”).Range(“a1:a10”).Calculate Range(“a1”).Calculate |
Additional notes
There is no VBA option to calculate only an entire workbook. If you need to calculate an entire workbook, the best option is to use the Calculate command:
Calculate
This will calculate all open workbooks. If you’re really concerned about speed, and want to calculate an entire workbook, you might be able to be more selective about which workbooks are open at one time.
Calculate Workbook – Methods That Don’t Work
There are a couple of methods that you might be tempted to use to force VBA to calculate just a workbook, however none of them will work properly.
This code will loop through each worksheet in the workbook and recalculate the sheets one at a time:
Sub Recalculate_Workbook() Dim ws As Worksheet For Each ws In Worksheets ws.Calculate Next ws End Sub
This code will work fine if all of your worksheets are “self-contained”, meaning none of your sheets contain calculations that refer to other sheets.
However, if your worksheets refer to other sheets, your calculations might not update properly. For example, if you calculate Sheet1 before Sheet2, but Sheet1’s formulas rely on calculations done in Sheet2 then your formulas will not contain the most up-to-date values.
You might also try selecting all sheets at once and calculating the activesheet. However, this will cause the same issue.
ThisWorkbook.Sheets.Select ActiveSheet.Calculate