Control Break out of Infinite Loop In 2010 (2013) Excel VBA

Question

If I write code creating an infinite loop, with my new Excel, the Ctrl + Break no longer works. Neither does the Esc key, etc.

I've looked all over the web and it appears that Microsoft has a bug and doesn't care to fix it.

Is there a way to re-introduce the Ctrl + Break function to VBA so if this happens in the future I don't lose work / force close?

Winstonwinstonn

Answer 1

Alt + Esc. Hold down the keys until it breaks.

From Windows 7 on, this will cycle through all open windows. Pay no mind, just keep squeezing it.

Chrotoem

This does not work if the endless loop includes showing a MessageBox. The MsgBox blocks all input into Excel. – Kayleen

Held keys down for 10 seconds and it worked - I can't believe it. Thanks Johhn – Membrane

This didn't work for me, but ctrl + pause break, did. Be sure to click 'End', not 'Debug' on the pop up window. – Marillin

You saved my life!!! super useful tip, thank you so much!!! – Uncouth

as one already wrote: ALT + ESC on windows 10 cycles through open windows, so this does not work – Skees

This does work in Windows 10! Minimize all other windows so that just the Excel windows are showing. Then hold down ALT+ESC. In a few seconds, Excel will stop and ask if you want to debug... – Giulio

Answer 2

On Office 2013, at least, Ctrl+Scroll Lock does it. I didn't need to change any settings or enable anything.

(Blatant plug: my blog post linking to the original source of this info :) )

Encomium

@Colin I had much the same reaction when I first saw it :) . – Encomium

Even though Break is on the Pause key, Ctrl+Scroll Lock worked! – Fionnula

Answer 3

One way to mitigate this very annoying behaviour is to include

DoEvents

In the loop. It doesn't have to be executed every iteration, so long as it is called periodically, Ctrl Break will still work.

Note: I usually remove this after the code is debugged, to avoid any performance impact

Phenylamine

Answer 4

For those who tried the previously proposed solutions to no avail, try this. It worked for me (windows8, Excel 2016)

On the VBA code window, left-click and hold (as though you are going to drag that window) while holding down the Alt+ESC keys. It broke the execution and asked me if I wanted to continue, or debug... I of course chose debug.

Punctuation

ALT + ESC on windows 10 cycles through open windows, so this does not work. – Landward

While it's true that ALT + ESC cycles through open windows, it can still be effective in breaking the loop (at least sometimes) – Phenylamine

Using the mouse together with alt+esc it works. Maybe the mouse stops the window cycling and focuses on the break with alt+esc. Evening saver.. – Skees

It has worked for me under Windows 10, nice trick – Tabulator

Pressing ALT + ESC was not the suggestion. Perhaps you misread the suggested answer. – Punctuation

Answer 5

If it can help anyone, pressing Ctrl+break when the macro is not running only works if you do it outside of the developer.

Secretarygeneral

Answer 6

An alternative will be introduce error intentionally in the loop. Say for e.g. your loop should not be running more than 1000 times:

Function XYZ()
do while(..)
errcnt = errcnt + 1

if errcnt > 1000 then
  cells.find(what:="Chunk Norris").activate
exit function
endif

loop
end function

it will produce an error and 'break' the code

Debbidebbie

Thanks Alex, that's an interesting way to go about it. But I'm wondering if I can change any commands in VBA like I can for, say, what Ctrl + Shift + 0, which allows me to unhide rows. That command was lost in the newer versions of Excel, but I was able to reintroduce it. Thanks again! – Winstonwinstonn

haha ok, You can check in the immediate window (ctrl-G) for EnableCancelKey; Type "?Application.EnableCancelKey" without the double quote, and see if it returns 0 – Debbidebbie

Far better to use debug.assert errcnt <= 1000 to get this effect. – Phenylamine

Alex- oddly enough application.enablecancelkey = 1, so that isn't the issue. I think I am going to have to be unsatisfied with this and will take Chris' suggestion... Thank you gentlemen! – Winstonwinstonn

@Tommyz no worries in fact Chris's suggestion will be my next step of testing. Glad there's solution works for you! – Debbidebbie

Answer 7

Break (Ctrl + Break) is also not available in the VBA editor if you are in design mode. In that case you can press the Design Mode icon which is to the right of the square stop icon. This brings the editor back in regular mode and the break option then becomes available.

Gao

Answer 8

There is no "Break" button on my poor keyboard. My only alternative is to hit the X to close the program or alt+F4. I'll have to code something as well.

Pyridoxine

Answer 9

From answers.microsoft.com, simply press ESC a few seconds when the focus is on your Excel window. At least, it works for me on Excel 2016.

Tabulator