Excel VBA: Prompt to Recalculate Before Closing a Workbook
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.
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.
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.
In Excel, open the macro-enabled workbook, press Alt+F11, and locate the workbook under Microsoft Excel Objects in the Project Explorer.
Double-click ThisWorkbook, then use the left Object drop-down to select Workbook and the right Procedure drop-down to select BeforeClose.
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
Close the Visual Basic Editor, choose File > Save As, select Excel Macro-Enabled Workbook (*.xlsm), and close the workbook to test the prompt.
Verify Excel calculation settings before using the prompt
Use this check when the prompt appears but formulas do not update as expected.
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. Download WPS Office: Select Download WPS Office Free and install the desktop application for your device.
- 2. Open the workbook: Launch WPS Spreadsheet, select Open from the Home screen, and choose the Excel workbook you want to review.
- 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.

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.




