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 aDoEvents
- When the debugger hits a ghost breakpoint, press
Ctrl+Shift+F8
, notF5
- 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.