How to Forces a Full Calculation All Open Workbooks in VBA (“F9”)

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.Calculate Worksheets(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