[Excel VBA] Speed Up Macros by Switching Calculation to Manual (Application.Calculation)

Along with Application.ScreenUpdating = False, temporarily switching the calculation method to manual is one of the two most important techniques for speeding up VBA macros.

If you use VBA to write values into a sheet containing many formulas one after another, Excel recalculates every time a value changes, causing the process to become extremely slow.

In this article, I will explain this essential optimization technique: using the Application.Calculation property to switch calculation to “Manual” only while the macro is running, and then returning it to its original state when finished.

Why Does Switching to Manual Make It Faster?

When Excel’s calculation method is set to “Automatic,” if the value of any cell changes, all formulas referencing that cell are recalculated. If you run a loop in VBA to input values into thousands of cells, this recalculation occurs thousands of times, resulting in a huge wait time.

By switching the calculation method to “Manual” before running the macro, you skip all these unnecessary recalculations. This allows VBA to focus solely on writing the values, drastically improving processing speed.

Complete VBA Code (Recommended Pattern)

Just like with ScreenUpdating, it is an ironclad rule to always restore the Calculation property to its original state after the macro finishes. I recommend the following pattern, which includes error handling to ensure settings are restored even if an error occurs.

Sub ProcessWithManualCalculation()

    ' Declare variables
    Dim originalCalcMode As XlCalculation

    '--- 1. Preparation ---
    ' Store the original calculation mode
    originalCalcMode = Application.Calculation
    
    ' Turning off screen updating and events speeds it up further
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' Set calculation to manual
    Application.Calculation = xlCalculationManual
    
    ' Setup to ensure settings are restored even if an error occurs
    On Error GoTo ErrorHandler

    '--- 2. Main Process ---
    ' (Example: Entering data into cells referenced by formulas)
    Dim i As Long
    For i = 1 To 20000
        ThisWorkbook.Worksheets("DataInput").Cells(i, 1).Value = Rnd() * 100
    Next i
    
    ' (Optional) Force a recalculation here if necessary
    ' Application.Calculate

'--- 3. Always restore settings after processing ---
ExitRoutine:
    Application.Calculation = originalCalcMode
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    MsgBox "Process completed."
    Exit Sub
    
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
    Resume ExitRoutine ' Ensure settings are restored on error
    
End Sub

Key Points of the Code

1. Store the Original Calculation Mode

originalCalcMode = Application.Calculation

To avoid permanently changing the user’s Excel settings, first store the current calculation method in a variable called originalCalcMode.

2. Set Calculation to Manual

Application.Calculation = xlCalculationManual

Set the Application.Calculation property to the constant xlCalculationManual. This ensures no recalculation occurs when the macro changes values.

3. Manual Recalculation (Application.Calculate)

‘ Application.Calculate

While calculation is manual, all calculation results remain outdated. If you need the latest results immediately after the main process finishes, execute the Application.Calculate method. This is the same operation as pressing the F9 key to recalculate the entire workbook.

4. Restore Calculation Mode

Application.Calculation = originalCalcMode

Using the On Error GoTo error handling structure ensures that this line is executed at the end of the macro, whether the process completes successfully or an error occurs. By restoring the original setting (originalCalcMode) stored in step 1, you allow the user to resume work in the same state as before the macro ran.

Main Calculation Mode Settings

VBA Constant	Calculation Mode	Description
xlCalculationAutomatic	Automatic (Default)	Automatically recalculates when any data referenced by a formula changes.
xlCalculationManual	Manual	Does not recalculate unless you press F9 or trigger it via VBA.
xlCalculationSemiautomatic	Automatic Except Tables	Automatically recalculates all formulas except for Data Tables.

Summary

Controlling ScreenUpdating and Calculation is fundamental to speeding up VBA macros and produces significant results.

1. Store the current Application.Calculation state in a variable before starting.
2. Set Application.Calculation = xlCalculationManual.
3. Execute the main process.
4. (Optional) Recalculate with Application.Calculate if needed.
5. Restore the calculation method to its original state using the stored variable at the end.

If you are performing processes that involve large amounts of data entry or formatting that could trigger formula recalculations, be sure to apply this pattern.