VBA Calculate – Now, Workbook, Worksheet, or Range

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on September 13, 2021

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.

Calculate Now

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:


Calculate Range

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:


Calculate Workbook

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
    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.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples