Excel Macro (VBA) to stop automatic calculations

One of the factors that can make a macro very heavy is a large number of formulas in an Excel sheet. In such cases, it may be possible to speed up the process at once by stopping the automatic calculations.

First, we will show you how to do this.

How to switch between manual and automatic calculation methods without using macrosSwitch between manual and automatic recalculation methods in Excel (Excel).

Automatic calculation stop/resume code

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

Application.Calculation = xlCalculationManual" changes the automatic calculation to a manual calculation. In the Excel screen, it does the same thing as switching to manual for the calculation method in the formulas tab of the toolbar, as shown in the image below.

Application.Calculation = xlCalculationAutomatic" changes a manual calculation to an automatic calculation. In the Excel screen, it does the same thing as switching to manual for the calculation method in the formulas tab of the toolbar, as shown in the image below.

examples showing the use (of a word)

Example of time-consuming code

Measure macro processing timeI will also use the excel sheet used in the

Fill column A with the appropriate text up to 10,000 lines, fill column B with the appropriate number (to be rewritten later in the macro), fill column C with the number 1,000 up to 10,000 lines, fill column D with the number 1,000 up to 10,000 lines, fill column E with the number 1,000 up to 10,000 lines, and so on.

Column D should then contain the formula for columns B/C.

Next, write code that can measure time.

this way (direction close to the speaker or towards the speaker)Measure macro processing timeThis code was also used in the

Sub Execution()

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

    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 Processing 2

    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 Processing 1()
    Dim s As Long: s = 3
    Dim i As Long: i = 1
    Do Until Cells(i, 1) = ""
        Cells(i, 2) = s
        s = s + 2
        Do Until Cells(i, 2) = s = s + 2
    End Sub Loop
End Sub

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

Process 1 is a simple matter of placing a number in column B.

Process 2 is to process only 100 rows of Process 1.

Let's get right to it!

Then the Immediate window displays the result like this.

*For more information about Immediate WindowHow to display the Immediate WindowSee the blog at

You can see that Process 1 is almost 100 times the volume of Process 2.

Interleave automatic calculation stop code

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

I inserted the automatic calculation stop code in process 1. Now let's run it again.

The time for Process 1 was then reduced to about 1/6.

Recalculate only once during manual calculation

Recalculate the entire book

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" performs a recalculation only once after changing the calculation method to manual. In the Excel screen, this is the same as pressing the "Run Recalculate" button on the Formulas tab of the toolbar, as shown in the image below.

Recalculated only partially

If another sheet in the book or another formula in the same sheet that has nothing to do with the current process is causing the slowdown, the recalculation can be performed only in a limited range of cells, not in the entire book.

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.