Excel debug mode failure

Question

I have a system of workbooks for various departments that each have auto_open macros. I also have a controlling workbook that, on demand, can run its own macro to open each book in the system and collect information. It normally works great.

However, when debugging the macro in the central workbook becomes necessary, stepping into the command to open any of the supporting books with an auto_open will subvert the debugger and cause the remainder of the code to execute as if the F5 key had been pressed.

I looked for a debug setting to prevent this, as well as any "Open" method parameters that may alter this behavior without luck. Google searches were fruitless as well. Is there any way to tell Excel debugger to maintain program control in this situation? Help me stackoverflow, you're my only hope.

Answer 1

As we have observed, the Workbooks.Open method does not trigger the Auto_Open event, so putting a breakpoint within the 'controlling' workbook, after the new workbook is opened, will not allow you to enter and debug code as you may need to do.

One workaround would be to insert a line of code with a breakpoint that manually calls the Auto_Open procedure. This should allow you to debug code in each of the books that are opened:

Sub test()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\path_to\your_file.xlsm")

Application.Run wb.Name & "!Auto_Open" '// PUT BREAKPOINT HERE!

End Sub

Alternatively, as suggested HERE, port the code from the Auto_Open event to the Workbook_Open event.

I should have thought of that sooner... I have only ever used Auto_Open with XLA/XLAM Add-In files, never with an ordinary workbook file where the Workbook_Open event should suffice.