VBA Calculate – Now, Workbook, Worksheet, or Range
In this Article
This tutorial will teach you all of the different Calculate options in VBA.
By default Excel calculates all open workbooks every time a workbook change is made. It does this by following a calculation tree where if cell A1 is changed, it updates all cells that rely on cell A1 and so on. However, this can cause your VBA code to run extremely slowly, as every time a cell changes, Excel must re-calculate.
To increase your VBA speed, you will often want to disable automatic calculations at the beginning of your procedures:
Application.Calculation = xlManual
and re-enable it at the end:
Application.Calculation = xlAutomatic
However, what if you want to calculate all (or part) of your workbooks within your procedure? The rest of this tutorial will teach you what to do.
You can use the Calculate command to re-calculate everything (in all open workbooks):
This is usually the best method to use. However, you can also perform more narrow calculations for improved speed.
Calculate Sheet Only
You can also tell VBA to calculate only a specific sheet.
This code will recalculate the active sheet:
This code will recalculate Sheet1:
If you require a more narrow calculation, you can tell VBA to calculate only a range of cells:
Calculate Individual Formula
This code will calculate only an individual cell formula:
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:
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.