VBA Calculate – Now, Workbook, Worksheet, or Range

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):

Calculate

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:

ActiveSheet.Calculate

This code will recalculate Sheet1:

Sheets("Sheet1").Calculate

Calculate Range

If you require a more narrow calculation, you can tell VBA to calculate only a range of cells:

Sheets("Sheet1").Range("a1:a10").Calculate

Calculate Individual Formula

This code will calculate only an individual cell formula:

Range("a1").Calculate

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:

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:

ThisWorkbook.Sheets.Select
ActiveSheet.Calculate

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!


Learn More!