Stopping Excel Macro executution when pressing Esc won't work

Question

I'm running excel 2007 on XP.

Is there a way to stop a macro from running during its execution other than pressing escape? Usually if I think I created an infinate loop or otherwise messed something up I hit escape and it throws an error but the macro stops.

This time (and I've done it before but not this badly), I set up a msgbox for some quick debugging. Turns out it had to loop about 6000 times, which made means I had to "OK" 6000 message boxes, which took several minutes. I didn't save before running (another mistake) so I couldn't open task manager to exit.

Is there another way to stop the execution of a macro in case I goof up like this again?

Tomalak · Accepted Answer · 2017-01-24T13:03:06.600

64

Use CRTL+BREAK to suspend execution at any point. You will be put into break mode and can press F5 to continue the execution or F8 to execute the code step-by-step in the visual debugger.

Of course this only works when there is no message box open, so if your VBA code constantly opens message boxes for some reason it will become a little tricky to press the keys at the right moment.

You can even edit most of the code while it is running.

Use Debug.Print to print out messages to the Immediate Window in the VBA editor, that's way more convenient than MsgBox.

Use breakpoints or the Stop keyword to automatically halt execution in interesting areas.

You can use Debug.Assert to halt execution conditionally.

edited Jan 24 '17 at 13:03

answered Jul 21 '11 at 13:38

Tomalak

332,285
67
532
628

1

on my Lenovo used Fn B [link](https://forums.lenovo.com/t5/ThinkPad-11e-Windows-E-and-Edge/E220s-How-to-press-Ctrl-Break/ta-p/560643) – Mille Bii Jul 10 '19 at 10:34
5

Excel will not stop until it reaches a point where it looks for key presses. If you have a tight endless loop, it will never stop until you kill excel using task manager, in which case you will lose anything not saved. If you add a DoEvents statement inside your loop it will force Excel to process events like key presses. (It will also slow your code down.) – Mike Lewis Jul 22 '20 at 19:27

score 21 · Answer 2 · answered Dec 25 '15 at 03:43

21

CTRL + SCR LK (Scroll Lock) worked for me.

answered Dec 25 '15 at 03:43

VBANoob

211
2
2

Worked in Access 2010 with message box open – jcarroll Jun 21 '16 at 16:15
1

Notably, this worked for me when I did not have a `break` key on my keyboard. – PatKilg Jul 20 '16 at 19:40
This crashed to the blue screen of death on a keyboard with a `break` key, work computer. – Nathan Dobie Mar 10 '17 at 15:42
I was stuck because MS Sculpt keyboard does not have a break key and Escape did not help. This works for me in Outlook VBA – Miserable Variable Jan 23 '18 at 00:00

score 12 · Answer 3 · answered Sep 22 '17 at 06:27

Sometimes, the right set of keys (Pause, Break or ScrLk) are not available on the keyboard (mostly happens with laptop users) and pressing Esc 2, 3 or multiple times doesn't halt the macro too.

I got stuck too and eventually found the solution in accessibility feature of Windows after which I tried all the researched options and 3 of them worked for me in 3 different scenarios.

Step #01: If your keyboard does not have a specific key, please do not worry and open the 'OnScreen Keyboard' from Windows Utilities by pressing Win + U.

Step #02: Now, try any of the below option and of them will definitely work depending on your system architecture i.e. OS and Office version

Ctrl + Pause
Ctrl + ScrLk
Esc + Esc (Press twice consecutively)

You will be put into break mode using the above key combinations as the macro suspends execution immediately finishing the current task. For eg. if it is pulling the data from web then it will halt immediately before execting any next command but after pulling the data, following which one can press F5 or F8 to continue the debugging.

I'm afraid none of those work, even with onscreen KB, with Excel 2016. — RBerman, Mar 11 '20 at 18:40

score 4 · Answer 4 · answered Jan 24 '16 at 09:34

You can stop a macro by pressing ctrl + break but if you don't have the break key you could use this autohotkey (open source) code:

+ESC:: SendInput {CtrlBreak} return

Pressing shift + Escape will be like pressing ctrl + break and thus will stop your macro.

All the glory to this page

score 3 · Answer 5 · answered Sep 10 '17 at 04:56

I also like to use MsgBox for debugging, and I've run into this same issue more than once. Now I always add a Cancel button to the popup, and exit the macro if Cancel is pressed. Example code:

    If MsgBox("Debug message", vbOKCancel, "Debugging") = vbCancel Then Exit Sub

score 2 · Answer 6 · answered Nov 14 '20 at 05:19

2

Just Keep pressing ESC key. It will stop the VBA. This methods works when you get infinite MsgBox s

answered Nov 14 '20 at 05:19

Kavindu Pasan Kavithilaka

450
6
12

score 1 · Answer 7 · answered Apr 15 '13 at 19:22

1

You can also try pressing the "FN" or function key with the button "Break" or with the button "sys rq" - system request as this - must be pressed together and this stops any running macro

answered Apr 15 '13 at 19:22

AMR

11
1

Does nothing in Excel 2016 – RBerman Mar 11 '20 at 18:41

score 1 · Answer 8 · answered Nov 09 '13 at 19:26

1

I've found that sometimes whem I open a second Excel window and run a macro on that second window, the execution of the first one stops. I don't know why it doesn't work all the time, but you may try.

answered Nov 09 '13 at 19:26

Manuel

11
1

score 1 · Answer 9 · answered Jun 14 '19 at 18:17

1

ESC and CTRL-BREAK did not work for me just now. But CTRL-ESC worked!? No idea why, but I thought I would throw it out there in case it helps someone else. (I had forgotten i = i + 1 in my loop...)

answered Jun 14 '19 at 18:17

bl33p bl00p

71
5

Thanks. Note that if you're stepping though a list of numbers, you can consider using something like: `For i = 1 to 10 step 1`. That would be cleaner and a way of writing less prone to infinite loops! – ptpaterson Sep 17 '19 at 20:56
1

Yes, thanks for the reply. I normally do it as you describe. IIRC in this case I was comparing two different lists and iterating conditionally, so a do loop with i = i + 1 in various places seemed the most efficient. Having said that, I'm an engineer not a programmer so I'm sure I often don't use the best coding practices. – bl33p bl00p Sep 18 '19 at 21:30
Ctrl-Esc brings up start menu in Windows 10 – RBerman Mar 11 '20 at 18:41

score 0 · Answer 10 · answered Apr 18 '17 at 08:46

0

I forgot to comment out a line with a MsgBox before executing my macro. Meaning I'd have to click OK over a hundred thousand times. The ESC key was just escaping the message box but not stopping the execution of the macro. Holding the ESC key continuously for a few seconds helped me stop the execution of the code.

answered Apr 18 '17 at 08:46

Yemi Adeoye

1
1

This is how I was able to live with the code I had as-is, but again, there were several thousand message boxes. It took literally several minutes to get through the execution of macro while I held down the escape/enter key - which was agonizing to say the least. You should keep what the other answers say in mind, because you cannot brute-force your way out of an infinite loop, or a program that never ends. – ptpaterson Apr 18 '17 at 16:14

score 0 · Answer 11 · answered Apr 27 '20 at 03:25

0

My laptop did not have Break nor Scr Lock, so I somehow managed to make it work by pressing Ctrl + Function + Right Shift (to activate 'pause').

answered Apr 27 '20 at 03:25

Syed Mohammed

1

Stopping Excel Macro executution when pressing Esc won't work

11 Answers11

Linked