30 Advanced Excel Tips for 2026

Excel Champs / VBA / VBA Calculate (Cell, Range, Row, & Workbook)
By Puneet Gogia (Microsoft MVP)

VBA Calculate (Cell, Range, Row, & Workbook)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Application.Calculation = xlCalculationManual turns off automatic recalculation, and Application.Calculation = xlCalculationAutomatic turns it back on. Switch to manual at the start of a macro that writes to many cells, and back at the end, so Excel doesn’t recalculate after every change.

To recalculate while in manual mode, pick the scope. Application.Calculate recalculates all open workbooks (F9), Worksheets("Data").Calculate one sheet (Shift + F9), and Range("A1:D100").Calculate or Rows(5).Calculate only those cells; formulas outside the range that depend on them aren’t updated. Application.CalculateFull recalculates every formula, changed or not (Ctrl + Alt + F9).

The setting applies to the whole of Excel, not one workbook or sheet. Excel takes the mode from the first workbook opened in a session, so a file saved in manual can leave every file you open after it in manual too.

If the macro stops with an error between the two lines, Excel stays in manual and formulas stop updating without any warning. Restore the setting in an error handler:

Sub UpdateReport()
    On Error GoTo CleanUp
    Application.Calculation = xlCalculationManual
    'your code here
CleanUp:
    Application.Calculation = xlCalculationAutomatic
End Sub

By default, in Excel, whenever you change a cell value, Excel recalculates all the cells that have a calculation dependency on that cell. But when you are using VBA, you have an option to change it to manual, just like we do in Excel.

calculate-cell-value-using-vba

Using VBA Calculate Method

You can change the calculation to the manual before you start a code, just like the following.

Application.Calculation = xlManual

When you run this code, it changes the calculation to manual.

Keep in mind that the calculation mode is an application-level setting. In the Excel desktop app, it applies to all the open workbooks, not just the one that has your code. xlManual and xlAutomatic work fine, and you can also use their full names, xlCalculationManual and xlCalculationAutomatic, which hold the same values.

using-vba-calculate-method

And at the end of the code, you can use the following line of code to switch it back to automatic.

Application.Calculation = xlAutomatic
switch-to-automatic-using-vba

You can use both lines in a macro in the following way.

calculation-method-vba-code
Sub myMacro()
    Application.Calculation = xlManual   
        'your code goes here   
    Application.Calculation = xlAutomatic
End Sub

Calculate Now (All the Open Workbooks)

If you simply want to recalculate all the open workbooks, you can use the “Calculate” method just like below.

Calculate

Use Calculate Method for a Sheet

Using the following way, you can recalculate all the calculations for the active sheet or for a specific sheet.

ActiveSheet.Calculate
Sheets("Sheet1").Calculate

The first line of code recalculates the active sheet, and the second line does it for “Sheet1”, but you can change the sheet name if you want.

Calculate for a Range or a Single Cell

In the same way, you can recalculate all the calculations for a particular range or a single cell, just like the following.

Sheets("Sheet1").Range("A1:A10").Calculate
Sheets("Sheet1").Range("A1").Calculate

You can also point to whole columns of the used range. The following line recalculates the formulas in columns A to C within the used range of “Sheet1”.

Sub CalculateColumns()
Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate
End Sub

Force a Full Calculation

The Calculate method only recalculates the formulas that Excel has marked as changed, along with their dependents. Sometimes, a formula doesn’t update even after that. In that case, you can force Excel to calculate every formula in all the open workbooks with the CalculateFull method.

Sub FullCalculation()
Application.CalculateFull
End Sub

And if that still doesn’t fix it, use CalculateFullRebuild. It rebuilds the dependencies first and then calculates all the formulas in all the open workbooks, which is similar to re-entering every formula. It’s the slowest of all, so use it only when you need it.

Sub FullCalculationRebuild()
Application.CalculateFullRebuild
End Sub

VBA Calculate Methods and Their Keyboard Shortcuts

Each of these methods does the same job as a keyboard shortcut in Excel on Windows. Here’s a quick comparison to help you pick the right one:

  • Application.Calculate: same as F9. Recalculates the changed formulas and their dependents in all the open workbooks.
  • ActiveSheet.Calculate: same as Shift + F9. Recalculates the changed formulas and their dependents in the active sheet only.
  • Application.CalculateFull: same as Ctrl + Alt + F9. Recalculates all the formulas in all the open workbooks, whether they changed or not.
  • Application.CalculateFullRebuild: same as Ctrl + Shift + Alt + F9. Checks the dependent formulas and then recalculates all the formulas in all the open workbooks.
  • Range.Calculate: has no shortcut. Recalculates only the range you specify.

Check the Current Calculation Mode

Before you change the calculation, you might want to know what it’s set to right now. The Calculation property also returns the current mode, and the following code shows it in a message box.

Sub ShowCalculationMode()
Select Case Application.Calculation
Case xlCalculationAutomatic
MsgBox "Calculation is Automatic"
Case xlCalculationManual
MsgBox "Calculation is Manual"
Case xlCalculationSemiautomatic
MsgBox "Calculation is Automatic Except for Data Tables"
End Select
End Sub

There’s a third mode here, xlCalculationSemiautomatic. It’s the same as the “Automatic Except for Data Tables” option in Excel (Formulas tab > Calculation group > Calculation Options). Excel recalculates everything automatically except data tables, and you can set it from VBA just like the other two.

Sub SemiAutomaticCalculation()
Application.Calculation = xlCalculationSemiautomatic
End Sub

Restore the Calculation Mode Even If the Code Fails

There’s one problem with the macro above. If your code stops with an error halfway through, the line that switches the calculation back never runs, and Excel stays on manual. After that, your formulas stop updating, and you might not even notice it.

The better way is to save the current mode in a variable first and use error handling to put it back, whatever happens. I also like to turn off screen updating at the same time, as it makes long macros run even faster.

Sub myMacroSafe()
Dim calcMode As XlCalculation
calcMode = Application.Calculation
On Error GoTo CleanUp
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' your code goes here
CleanUp:
Application.Calculation = calcMode
Application.ScreenUpdating = True
If Err.Number <> 0 Then MsgBox "Error: " & Err.Description
End Sub

In this code, the CleanUp part runs at the end in both cases: when the code finishes normally and when an error stops it. So the calculation always goes back to the mode it had before your macro started.

Frequently Asked Questions

Why are my formulas not updating after running a macro?

Most likely, the macro set the calculation to manual and never switched it back, often because it stopped with an error. Go to Formulas tab > Calculation Options and select Automatic, or run Application.Calculation = xlCalculationAutomatic. Then use the restore method above in your macro.

Does Application.Calculation change other open workbooks too?

Yes. In the Excel desktop app, the calculation mode applies to all the open workbooks, so setting it to manual in one workbook’s macro also stops automatic calculation in every other open workbook.

What is the difference between Calculate and CalculateFull?

Calculate recalculates only the formulas that changed since the last calculation and the formulas that depend on them. CalculateFull recalculates every formula in all the open workbooks, whether it changed or not, so it’s slower but more thorough.

Can I recalculate a range while calculation is set to manual?

Yes. Range.Calculate and Worksheet.Calculate work in manual mode as well, and the mode stays manual afterward. That’s useful when you only need fresh results in one part of a large workbook.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel