How can I break long running EXCEL VBA code

Question

Up until a few years ago (don't remember which version of Office/EXCEL it was) I was able to break long-running (or in infinite loop) VBA code by pressing CTRL-BREAK, if the code was performing Input/output. But this is no longer working in EXCEL 2016. Running the following code:

Public Sub doit()
  While 1 = 1
     Debug.Print Now()
  Wend
End Sub

Runs for a while, printing (I/O) the current date-time on the immediate window (it can be interrupted at this point), but after a few seconds EXCEL goes into 'Not Responding' mode, and never recovers (the only option is to End the program; loosing data/coding to last Autorecover point).

Does anyone know why? and if there is a way to break the code while in that Not Responding mode? Is sprinkling DoEvents all over the code the only way to guarantee that we can break?

This is 32 bit EXCEL.

Try Ctrl+Scroll Lock - See https://stackoverflow.com/questions/21737796/control-break-out-of-infinite-loop-in-2010-2013-excel-vba — FunThomas, Jan 17 '22 at 12:15
On some laptop where there is no Scroll-Lock/ Break key, you often have a shortcut to do that (on my Asus it's Fn+B) — Vincent G, Jan 17 '22 at 15:09
"Not responing" means the Windows' window manager has noticed that Excel is not retrieving it's messages from the message queue. `DoEvents` gives Excel a chance to get it's messages. All keyboard input, mouse input, communication between main and sub windows, etc go via a message queue. — KL-1, Jan 17 '22 at 19:48
Also timers and user input messages have low priority, other messages will jump them in the queue. For timers and mousemove messages they go to the end of the queue and any same message already in the queue gets thrown away. — KL-1, Jan 17 '22 at 19:54
Once it becomes 'Not Responding' , no key combination helps (Ctrl+ScrollLock, Ctrl+Brek, shift+ combinations). So I take that there is no way to break the code in this mode (and recover unsaved code/data). Is there a sure way of preventing it? — tinazmu, Jan 17 '22 at 21:15
I'm not sure if it's you flooding the queue by writing to the immediate window. Does a pause between writes help. — KL-1, Jan 17 '22 at 22:54
It is not related to writing to the Debug Window; change the example to write to a file: `Public Sub doit(): Open strFileName For Output As #1: While 1 = 1: Print #1, Now(): Wend: End Sub` Same outcome. — tinazmu, Jan 17 '22 at 23:20
I know that DoEvents alleviates it; I was looking for alternatives without having to sprinkle otherwise unnecessary DoEvents everywhere (see question). — tinazmu, Jan 17 '22 at 23:57
What is unnecessary about `doevents` It suspends your program and lets excel clear it queue then resumes your program. It IS widely misused as some magical keyword. You can also poll the keyboard in your code. Seehttps://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-getasynckeystate — KL-1, Jan 18 '22 at 00:01
When I want VBA to do stuff not related to EXCEL (for some/that part of the work), and I want it fast, DoEvents is normally unnecessary. — tinazmu, Jan 18 '22 at 00:05

score 0 · Accepted Answer · answered Jan 18 '22 at 05:16

The answer in this Linked question:

Control Break out of Infinite Loop In 2010 (2013) Excel VBA Alt-Esc (even though it switches through all open windows) eventually breaks it, allowing us to save the work.

Somehow I missed that thread when I searched; apologies!

How can I break long running EXCEL VBA code

1 Answers1