How to Disable Automatic Calculation in Excel VBA to Speed Up Your Macros

One common reason a macro runs slowly is that the Excel sheet contains a large number of formulas. In such cases, you can significantly improve performance by temporarily disabling automatic calculation while your macro runs.

In this article, we’ll show you how to do it.

For instructions on how to switch between manual and automatic calculation without using macros, see our separate article:Switch between manual and automatic recalculation methods in Excel (Excel).

Code to Stop and Resume Automatic Calculation

Application.Calculation = xlCalculationManual' Stop automatic calculation and change to manual calculation
~Processing~.
Calculation = xlCalculationAutomatic'Revert to automatic calculation

"Application.Calculation = xlCalculationManual" changesthe calculation mode from automatic to manual—this is the same as selecting Manual from the Formulas tab in Excel’s toolbar (as shown in the image below).

Application.Calculation = xlCalculationAutomatic" changes the calculation mode from manual back to automatic. In Excel, this is the same as selecting Automatic for the calculation method in the Formulas tab of the toolbar, as shown in the image below.

Examples of Usage

Example of Time-Consuming Code

Measure macro processing timeWe will measure macro processing time using the same Excel sheet shown on this article.

Fill column A with text values (up to 10,000 rows), column B with numbers (to be updated later by the macro), and columns C, D, and E with the number 1000 (up to 10,000 rows each).

Column D should contain a formula that divides the value in column B by the value in column C.

Next, write code to measure the processing time.

This is the code that we used in this article:Measure macro processing timeThis code was also used in the

Sub Execution()

    Dim starttime As Double: starttime = Timer ' Start of time measurement
    
    Call Process1    

    Dim myspeed As Double: myspeed = Timer - starttime 'Time measurement lapse 1
    myspeed = WorksheetFunction.Round(myspeed, 3)

    Debug.Print "The time for process 1 is " & myspeed & "seconds"

    starttime = Timer 'Reset time
    
    Call Process2

    myspeed = Timer - starttime 'Time measurement progress 2
    myspeed = WorksheetFunction.Round(myspeed, 3)
    Debug.Print "The time for process 2 is " & myspeed & "seconds"

End Sub

Sub Process1()
    Dim s As Long: s = 3
    Dim i As Long: i = 1
    Do Until Cells(i, 1) = ""
        Cells(i, 2) = s
        s = s + 2
        i = i + 1
    Loop
End Sub
Sub Process2()
    Dim s As Long: s = 3
    Dim i As Long
    i = 1
    Do Until i = 100
        Cells(i, 2) = s
        s = s + 2
        i = i + 1
    Loop
End Sub

Process 1: Simply writes numbers into column B.

Process 2: Performs the same operation as Process 1, but only for 100 rows.

Now, let's run the test.

The Immediate Window displayed the result like this, showing that Process 1 handles nearly 100 times the amount of data as Process 2.

*For more information about Immediate Window see:How to display the Immediate Windowのブログを参照してください。

処理1は処理2の100倍近いボリュームになっているのがわかります。

Adding Automatic Calculation Stop Code

Sub Process1()
    Dim s As Long: s = 3
    Dim i As Long: i = 1
    Application.Calculation = xlCalculationManual ' Automatic calculation stop
        Do Until Cells(i, 1) = ""
            Cells(i, 2) = s
            s = s + 2
            i = i + 1
        Loop
    Application.Calculation = xlCalculationAutomatic ' Resume automatic calculation
End Sub

As above, by stopping automatic calculation during Process1, we can greatly reduce execution time.

After adding this, running Process1 again reduced the time to about 1/6 of the original.

Recalculate only once during manual mode

Recalculate the entire workbook

Application.Calculation = xlCalculationManual' Stop automatic calculation and change to manual calculation

~Processing~.

Application.Calculate 'Run manual calculations once with automatic calculations stopped

~Processing~.

Calculation = xlCalculationAutomatic'Revert to automatic calculation

Application.Calculate" recalculates the entire workbook once—equivalent to pressing Calculate Now in the Formulas tab. as shown in the image below.

Recalculate Only Part of the Workbook

If slowdowns are caused by formulas in other sheets or unrelated cells, you can limit recalculation to a specific sheet or range:

Application.Calculation = xlCalculationManual' Stop automatic calculation and change to manual calculation

~Processing~.

Worksheets("Sheet1").Calculate'Recalculate only Sheet1


Range("A1:C3").Calculate'Perform recalculation of the range from "A1" cell to "C3" cell

~Processing~.

Calculation = xlCalculationAutomatic'Revert to automatic calculation

Please utilize these methods to help speed up macros that are weighed down by Excel functions.