How to Stop an Excel Application.OnTime Macro When a Workbook Closes
Question details
The user needs a method to cancel an active Application.OnTime VBA schedule before closing the workbook so the macro does not continue to execute.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook is running a VBA procedure scheduled at intervals (e.g., every second) using Application.OnTime. The user wants to safely close the workbook without triggering the next scheduled execution.
- Observed behavior
- If the schedule is not canceled during the close event, Excel will either throw a runtime error or automatically reopen the closed workbook to run the pending macro.
Make sure you have saved a backup of your workbook and enabled the Developer tab to access the VBA editor before modifying your macro scripts.
Cancel the OnTime Schedule using the Workbook_BeforeClose Event
By storing the exact scheduled time in a public variable, you can reference it during the workbook closing process to definitively cancel the pending Application.OnTime procedure.
To stop an OnTime macro, Excel requires the exact time the macro was scheduled to run. You must store this time in a public variable when scheduling it. Then, you can call Application.OnTime again with the Schedule parameter set to False to cancel it.
Press Alt + F11 to open the VBA Editor. In a standard module (e.g., Module1), declare a public variable at the very top of the code window to store the time: Public dtmNext As Date.
In your existing macro that sets the timer, assign the future time to the variable and run the OnTime method. For example: dtmNext = Now + TimeValue("00:00:01") followed by Application.OnTime dtmNext, "Form_Timer".
In the Project Explorer pane on the left, double-click on 'ThisWorkbook'. From the top left dropdown, select 'Workbook', and from the right dropdown, select 'BeforeClose'.
Inside the Private Sub Workbook_BeforeClose event, add error handling and the cancellation command: On Error Resume Next, followed on the next line by Application.OnTime dtmNext, "Form_Timer", , False.
Manage and Run VBA Macros Easily with WPS Office
WPS Spreadsheet offers comprehensive support for VBA macros, allowing you to seamlessly run, edit, and debug Application.OnTime scripts with high performance.
- 1. Open your macro workbook: Launch WPS Spreadsheet and open your .xlsm file containing the Application.OnTime macro.
- 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon menu to access macro tools.
- 3. Open Visual Basic: Click the 'Visual Basic' icon to launch the VBA Editor, where you can easily implement the Workbook_BeforeClose cancellation logic.
- 4. Run and Save: Test your macros directly in WPS Office and save the file with perfect format preservation.
Frequently Asked Questions
Why does my Excel file keep reopening after I close it?
This happens when an Application.OnTime macro is still pending in the background. Because the timer is managed by the Excel application itself, it will reopen the associated workbook to execute the macro when the scheduled time arrives. You must cancel the timer using Workbook_BeforeClose to prevent this.
What happens if I don't use On Error Resume Next when canceling the timer?
If you try to cancel an Application.OnTime schedule that has already run or was never started, Excel will generate a Runtime Error 1004. Adding 'On Error Resume Next' before the cancellation command safely ignores this error.
Can I use a local variable instead of a Public variable for the time?
No. The scheduled time must be passed exactly as it was set to cancel it. Since you set the timer in a standard module but cancel it in the 'ThisWorkbook' module, the variable must be declared as Public so it can be accessed across different modules.
Does this cancellation method work if I have multiple OnTime macros running?
Yes, but you will need to declare a separate Public variable for each scheduled macro (e.g., dtmNext1, dtmNext2) and cancel each one individually using the False parameter in the Workbook_BeforeClose event.
