vba excel: Breaking into vba code when a message box is caught in infinite loop

Question

This can be a very valuable if there is an answer to it:

You have a messagebox within a loop, and you can't break into the code because the Messagbox keeps popping up. Once the Messagebox is up, you can't really go back to the vba developement window to stop the program, because it is a modal window that takes over then entire vba.

Any good way to break into this? I don't want to go through a loop of 10000 items. I've seen enough, so I am ready to break into the code--to stop the code from running further.

How do I do this gracefully, without losing possibly any edits I may have made?

Maybe this can't be done, but worth knowing if it is possible.

Try hammering `Esc`. In future build your `MsgBox`s better. Compile the message that you want and then display in one box — Tom, Oct 23 '18 at 14:58
this is a good reason to save all changes before running any code. Then you could just shut it down and reopen. — Scott Craner, Oct 23 '18 at 14:59

score 1 · Answer 1 · edited Oct 12 '21 at 05:31

1

You can press Ctrl+Break to break into the code even when a MessageBox is being displayed.

edited Oct 12 '21 at 05:31

Cody Gray - on strike

239,200
50
490
574

answered Oct 23 '18 at 14:59

Heap of Pinto Beans

677
2
12
23

1

It does work, but if the loop has been running for a while, it seems not to work. it might be okay in your situation as the msgbox will not let the loop go wild. – Davesexcel Oct 23 '18 at 15:03

score 0 · Answer 2 · answered Oct 23 '18 at 15:21

if i interpret correctly your need :

"I don't want to go through a loop of 10000 items. I've seen enough, so I am ready to break into the code--to stop the code from running further."

Instead of a msgbox, you could use

Debug.print InformationYouNeed
stop

Then read the result and stop the code if you got the info you needed.

Can you provide the code you're using ?

Michał Turczyn · Answer 3 · 2020-04-16T07:03:31.667

The easiest way is to set breakpoint on MsgBox line.

In order to do that just press F9, when you have cursor on that line.

Then the execution will stop before showing message box and you'll be able to inspect all variables as well as stop the code.

After setting breakpoint, the line wil l be highlighted with dark red:

(insted of using F9 you can just click in place of the red circle - this will also set a brakpoint)

Other way is to show messagebox using WinApi, as descirbed here. But it's more complicated than using breakpoints. Code snippet for 64-bit windows:

Private Declare PtrSafe Function MessageBox _
        Lib "User32" Alias "MessageBoxA" _
           (ByVal hWnd As Long, _
            ByVal lpText As String, _
            ByVal lpCaption As String, _
            ByVal wType As Long) _
        As Long

Sub MsgBoxTests()
    ' Execution will show message box and stop on this line,
    ' at this point you will be able to stop execution, but still, you
    ' will need to dismiss messagebox
    MessageBox &O0, "This is a native Message Box", "My Box", vbOKOnly
End Sub

vba excel: Breaking into vba code when a message box is caught in infinite loop

3 Answers3