0

In the past few months, debugging Excel VBA has become a nightmare for me.

It looks like, during step-by-step execution, a breakpoint is automatically set after every access to any member of a COM object defined in an external application.

For example, if I press F8 to execute a line that calls a function, and that function accesses hundreds of COM objects from an external application, the execution stops after every access, and I need to either press F8 hundreds of times, or set a breakpoint to the line after the original line and press F5. Pressing shift+F8 doesn't help, it will still stop to these phantom breakpoints.

I have test suites that I used to run by pressing F8 a handful of times, now I can only run them by setting one breakpoint per line and pressing F5 instead of F8.

I noticed this change in behavior (at least) a few months ago, but, since I don't work in VBA every day, I never bothered looking for help.

If this question does not really meet the Stackoverflow requirements, please let me know if there is a better place for it.

EDIT

Following Tim's suggestion in the comment, I tried to create an example that reproduces the problem. While working on the example I noticed that:

  • The ghost breakpoints do not appear after every access to a COM object, they appear after some accesses and after some DoEvents
  • The longer you work, and the likelier they are to appear, but I've seen them on the first try
  • The more breakpoints you have, the the likelier they are to appear, but I've seen them without any breakpoints
  • If you work long enough, they start appearing after every access and every DoEvents

The problem reproduces by creating a module with the following code, then:

  • Click on Sub Test()
  • Press F8 twice
  • Press Shift+F8 -> the macro will run for a few seconds, and maybe, stop at a ghost breakpoint
  • If the debugger doesn't stop at any ghost breakpoint, while the macro is still running, create a breakpoint by clicking on the gutter at the line with Debug.Print "Done" -> the macro will stop after a DoEvents
  • When the debugger hits a ghost breakpoint, press Ctrl+Shift+F8, not F5
  • If the problem does not reproduce, try again, keep adding and removing that breakpoint or other breakpoints. Sooner or later it will start stopping after WordApp.Visible, CreateObject("Word.Application") or other COM accesses.
Option Explicit

Dim WordApp As Object

Sub Test()
  Test2
End Sub

Sub Test2()
  Dim I As Integer
  For I = 1 To 10
    Debug.Print I
    CreateWordAppObject
    CloseWord
  Next I
  Debug.Print "Done"
End Sub

Sub CloseWord()
  Dim X, Doc As Object
  Debug.Print "Closing Word";
  For Each Doc In WordApp.Documents
    Debug.Print "Closing document"
    Doc.Close SaveChanges:=False
  Next Doc

  Debug.Print "Closing Word";
  WordApp.Quit

  On Error Resume Next
  Do
    Err.Clear
    X = WordApp.Visible
    If Err Then Exit Do

    Debug.Print ".";

    DoEvents
  Loop
  On Error GoTo 0
  Debug.Print

  DoEvents
End Sub

Sub CreateWordAppObject()
  Dim IsGlobalValid As Boolean, N As Integer, Doc As Object
  On Error Resume Next
  IsGlobalValid = WordApp.Visible
  If Err.Number Then Set WordApp = Nothing

  On Error Resume Next
  Set WordApp = GetObject(, "Word.Application")
  On Error GoTo 0

  If WordApp Is Nothing Then
    Debug.Print "Starting Word"
    Set WordApp = CreateObject("Word.Application")
  End If

  Set Doc = WordApp.Documents.Add
  For N = 1 To 10
    Doc.Content.Text = Doc.Content.Text & "Word " & N
  Next N

  DoEvents
End Sub

EDIT2

Here is a video that shows how the problem reproduces. Before starting recording I have created a new file and added a new module. Then the video shows that I pasted the code and stepped through the code. I only use the mouse to step through the code, so it's clearer than if I used keyboard shortcuts.

In this case it decided to stop at (maybe) all the COM accesses. ghost breakpoints

stenci
  • 8,290
  • 14
  • 64
  • 104
  • Would be helpful to post a specific example which demonstrates the problem you're describing (ideally one we can run). – Tim Williams Aug 22 '23 at 21:54
  • Hmmm... I didn't post any reproducible example because my real applications connect to CAD, CAM and other uncommon applications, and it would be impossible to reproduce. But I can try to reproduce the problem by using Word or other widely available COM server.... give me a few minutes, I will try.... – stenci Aug 22 '23 at 21:58
  • Some other Office app was what I was thinking of... – Tim Williams Aug 22 '23 at 22:01
  • It seems to be a stress related problem. I restarted Excel, set a breakpoint where I had it earlier, ran to the breakpoint, pressed shift+F8 and now it worked as expected. I removed the breakpoint, ran a test suite, set the same breakpoint, ran to the breakpoint, pressed shift+F8 and now it stopped down in the call stack at the (maybe) first access to a COM object. So it looks like the ghost breakpoints appear after running some other code, including that test suite. The addin I'm working on has 60,000+ lines, 10+ references. It's late now, I will try to reproduce the problem tomorrow. – stenci Aug 22 '23 at 22:27
  • @TimWilliams I have followed your suggestion and I have added some code that seems to reliably reproduce the problem. – stenci Aug 23 '23 at 20:36
  • Can't seem to reproduce the problem here using your posted example. – Tim Williams Aug 23 '23 at 21:00
  • @TimWilliams Thanks for trying. I have edited the post and added a video that shows what happens to me. Debugging in these conditions is a nightmare. I can only set a breakpoint and press `F5` or `F8`, `shift+F8` and `ctrl+shift+F8` are unusable. – stenci Aug 23 '23 at 22:34
  • This might be useful? https://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted – Tim Williams Aug 23 '23 at 22:40
  • @TimWilliams No. I know that problem it has always been there, while this problem is relatively new. I also added an answer that I think is better than any of the answers in that post: https://stackoverflow.com/a/76970468/1899628 – stenci Aug 24 '23 at 14:47

0 Answers0