'Control Break out of Infinite Loop In 2010 (2013) Excel VBA
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?
Solution 1:[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.
Solution 2:[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 :) )
Solution 3:[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
Solution 4:[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.
Solution 5:[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.
Solution 6:[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
Solution 7:[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.
Solution 8:[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.
Solution 9:[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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow