VBA & Macro Problems

Excel VBA: Prompt to Recalculate Before Closing a Workbook

Aamir Naveed AkramAamir Naveed Akram Nov 12, 2026 869 views

Question details

The user needs Excel to prompt for a formula recalculation before a workbook closes.

Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A large workbook uses manual calculation to improve performance, but the user wants an option to recalculate before closing the file.
Observed behavior
The workbook can close without prompting the user to recalculate its formulas.
Before you start

Confirm that the workbook uses manual calculation and that macros are enabled. Save the file as an Excel Macro-Enabled Workbook (*.xlsm) so the Workbook_BeforeClose event can run.

Solution 1Recommended

Add a Workbook_BeforeClose prompt in Excel VBA

Use this solution when a manual-calculation workbook should ask whether to recalculate each time it closes.

The Workbook_BeforeClose event must be placed in the ThisWorkbook module, not in a standard VBA module. Choosing Yes runs Application.Calculate before the workbook closes; choosing No allows the close operation to continue without that calculation command.

1
Open the VBA editor

In Excel, open the macro-enabled workbook, press Alt+F11, and locate the workbook under Microsoft Excel Objects in the Project Explorer.

2
Select ThisWorkbook

Double-click ThisWorkbook, then use the left Object drop-down to select Workbook and the right Procedure drop-down to select BeforeClose.

3
Insert the event code

Enter this code in the ThisWorkbook code window: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim answer As VbMsgBoxResult answer = MsgBox("Do you want to recalculate before closing?", vbQuestion + vbYesNo, "Recalculate Before Closing") If answer = vbYes Then Application.Calculate End Sub

4
Save and test the macro

Close the Visual Basic Editor, choose File > Save As, select Excel Macro-Enabled Workbook (*.xlsm), and close the workbook to test the prompt.

Calculation behavior: Excel may still calculate for other reasons, including dependency changes or manual calculation commands. Autosave itself is not generally a separate calculation trigger.
Free Microsoft Office alternative

Move Everyday Workbook Editing to WPS Office

WPS Office is a free, lightweight Microsoft Office alternative with a familiar interface and broad format compatibility, making it easy to open and migrate everyday Excel workbooks. Excel-specific VBA events such as Workbook_BeforeClose should be tested separately because macro behavior can vary between applications.

  1. 1. Download WPS Office: Select Download WPS Office Free and install the desktop application for your device.
  2. 2. Open the workbook: Launch WPS Spreadsheet, select Open from the Home screen, and choose the Excel workbook you want to review.
  3. 3. Check the workbook content: Review formulas, formatting, and worksheet data in WPS Spreadsheet; test any VBA-dependent behavior in the application that will run the macro.
Free and lightweight for everyday spreadsheet editingFamiliar workbook, worksheet, and formula interface for seamless migrationCompatible with common Microsoft Excel formats including .xlsx, .xls, and .xlsmUseful for reviewing large workbooks while preserving familiar Office-style workflows
QA img-9

Frequently Asked Questions

Where should the recalculation VBA code be placed?

Place it in the ThisWorkbook module in the Visual Basic Editor. Do not put it in a standard module, because the Workbook_BeforeClose event is tied to the workbook object.

What file type is required for the Workbook_BeforeClose macro?

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). A standard .xlsx file cannot store this VBA event procedure.

What happens if I select No in the recalculation prompt?

The code does not call Application.Calculate, so the workbook continues closing without that requested recalculation. Excel can still calculate for other reasons when its calculation rules require it.

Does autosave automatically recalculate the workbook?

No. Autosave is not generally a separate calculation trigger. Calculation can occur because of dependency changes, calculation settings, or an explicit calculation command such as Application.Calculate.