Dutch Gemini's Weblog Pages

February 22, 2010

Severe Excel crash adding Event Procedure to ThisWorkbook

Filed under: Excel,Programming,VBA — dutchgemini @ 10:45 am
Tags: , , , ,

Excel generates a severe crash in module VBE6.DLL when calling the .CreateEventProc method in the attempt to add an event procedure to the code module of ThisWorkbook from VBA. The crash generates the following Event Log entry (examples are for Excel 2002/XP and for 2007):

Faulting application excel.exe, version 10.0.6856.0, faulting module vbe6.dll, version 6.5.10.24, fault address 0x00024049.

Faulting application excel.exe, version 12.0.6331.5000, stamp 48fa2869, faulting module vbe6.dll, version 6.5.10.24, stamp 464105f1, debug? 0, fault address 0x0002401d.

At the same time the following Fault bucket numbers (in random order) can be observed: 1558228417, 1580105499, 1558360553, 1558124050 and 1558351305.

When Excel crashes, most of the times an Automation Error error pops up, on Excel 2003 & 2007 the message reads Automation Error. Exception occurred. This message needs to be closed before Excel can continue to recover work (or whatever is left from it). The message brings me to think there is a problem with automation -indeed I use automation, but all advise, including that from Microsoft, didn’t help a bit.

The crash is persistent on all versions of Excel tested so far, from 2000 up to 2007.

Inside my VBA code (embedded in an Add-In and activated from a button on a custom CommandBar) I write code to a few event procedures in a workbook so that Excels reacts when for instance the workbook is opened or saved afterwards. If the desired event procedure is missing then I add it using the above mentioned .CreateEventProc method. I also tried .AddFromString as recommended somewhere in a forum but without any positive result.

I have, however, been able to isolate the particular condition in Excel for this crash to occur but also when it is not occurring anymore, and that is:

  • If I start Excel and with a blank workbook and I execute my code, Excel crashes.
  • If I start Excel, put the active workbook in Design mode and next execute my code, then Excel never crashes, not even when I remove the Design Mode from any of the workbooks and runh my code again. Design mode is the state Excel keeps a workbook in either when macros are disabled or when the ‘Design Mode’ button on the Control Toolbox command bar (the ID of this button is 1605) is in its ‘pressed’ state.
  • If I open a workbook that already has some event procedure in ThisWorkbook, Excel also never crashes.

Unfortunately, the Design Mode is a per-workbook setting and must be set manually. When you add a new workbook the Design Mode is not active on this new workbook. And if you try to set Design Mode via code, using the CommandBarButton’s .Execute method, then Excel immediately stops executing whatever code and returns idle (I have another post on this topic).

I think the problem is related to the VBE component not being properly initialised the first time I call it for creating the event procedures and that the Design Mode in a way or the other puts Excel in a more stable situation, but at the moment it’s nothing more than an educated guess.

I am trying to further isolate -and hopefully solve- the problem and possibly make available a reduced version of the Add-In for download so that you can try the same test.

Dutch

Advertisements

6 Comments »

  1. I have found a solution to the above problem.

    In my office I found a PC that did not crash at all.

    Following the trace in the Event Log of a failing PC, I went checking the VBE6.DLL file. This file is located in ‘C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6’.

    While all failing PCs had version 6.5.10.24 on board, the one working correctly had a more recent version, namely 6.5.10.40. I have no idea why this new version and where it came from or what program installed it, matter of fact after copying the newer version to the failing PCs, Excel did not crash anymore.

    Dutch.

    PS: version 6.5.10.32 of VBE6.DLL also works correctly.

    Comment by dutchgemini — February 23, 2010 @ 12:20 pm | Reply

  2. Version 6.5.10.34 of VBE6.DLL is installed via this hotfix from Microsoft at http://support.microsoft.com/kb/960420/en-us

    Version 6.5.10.40 of VBE6.DLL is installed via this hotfix from Microsoft at http://support.microsoft.com/kb/969403/en-us

    The latest version of VBE6.DLL seems to be 6.5.10.48, available at http://support.microsoft.com/kb/969960/en-us

    Comment by dutchgemini — February 23, 2010 @ 2:39 pm | Reply

  3. Installing (there is no need to register the DLL) Version 6.5.10.40 also resolves the same crash on Excel 2000.

    Comment by dutchgemini — February 23, 2010 @ 3:19 pm | Reply

  4. I have been struggling with this issue for atleast a week.
    This worked like a charm! Thank you so much.

    Comment by Salini — April 25, 2011 @ 7:51 pm | Reply

  5. I had the same issue using Excel 2007 VBA. The fix is to disable the macro/VBA in trust centre on the crashing PC or make a change to the code so it has to be re-compiled (save the file without compiling). The Excel file should be able to open on the crashing PC now. After opening the Excel file, open up VBA and compile the code (Debug->Compile VBAProject), then save it. It should now work with all other conflicting systems.

    Comment by ajibcj — June 15, 2011 @ 7:35 pm | Reply

    • Thx for the tip. I usually work with macros disabled on the XLS/XLSM and turn them on when testing the application as XLA/XLAM so I may have missed this.

      Nevertheless, after I upgraded the VBE6.DLL on the failing PC’s, I never ran into this problem again so Microsoft must have fixed this for me (after sending them an average of 20-30 crash reports per day due to this issue).

      Dutch

      Comment by dutchgemini — June 16, 2011 @ 8:26 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Create a free website or blog at WordPress.com.

%d bloggers like this: