Closed Thread
Results 1 to 3 of 3

Excel crashes due to incomplete VBA compilation?

  1. #1
    Registered User
    Join Date

    Excel crashes due to incomplete VBA compilation?


    It's a long story. If anyone has some suggestions, I'd be very grateful.

    My workbook works fine in Excel 2003 and in Excel 2007 compatibility mode. The problems start showing up when the file is converted to the 2007 format.

    When the original xls file is converted to xlsm format and then reopened using the Trust Center option "run all macros without notification", I get the error:
    run-time error 57121 (Application-defined or object-defined error)
    which then proceeds to crash Excel entirely if I select the "Debug" option.

    However, if I press the "End" option when the error appears, there doesn't appear to be any further problems. Also, the error never appears if the Trust Center is set to "disable all macros with notification" and I manually enable the macros when opening the file.

    This led me to suspect event code is being executed before the entire application is fully compiled, and I found the following problem.

    Incomplete Compilation?
    When I edit the VBA code so that the editor flags it as needing to be compiled (simply adding a comment works), and I close the file without compiling, then the above problem goes away. I also noticed that compiled VBA doesn't stay compiled when re-opening the xlsm file.

    ISSUE (and solution?) #1: Do not save the file when fully compiled. Instead, make sure it needs compiling before being saved.

    Unfortunately, after fixing the above problem, a more serious problem became forefront when the file is opened:

    "Compile Error: Circular dependencies between modules".
    or "Compile Error: Member or data not found".

    As with the original problem, this happens only when the Trust Center is set to "run all macros without notification". This time I could tell where in the compile sequence it failed, as it brought up VBE automatically, pointing to the problem subroutine in break mode. Turns out it couldn't find one of the worksheet objects. To fix, I had to replace code such as

    Please Login or Register  to view this content.
    with the code
    Please Login or Register  to view this content.
    In other words, the worksheet code-named InputSheet was not yet properly visible to the compiler. It was visible if I stopped execution and recompiled, but by then, the initialization routines had already aborted. By using the indexed Worksheets method, it resolves objects at run-time and I was able to avoid having the compiler having to know about the object, but this is obviously a serious flaw. Also if a user changes the name of the worksheet tab, this workaround will fail, which is why I prefer using the worksheet's code-name.

    ActiveX and Worksheet Event interactions

    Although it seemed that things were getting better, turns out I was mistaken. ActiveX and worksheet events were plaguing the whole system. The events would appear to execute OK but when the file was saved and then re-opened, Excel would immediately crash. These are normal ActiveX controls such as Microsoft's ComboBox.

    ISSUE 2: Event triggered code can damage the file because an object isn't yet initialized. This problem exists on ActiveX events as well as worksheet events. The damage occurs before (during?) the filesave, but Excel crashes immediately after the workbook is reopened with "run all macros without notification".

    I ran into variants of issue 2 when opening the xslm file using the file converter for Excel 2003. (Remember that the original file fully functions in Excel 2003). When converting the xlsm file back to Excel 2003, I get the following error:
    Uninitialized ActiveX controls will not be opened in this version of Excel.
    Can't exit design mode because Control 'OK_Button' can not be created.

    The worksheet code-name InputSheet that triggered the "missing object" during compile has ActiveX controls. So I thought maybe I simply need to rebuild the controls from scratch or delete the event code for the ActiveX controls. Neither option worked. Things would work the first time the file was opened, but after saving and reopening it, Excel would crash immediately. No luck trying to find out what aspect didn't work.

    Sorry about such a long bug report, but I thought the more info, the better the chances something I observed might have been seen before. If this can be fixed, it would be wonderful. In the meantime, I can continue to operate using the .xls compatibility mode.

    Also posted on MSDN

  2. #2
    Registered User
    Join Date
    MS-Off Ver
    Excel 2003

    Re: Excel crashes due to incomplete VBA compilation?

    I am having the exact same problem.

    I am initializing ActiveX just fine on 3 other worksheets, but one of them fails to initialize when I convert from Macro-enabled 2007 file to 2003 file.

    In fact it causes Excel to crash. It tries to recover, but then when I click on recovered worksheet it crashes Excel entirely!!

    Strange. I expect this is another wonderful undocumented feature of Excel 2007.

    Please help! Maybe there is a better way to initialize ActiveX in my code.

  3. #3
    Forum Guru shg's Avatar
    Join Date
    The Great State of Texas
    MS-Off Ver
    2003, 2010

    Re: Excel crashes due to incomplete VBA compilation?

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Entia non sunt multiplicanda sine necessitate

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1