Fix Excel Workbook Taking Over 20 Minutes to Close
Question details
The user needs to resolve an issue where a complex, macro-enabled Excel workbook takes an excessively long time to exit.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Closing a macro-enabled workbook containing more than 20 worksheets and complex dependencies.
- Observed behavior
- The workbook becomes unresponsive and takes over 20 minutes to close, even when no changes are saved, whereas other workbooks close normally.
Before applying VBA macro solutions, verify that you have saved a backup copy of your Excel workbook to prevent any accidental data loss or macro errors.
Disable Automatic Calculation on Close Using VBA
Use a VBA macro to automatically switch Excel's calculation mode to manual just before the workbook closes, bypassing the calculation freeze.
Excel sometimes recalculates complex formulas and volatile functions during the closing process. As workbooks grow in complexity with dozens of sheets, this can cause massive delays. By forcing Excel into manual calculation mode right before the workbook closes, you can immediately reduce the exit time.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left side, locate your active workbook and double-click on 'ThisWorkbook'.
Paste the following code into the code window: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.Calculation = xlCalculationManual End Sub
Save your file as a Macro-Enabled Workbook (.xlsm) and close the VBA editor. Try closing your workbook to see if the delay is eliminated.

Manually Switch Calculation Mode Before Exiting
Manually turn off automatic calculations through the Excel ribbon before you exit to prevent the closing delay.
Manage Complex Macro-Enabled Workbooks Smoothly with WPS
If your large Excel workbooks continue to freeze or close slowly, WPS Spreadsheet provides a highly optimized, lightweight environment for complex data. It fully supports VBA macros, complex dependencies, and ensures vastly improved loading and closing times.
- 1. Download WPS Office: Download and install WPS Office for free from the official website.
- 2. Open Your Complex Workbook: Launch WPS Spreadsheet and load your heavy, macro-enabled .xlsm workbook.
- 3. Manage VBA Macros: Navigate to the Developer tab to access the VBA editor, where your existing code will run seamlessly.
- 4. Experience Faster Processing: Save and close your files without the massive delays common in unoptimized spreadsheet software.

Frequently Asked Questions
Why does Excel calculate formulas when closing a file even without saving?
Excel may trigger recalculations on close if the workbook contains volatile functions (like TODAY, NOW, or OFFSET) or highly complex inter-sheet dependencies. Excel attempts to resolve the workbook's state before shutting down the application instance, which causes the massive delay.
Will setting calculation to manual break my formulas?
Setting the calculation to manual will not break your formulas, but it will prevent them from updating automatically when you change cell values. You will need to press F9 to calculate manually, or switch back to Automatic mode to see updated formula results.
How do I turn automatic calculation back on when opening the file?
You can add a macro to automate this. In the VBA editor, double-click 'ThisWorkbook' and add the following code: 'Private Sub Workbook_Open() Application.Calculation = xlCalculationAutomatic End Sub'. This ensures calculations run normally while you actively work.




