Excel VBA Pause (Break / Stop) & Resume a Macro
Being able to pause a macro while testing VBA code that has been written is a useful way to work out where any ‘bugs’ may be in our code. There are a number of ways that we can break the running of the macro, and then resume it from the point where the macro was interrupted.
The Control and Break keyboard combination
If we hold down the control key, and then press the Pause/Break key when VBA code is running, the code will immediately stop with a debug message warning us that the running of the macro has been interrupted.
We can then press the Continue button in the dialog box that pops up to continue running the macro. Alternatively, if we then press the Debug button in the dialog box, the macro will highlight the position where it stopped running. By resting the mouse over any existing variables, we can then see what is stored in the variables. This can be very useful in debugging our code.
We can then click the Run button in the ribbon (or press F5 on the keyboard) to resume running the macro.
Adding Break Points to the Macro
Before starting the macro, we can insert break points into the macro in order to stop the macro as specific lines of code.
We can run the code by clicking on the Run button in the Ribbon, or by pressing F5 on the keyboard. The macro will stop at the break point.
Press the Run button again (the caption will now say Continue) to resume the macro or press F5.
There may be other times that the running of a VBA macro may need to be paused. This can also be done using the Wait and Sleep methods. These methods are used more in delaying the actual progress of the macro rather than being used to debug the actual code.
For example, this line of code will delay the macro from running until 5 more seconds have passed.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! 
VBA Code Examples Add-in
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.
How to stop VBA code running?
Say I have a button embedded into my spreadsheet that launches some VBA function.
I’d like to have an opportunity to have some sort of a «cancel» button that would stop SomeVBASub execution at an arbitrary moment, and I’m not into involving Ctrl+Break here, ’cause I’d like to do it silently.
I guess this should be quite common issue, any ideas?
7 Answers 7
Add another button called «CancelButton» that sets a flag, and then check for that flag.
If you have long loops in the «stuff» then check for it there too and exit if it’s set. Use DoEvents inside long loops to ensure that the UI works.
How about Application.EnableCancelKey — Use the Esc button
Or, if you want to avoid the use of a global variable you could use the rarely used .Tag property of the userform:
what jamietre said, but
I do this a lot. A lot. 🙂
I have got used to using «DoEvents» more often, but still tend to set things running without really double checking a sure stop method.
Then, today, having done it again, I thought, «Well just wait for the end in 3 hours», and started paddling around in the ribbon. Earlier, I had noticed in the «View» section of the Ribbon a «Macros» pull down, and thought I have a look to see if I could see my interminable Macro running.
I now realise you can also get this up using Alt-F8.
Then I thought, well what if I «Step into» a different Macro, would that rescue me? It did 🙂 It also works if you step into your running Macro (but you still lose where you’re upto), unless you are a very lazy programmer like me and declare lots of «Global» variables, in which case the Global data is retained 🙂
Interrupt a Macro
You can interrupt a macro in Excel at any time by pressing Esc or Ctrl + Break.
Place a command button on your worksheet and add the following code lines:
Dim x As Long
x = 5
Do While x > 2
x = x + 1
Loop
1. Click the command button on the sheet. This macro never stops because the part after ‘Do While’ will always be true (x will always be higher than 2).
2. To halt this infinite loop, press Esc or Ctrl + Break. The following dialog box will appear:
3. Click End to end the macro, click Debug to take a look at the macro in the Visual Basic Editor.
4. Add the following code line at the start of your code if you don’t want users of your program to be able to interrupt your macro (not recommended).
5. Although Excel VBA resets the EnableCancelKey property automatically to xlInterrupt at the end of your macro, it’s good practice (when using the previous code line) to end your macro with the following code line:
Note: if Excel freezes and you cannot interrupt your macro anymore, press Ctrl + Alt + Delete and close Excel.
Excel VBA stop macro execution manually with Esc or Ctrl+Break
This is a question I get asked quite often. Fortunately, the solution is easy to remember!
How to manually stop your macro
In VBA, you can stop your macro execution manually with the Esc key or by pressing Ctrl+Break.
Here are a couple alternatives if the first two keystroke options fail.
- If you’re running a macro that uses a lot of processing power, you may need to repeatedly press the Esc or Ctrl+Break keys before it’s finally recognized.
- If your keyboard has a function key, usually labeled Fn, you may have to hit Ctrl+Fn+Break to manually kill your macro.
- If none of these options work, your last resort is to kill the Excel application in its entirety. One way to do this is to press Ctrl+Alt+Delete, launch the Task Manager, click “Details” or “Processes” and scroll down until you find Excel. Once you find it, click it and select “End task.” This is certainly not the desired way to stop your macro because you’ll lose all your unsaved work!
Stopping an Infinite Loop Demo
Make powerful macros with our free VBA Developer Kit
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more — grab your free copy below.
Running the macro above creates an infinite loop. If you’re brave and you want to test out the keystrokes recommended above, now’s your chance.
Let’s try it! Run the macro and try pressing Esc or Ctrl+Break. You should get a dialog box that looks like this:
All you have to do is click “End” and your macro will stop.
That’s all for today’s tutorial. Short and sweet! When you’re ready to take your VBA to the next level, subscribe using the form below.
Ready to do more with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we’ll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.
Before we go, I want to let you know we designed a suite of VBA Cheat Sheets to make it easier for you to write better macros. We included over 200 tips and 140 macro examples so they have everything you need to know to become a better VBA programmer.