Suppose you want to handle an Open event: Workbook_Open in Excel or Document_Open in Word. You whack in some code, but it doesn’t quite work first time (a rare occurrence, but your self-esteem is intact).
So you put a breakpoint on the handler, save the workbook, and then … Breakpoints are not saved, so your faulty code runs anyway. Okay, why not just run the code manually from within the VBE? This might suffice, but having opened the document to get into the code, the faulty handler has already run, which might leave things in an ‘unclean’ state (maybe some things have updated, but some haven’t).
Well, I suppose that you could comment out the code, save the workbook, open it, uncomment the code, add a breakpoint, and then run manually. However, there are other events being generated when you open a workbook (Workbook_Activate and Workbook_WindowActivate, at least). If you were handling these as well, it’s possible the Workbook_Open handler might not work in isolation (although Workbook_Open is the first event to occur in the sequence).
One trick that seems to work is to define a dummy form: leave it as UserForm1, with no controls, and ShowModal=True. Then, as the first line of Workbook_Open put UserForm1.Show. When the form is displayed, you can zap over to the VBE and set a breakpoint in Workbook_Open. Go back to Excel, close the form, and then we hit the breakpoint and can debug normally. Any other solutions?
I’ll talk about the facility I was debugging next week. It’s a lighter-weight take on rolling-over data between periodic workbooks, using named ranges.