Calculation Issues

How to Stop Excel from Recalculating After Every VBA Code Change

Bushra Parveen Oct 1, 2026 870 views

Question details

Excel constantly recalculates and appears to compile code after every VBA edit, leading to slow performance or unresponsiveness.

How to Stop Excel from Recalculating After Every VBA Code Change

Product: Microsoft Excel
Device & OS: not provided
Scenario: Writing or editing VBA code that interacts with a workbook containing User-Defined Functions (UDFs).
Observed behavior: Excel recalculates user-defined functions immediately after changes are made in the VBA editor, even when background compilation is turned off.

Before you start

Before modifying your calculation settings, temporarily save your workbook progress and VBA code to prevent any accidental data loss.

Solution 1Recommended

Switch Worksheet Calculation to Manual Mode

Changing Excel's calculation mode to Manual stops User-Defined Functions from triggering automatically, instantly resolving the lag while editing VBA.

This freezing issue is related to how Excel handles worksheet recalculations, rather than the VBA background-compile settings. When Excel needs a result from a User-Defined Function (UDF), it forces a recalculation.

By setting the calculation mode to Manual, you regain complete control over when the workbook processes these functions.

Navigate to the Formulas Tab

Open your Excel workbook and click on the 'Formulas' tab in the top ribbon.

Open Calculation Options

Locate the 'Calculation' group on the right side of the ribbon and click on the 'Calculation Options' dropdown button.

Select Manual Mode

Choose 'Manual' from the dropdown list. Excel will now only recalculate when you manually instruct it to do so.

Switch Worksheet Calculation to Manual Mode

Immediate Performance Boost: Your Visual Basic Editor should no longer freeze or lag after every code edit, allowing for a much smoother coding experience.

Solution 2

Disable Automatic Calculation via the Immediate Window

If you prefer not to leave the VBA Editor interface, you can quickly toggle Excel's calculation settings using a direct VBA command.

Seamlessly Manage Macros and Calculations in WPS Spreadsheet

WPS Spreadsheet provides a robust environment for managing complex data, User-Defined Functions, and VBA macros without frustrating freezes. Switching calculation modes is quick and intuitive, keeping your workflow highly efficient.

1. Open your macro workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the complex data or User-Defined Functions.
2. Access the Formulas tab: Navigate to the Formulas tab located in the main top ribbon.
3. Change Calculate Options: Click on 'Calculate Options' and select 'Manual' to prevent background recalculations while writing your macros.

Fully compatible with Microsoft Excel macro-enabled files (.xlsm)Easily toggle between automatic and manual calculationsLightweight architecture prevents lagging during heavy VBA codingFree to use with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Does disabling 'Background Compile' in VBA stop this recalculation?

No. The background-compile setting in the VBA Editor does not control worksheet calculations. If you have User-Defined Functions, Excel will still attempt to recalculate them unless the worksheet calculation mode itself is set to Manual.

How do I force a recalculation when Manual mode is turned on?

You can manually force a recalculation at any time by pressing the 'F9' key to calculate the entire workbook, or 'Shift + F9' to calculate only the currently active worksheet.

Why do User-Defined Functions cause my VBA editor to run slow?

Excel automatically requests the results of User-Defined Functions whenever it perceives a data or code change that might affect the output. In Automatic calculation mode, this triggers continuously during VBA edits, severely slowing down the Visual Basic Editor.

Excel & Data Problems