When you work with very large workbooks that make complex calculations, Excel can slow down. If you’re using VBA to change cells, especially in an iterative manner (like a for-loop), Excel can slow way down. This happens because every change triggers another complex calculation in the background before the next iteration is executed.

To run your code faster, you can turn off automatic calculation and manually calculate sheets. It’s still via VBA, though, so I am using “manual” quite loosely here. Manually calculating sheets is just one of several ways to speed up your macros.

Alternatively, for interactive programs, you may want the user to double-check the inputs before performing any calculations at all. This could come in handy when using an API to load data. For example, the API loads the data, the user checks it within the workbook before calculations occur, then the input can be reverted or the calculation finalized.

Whatever your use case, let’s learn how to make manual calculations with VBA.

The First Step

First, you need to turn off automatic calculations. By default, Excel uses automatic calculations, and very few users turn it off (including power users). That makes sense, because imagine how tedious it would be to constantly force formulas to calculate manually?

Moreover, it’s part of coding best practices to set environmental variables explicitly at the beginning to ensure your processing environment does not invalidate any of your assumptions (this is another reason why strictly typing your variables is beneficial).

Here, your environmental variable is the Application.Calculation property. Usually it will be set to xlCalculationAutomatic. To enable manual calculations, you’ll need to change it to:

Application.Calculation = xlCalculationManual

Even if the user has already set the property to manual calculation by themselves, there is no harm in adding this one line to ensure your program functions properly.

Notice that this is an application-level property. It’ll be set for all open workbooks unless you’ve initialized a second instance of Excel (which is uncommon).

If you want, you can even store the pre-program property value so you can change the calculation style back to the one the user initially had. This would be part of best practices for UX (user experience), since the user will never even know the property was changed. Generally, though, setting it back to xlCalculationAutomatic after your program finishes (or aborts from an error) is a safe bet.

Choose What You'll Calculate

You can use VBA to calculate the entire workbook (or all open workbooks), or you can be more selective in what is manually calculated. In this section we look at all the ways you can trigger a manual calculation for different parts of your workbook.

Calculate The Whole Workbook (or Workbooks)

The easiest and usually most robust method is to calculate all open workbooks. All the formulas in the open workbook(s) will be calculated at the application level, whether they are contained within a single sheet, spread across sheets, or even spread across workbooks.

You can use either of these lines to force a calculation on the whole workbook:

Application.Calculate
Calculate

We used the Application object, so all open workbooks within the scope of the Excel application will calculate.

Generally, because the calculation happens once, unless you have very significant interaction between a large number of cells, the calculation will execute very fast. Of course, if you include this in an iterative piece of your code, Excel will calculate repetitively, possibly negating the benefit of manual calculation. For efficiency, try to calculate as infrequently as possible.

Calculate An Individual Workbook

If you have Intellisense enabled, you’ll see that the Workbook object does not have a .Calculate method. It is not possible to calculate a single entire workbook via VBA. You will need to either calculate all open workbooks or calculate all the sheets on a specific workbook. This will require iteration if there is more than one sheet, which is explained in the next section.

The alternative to this is to create a new Excel Application instance with VBA and open your workbook in that instance, but that’s outside the scope of this tutorial.

Calculate One Sheet

The Calculate method lets you calculate formulas on individual sheets. In fact, in the Excel interface, there’s an option for calculating just the current sheet (check under the Formulas ribbon). This can be replicated in VBA.

.Calculate is a VBA method that applies to multiple objects, including the Sheet (and Worksheet) objects.

To calculate just a single sheet, reference that sheet. Here we’ll use a sheet named API Content:

Sheets("API Content").Calculate

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

While it’s not possible to calculate an entire workbook without calculating all workbooks, it is possible to calculate a single sheet. Thus, a simple solution to calculating one workbook but not another is to cycle through all the sheets in a specific workbook.

For example, you might download data in the API Downloader workbook but store content in the Long Term Data workbook. If you want to calculate the entire API Downloader workbook only, you’ll have to do something along these lines with For Each loops or another iterative structure:

Sub CalculateAllSheets()
For Each s In Workbooks("API Downloader").Sheets
    s.Calculate
Next s
End Sub

For an API example, you might run the API subroutine to make a GET call to the API endpoint, populate a worksheet with the new data, then ask the user to verify the downloaded content. Once verification is complete, you might work in some programmatic integrity checks, like a sum to ensure the total isn’t negative or an average to ensure there are no significant outliers, and then let Excel calculate the sheet. If there’s an error, identified either by humans or by code, you can roll back the changes without affecting any of the other sheets in the workbook.

Calculate Any Range (Cells, Rows, Columns)

You can get more granular with your calculations, too. Instead of an entire sheet, you can calculate a range. Just set your range and use the .Calculate method. Let’s say you have a set of formulas in B1:B5. If you’ve enabled manual calculations (by setting Application.Calculation equal to xlCalculationManual) and you want to just calculate the first two cells after each iteration of a loop, you can do it this way:

Range("B1:B2").Calculate

You can force a calculation for any of the following ranges without calculating anything else:

  • Rows (Rows(1).Calculate)
  • Columns (Columns("A").Calculate or Columns(1).Calculate)
  • Cells (Cells("A1").Calculate or Cells(1,1).Calculate)
  • Ranges (Range("A1:D5").Calculate)

This is a good way to hone in on a few formulas to cut down recalculation times.

Beware of Sequence-Sensitive Connections

When using manual calculations, it is important to be aware of how your formulas are connected to each other. If Formula A flows into Formula B, make sure you calculate Formula A first. Otherwise, Formula B will not have the most updated data for its calculation. Calculating them out of order will cause Formula B to result in erroneous output. This is precisely why Excel defaults to automatic calculations.

In very complex workbooks, this sequence-sensitivity is the reason many programmers simply calculate the entire workbook. It still allows for changes to occur without calculation, but when calculations are made, it is assured that all calculations are updated simultaneously. Let Excel handle the updates for the entire workbook if you’re not sure which formulas are connected to which data.

The Final Step

Of course, once you are finished with your calculations, make sure to return the Application.Calculation property back to its original state (for most applications, that will be the xlCalculationAutomatic value). For less tech-savvy users, forgetting to do this might cause them to think you broke their Excel application and complain!

It’s also important to remember to set the calculation state back to automatic inside any error handling routines you write that could abort your code early. This is a common a mistake, and a common reason why people may think your macro messed up their worksheet.

If you have a reason to calculate formulas manually in Excel, whether it’s to speed up calculations or to inject human interaction, it really is a straightforward process. You can force manual calculations for a variety of targets, including all open workbooks or any-sized range object, even single cells.

It’s very important to be aware of how formulas rely on others, though, because overlooking the formula connections can cause unexpected output when manually calculating out of order. With automatic calculations, Excel tracks formula calculations to ensure all parts are up to date, but if you do it manually, you’ll need to take this into consideration yourself.

For more VBA tips like this one, I hope you’ll subscribe using the form below.