VBA & Macro Problems

How to Stop a VBA Macro Started with Application.Run

Adam Davis Oct 16, 2026 868 views

Question details

The user needs a method to safely interrupt or terminate a running VBA procedure that was initiated using the Application.Run method without crashing the application.

How to Stop a VBA Macro Started with Application.Run

Product: Spreadsheet
Device & OS: not provided
Scenario: A long-running macro is executed from a calling procedure, and the user requires a way to halt the execution manually via a command while the calling program continues to run.
Observed behavior: Procedures launched with Application.Run take full control of the application thread by default and cannot be forcibly interrupted by the calling procedure without cooperative code.

Before you start

Ensure your long-running macro utilizes an iterative loop (such as a For, Do While, or Do Until loop) where it can periodically check for a cancellation signal.

Solution 1Recommended

Use a Global Boolean Variable with DoEvents

Implement a shared public flag that the macro checks periodically, yielding execution time to the application so a separate 'Stop' button click can be processed.

Because VBA is generally single-threaded, a macro launched via Application.Run seizes control of the interface. To interrupt it gracefully, the running macro must voluntarily yield execution time to the application using the DoEvents function. This allows user inputs, like clicking a 'Stop' button, to register and change the state of a global variable.

Declare a Public Variable

At the very top of your standard VBA module (before any Sub or Function), declare a global variable by typing: 'Public StopMacro As Boolean'.

Initialize the Variable

In the calling procedure, set the variable to False ('StopMacro = False') immediately before launching the macro with Application.Run.

Add DoEvents to the Loop

Inside the main loop of the called macro, insert the 'DoEvents' command. This forces the application to pause and process any pending interface interactions.

Check the Stop Flag

Right after the DoEvents line, add an evaluation statement: 'If StopMacro = True Then Exit Sub' (or Exit Do). This cleanly terminates the loop if the flag is triggered.

Create a Stop Button Macro

Create a short macro named 'CancelMacro' that contains only one line of code: 'StopMacro = True'. Assign this macro to a command button on your worksheet.

Use a Global Boolean Variable with DoEvents

Optimize Performance: Calling DoEvents on every iteration of a tight, rapid loop can significantly slow down your macro. Consider calling it only every nth iteration using the Mod operator (e.g., If i Mod 100 = 0 Then DoEvents) to balance responsiveness and speed.

Efficient Macro Execution

Write and Manage VBA Macros Smoothly with WPS Spreadsheet

WPS Spreadsheet offers powerful built-in VBA support, allowing you to write, edit, and safely interrupt complex scripts like Application.Run with complete Microsoft Excel compatibility.

1. Open the VBA Editor: Launch WPS Spreadsheet, navigate to the 'Developer' tab on the ribbon, and click 'Visual Basic' to open the built-in VBA editor.
2. Insert Your Code: Click 'Insert' > 'Module' and paste your Application.Run calling macro, the public Boolean variable, and the worker macro containing the DoEvents loop.
3. Add a Form Control: Return to your spreadsheet interface, click 'Insert' under the Developer tab, and place a 'Button (Form Control)' onto your worksheet.
4. Assign the Stop Macro: Right-click the newly inserted button, select 'Assign Macro', and link it to the subroutine that sets your StopMacro variable to True.

Fully compatible with Microsoft Excel VBA syntax and the Application.Run methodBuilt-in robust VBA editor with debugging tools and real-time error checkingLightweight software architecture for faster, more stable macro executionFree to download with a familiar interface that requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does the spreadsheet freeze when my Application.Run macro is executing?

By default, VBA runs synchronously and monopolizes the application thread. Without a 'DoEvents' command embedded in your loop, the application interface cannot update, repaint, or register mouse clicks until the entire macro finishes running.

Can I use the Esc key or Ctrl+Break to stop an Application.Run macro?

Yes, pressing Esc or Ctrl+Break can forcibly halt execution and trigger a debugging prompt. However, this is considered a 'hard break.' It is generally not recommended for end-users because it prevents the code from exiting gracefully, which might leave background processes running or variables un-cleared.

Does using DoEvents negatively impact my macro's execution speed?

Yes. Every time 'DoEvents' is called, VBA pauses to check the operating system's message queue. Doing this thousands of times per second inside a fast loop will cause a noticeable drop in macro performance. It is best used conditionally (e.g., once every 50 loops).

How can I automatically terminate a macro after a specific time limit?

You can record the start time using VBA's Timer function at the beginning of the macro (e.g., StartTime = Timer). Inside your loop, regularly check the elapsed time: 'If Timer - StartTime > 60 Then Exit Sub'. This will safely stop the macro after 60 seconds.

Excel & Data Problems