Setting Breakpoints in an Open Event Handler

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.


8 Responses to “Setting Breakpoints in an Open Event Handler”

  1. 1 Dick Kusleika June 12, 2009 at 8:27 pm

    I just put a Stop line in there.

  2. 2 Roy MacLean June 13, 2009 at 10:58 am

    Thanks Dick, I never knew that existed! I wonder if there are any other situations where you would need it, rather than just setting a breakpoint. Or maybe it’s a bit of the language that pre-dates breakpoints.

  3. 3 Dick Kusleika June 14, 2009 at 2:25 pm

    I still prefer breakpoints because they go away when I close the project. Maybe, just maybe, I’ve accidentally left a Stop command in my code. 🙂

  4. 4 Peder Schmedling June 15, 2009 at 6:44 am

    The Stop statement is OK for hard coding a breakpoint quick and dirty. A more flexible approach is to use the .Assert method of the Debug object. “Debug.Assert False” does the same as a “Stop” statement. Using assertions your code can become more dynamic, short example:

    Option Explicit
    Const gcEnableCustomBreakpoints As Boolean = True
    Const gcDebugConfig As Boolean = True

    Private Sub Workbook_Open()
    Dim boolShouldBeTrue
    ‘Implement custom, pre-defined breakpoints
    Debug.Assert Not gcEnableCustomBreakpoints
    ‘Perform some action with boolShouldBeTrue and check
    ‘boolShouldBeTrue = True
    Debug.Assert boolShouldBeTrue
    ‘Break if “debug mode” enabled AND test fails
    Debug.Assert (Not gcDebugConfig) Or boolShouldBeTrue
    End Sub

  5. 5 Roy MacLean June 15, 2009 at 9:19 am

    Thanks, Peter,
    I’d rather forgotten about Debug.Assert as well. I suppose that instead of “Debug.Assert False”, you could have any error-generating statement, such as accessing an empty object variable.

    Interesting with regard to using Debug.Assert for proper assertions – maybe one should use it more for preconditions, at least. It’s a shame that switching the checks on/off can’t be controlled at the Application level, say.

  6. 6 Roy MacLean June 15, 2009 at 9:56 am

    With the regard to my last remark, there is the notion of conditional compilation, using Project-level parameters (in the project properties). So one could have something like this:

    #If prDebug = 1 Then
    #End If

    But that’s rather verbose (and you seem to need numerical values). Shame we couldn’t have predefined properties for debugPrint and debugAssert, which are handled by the Debug object, rather by explicit conditionals.

  7. 7 mild acne September 19, 2014 at 6:30 am

    When I originally commented I seem to have clicked the
    -Notify me when new comments are added- checkbox and from now on every time a comment
    is added I recieve 4 emails with the same comment. There has
    to be an easy method you can remove me from that service?

  8. 8 Betsy September 25, 2014 at 10:38 am

    I realy like what yoou guys tnd too be up too.This type of clever work and
    exposure! Keeep up tthe awesome works guys I’ve added you gjys too our blogroll.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

June 2009
« May   Jul »

%d bloggers like this: