none
Excel 2007 crashes with Excel 2003 VBA Program in compatibility mode.

    Question

  • I opened the VBA editor (Developer/Visual Basic) for an Excel 2003 file opened in Excel 2007, added more code, and then, at some point, after a very few lines, Excel will not accept any more VBA code into the editor and crashes, with the error report send dialog.

    Up till now, I had been opening the VBA editor before opening the Excel 2003 file, got the warning about included VBA code, clicked OK, and everything worked fine.

    Then I got the idea to open the VBA editor after opening the file.  If one opens the file before opening the VBA editor, then Excel disables the VBA code.  What I had done up till now, was to close the Excel file, open the VBA editor, and then reopen the Excel file, which had worked OK. I only had to do this when I forgot to open the VBA editor first, before opening the Excel 2003 file.  However, as I said above, I got the idea to open the VBA editor afterwards, as a workaround for this problem, with seemingly disastrous results.

    I don't know that this latter procedure caused the Excel file corruption (it is now corrupted - I had to go back to an earlier backup), but, if it didn't, then it is quite a coincidence.

    Has anyone seen this problem before?

    To preempt obvious criticisms, we are in the process of upgrading to Excel 2007, as well as Visual Studio with vb.net eventually, but, in the meantime, the program must remain in an Excel 2003 file in VBA form.

    Tuesday, July 20, 2010 2:23 PM

Answers

  • Hi forger

    Glad you found some information :-)

    The old thing wasn't a forum, it was a newsgroup with a web interface. I agree, it would have been helpful if they'd at least put a message up in the old interface with a link to the new one.

    I suppose there would have been a way to programmatically move the message store to a forum. Whether this would have been useful (and worth the cost, as it could not have simply been "copied" over - different technology) is another question, entirely. There was a time limit on the MSFT newsgroup servers, after which messages were deleted. The content over the last fifteen years was never archived by Microsoft, only (as far as I know) by Google. So it was always more useful to search Google Groups for background information. The newsgroups were only good for a couple of months back...


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by Bessie Zhao Wednesday, July 28, 2010 9:03 AM
    Monday, July 26, 2010 6:49 PM

All replies

  • Hi forger

    I'm not an Excel specialist, but I hope one will come along and contribute his/her experience...

    In your place, I would suspect the VBA modules may be slightly damaged. This can happen (in all versions, not just 2007, specifically). Sometimes, you get warning signals, other times, the file just won't open anymore.

    The usual way to fix this is to export to plain text files & remove all the VBA projects from the workbook, then import them again. This process "cleans them up". The commands are in the right-click menu for the Explorer window, or in the File menu. If you have only a very few modules, this is quickly done manually.

    If you have a lot of modules, look for the "Code Cleaner" (I believe that's what it's called) developed by an Excel MVP. That uses the VB Extensions to do the job for you.


    Cindy Meister, VSTO/Word MVP
    Wednesday, July 21, 2010 8:50 AM
  • Hi Cindy,

    Thanks for your reply. 

    But I suspect that the specific action I did above (i.e. opening the VBA Editor after the Excel file containing code was already opened) triggered the corruption, since it has never happened before, or since.  I would like to report the problem to Microsoft directly, rather than with the Crash Exit dialog, because I don't know that the dialog captures the actions which triggered the event.

    But I don't know how to contact Microsoft Support.  When I explored the contact methods that I was directed to by the Crash Exit dialog, they wanted $90 even to email them.  Why should I pay $90 to report a bug in their application?

    I contacted our IT guy (who is currently on vacation) about this, but I doubt that he has a budget allotment for this.

    Wednesday, July 21, 2010 4:48 PM
  • Hi forger

    If it's a bug, you'll get your money back from support.

    But quite honestly, I doubt it's a bug. Because it usually doen't matter whether the VBA editor is already open or not when you open a document. And if you can't reproduce it, and no one else can reproduce it, then you haven't a chance of getting it recognized as a bug.

    And if it can only be reproduced with the one file, then it's the file that's the problem, not Excel (and not a bug).

    You might be able to log a report on the Connect site, for Excel. Doesn't mean you'll get any kind of interaction or feedback. But it will get looked at.


    Cindy Meister, VSTO/Word MVP
    Wednesday, July 21, 2010 5:08 PM
  • Hi Cindy,

    Thanks for your reply.

    I do think that opening VBA afterwards caused it, because I recall reading something about the VBA code being disabled, if your security settings don't allow it.  I think my settings were OK for Excel 2003, where they were set in the Tools/Options Menu, but they probably need to be reset for Excel 2007.  I didn't know how to do this in the new Office setup, so I never bothered with it.  When I open the VBA Editor beforehand, then Excel gives me the cautionary dialog about embedded code in the Excel file, and asks me to approve opening the file.  This is the customary behavior in Excel 2003, whether or not the VBA Editor is already open.  However, when I opened the VBA Editor in Excel 2007, after the Excel file was already open, Excel did not ask me to allow the VBA code to function.  Nor did it ask me before the VBA Editor was open either, because I think that Excel disabled the VBA code, so it didn't bother to ask me to allow the code to function. 

    So I think that when I opened the VBA Editor and Excel discovered that the previously disabled code was there, Excel was presented with code that had never been approved to function by the User.  So some of the disabling safeguards Excel had previously placed on the code were still in place.  When I then modified the code, this probably caused the VBA code handler to malfunction.

    At least that's my theory.  I'm not going to test my theory, because I really need this program to work.

    Wednesday, July 21, 2010 7:04 PM
  • Hi forger

    Mmm. I've never seen anything like that happen...

    In any case, it would probably make some sense to familiarize yourself with the macro security in Office 2007. It can seem a bit daunting at first (wait until you see what 2010 has in store), but it's not too bad, once you have a chance to think about it.

    1. Excel Options/Trust Center/Trust Center Settings. It's a long way to get there, but once you're there, you have more control than any time over the last 10 years :-)

    2. The two main places to begin are "Macro settings" and "Trusted locations".

    3. Macro Settings: This is what you're familiar with from previous versions, but the option to get a warning and allow you to activate is gone. It's either disable, with or without warnings, or let everything run.

    4. "Trusted locations". Given the change that you can't get a warning and let the code run, this is where you should focus. You can set any folder (with our without subfolders) as "trusted". This means macros in files in such a folder will run, even if you have "disabled" set otherwise. You can add and remove, as you wish/require. By default, the location of your "personal" workbook (template in Word) is automatically trusted, as are the Startup, Templates and Add-ins locations.

    5. Everything else in this dialog box you don't have to worry about, unless you're using something like ActiveX controls or creating COM add-ins. It's good to know where to look for it, but mostly these options can be left alone.


    Cindy Meister, VSTO/Word MVP
    Thursday, July 22, 2010 8:04 AM
  • Hi Cindy,

    Well the Excel crashing problem, when I add code, returned finally.

    I decided to try your VBA Code Cleaner suggestion.  It is at:

    http://www.appspro.com/Utilities/CodeCleaner.htm

    I installed it, and ran it successfully, and Excel crashed again when I tried to add code anyway.

    It is looking very much to me like there is some memory size limitation for code size, which is not documented.  I cannot find anything about it from the forum search tools.

    I only have code on 2 worksheets and 1 module in the workbook, and the first worksheet has 95% of it.

    So I reran the VBA Code Cleaner just to export the code, and I deleted all the code from the first worksheet. 

    Anyway, I am now adding all the code from scratch to the first worksheet.  I started first with the General Declarations Area (with the additional code declarations which caused Excel to crash before), and made sure that this portion compiled, and saved the Excel Workbook file (naturally, I am working on a separate copy, just for this cleaning effort).

    I am now adding all the code subroutines and functions, one group at a time, pretty much in the order they appear in the main program.

    So far Excel VBA is taking them, but I cannot compile fully (with the compile button), until all the subroutines and functions are in there, because the compiler stops at and flags the still-missing ones.  Nevertheless, I am checking that I don't have any "red" code and so forth, and I am saving along the way.

    I have a sinking feeling that at some point Excel will crash when I try to add any more code to the worksheet module, because I pretty-much suspect that there is a memory limitation on the number of lines of code, which is undocumented.  I am calculating dimensions for about 100 dies in a setup in the program, so the number of subroutines and code lines is huge.

    By the way, I am not getting any email notifications of your replies, and I checked my settings.  Have you encountered that problem on the forum before?

    Thursday, July 22, 2010 10:54 PM
  • Hi forger

    Mmmm. Have you considered adding additional modules to your project? All your code doesn't need to be a single Worksheet or Workbook module, I wouldn't think? That would only be absolutely required for events on that worksheet (or the workbook).

    In the VBA Editor, Insert/Module. Then move some of the subroutines and such into that. You shouldn't need to change any of the calls to subroutines - VBA should find anything that's in the same project, in a "plain" module.


    Cindy Meister, VSTO/Word MVP
    Friday, July 23, 2010 9:28 AM
  • Hi Cindy,

    Thanks for your reply.

    I found the following link by Googling, which seems to confirm your opinion:

    http://www.excelforum.com/excel-programming/669743-excel-2003-maximum-amount-of-vba-code-in-a-worksheet.html

    The sublink provided more details:

    http://www.dailydoseofexcel.com/archives/2004/10/13/module-size-limits/

    I guess I will have to break up my code into separate standard modules.  I knew about the 64K procedure limit, because Excel VBA flagged that in the past (but didn't crash), but I never heard of a module size limit.

    I hate to bother with this, when I don't have any specific error message (just an unpredictable crash), and no supporting documentation from Microsoft.  I don't want to do all that work, and then find that it crashes anyway.  I know in particular that I named the same utility subroutine differently on the main worksheet and in the one standard module I created (where I had several variations of it in the module (_1, _2, etc.), hence the new name for the vanilla variation).  I guess I can keep the same name I had on the worksheet module in the additional standard module I will add, or else I can keep that one utility subroutine on the worksheet module - just thinking out loud here.

    I wish Microsoft would at least support their crashes.

    Friday, July 23, 2010 3:03 PM
  • <<I wish Microsoft would at least support their crashes.>>

    Mmm, yes, better documentation about limits would help.

    There are some in Word that are "cumulative" and can be a real bear to get to the bottom of. Bookmarks, I think, is the one that comes to mind. There's a maximum amount of storage space for bookmark names. But all the documentation tells you is a maximum number of characters / name and maximum number of bookmarks. They don't tell you that maximum number of bookmarks is lower if all of them have long names...

    I suppose the reasoning is that "no one would ever exceed this limit". But of course someone always does!


    Cindy Meister, VSTO/Word MVP
    Friday, July 23, 2010 4:55 PM
  • Hi Cindy,

    I decided to check our old Excel Programming forum (listed below), that you pointed me to, to see if anything was recorded there about this issue:

    http://groups.google.com/group/microsoft.public.excel.programming/topics?lnk=sg

    I found that other people there had encountered this problem as well.  Here is the specific link:

    http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/8cab56225e164f83/d8aec668f1196f14?lnk=gst&q=code+limit#d8aec668f1196f14

    I don't understand why Microsoft didn't just transfer everything to the new forum when they decided to start a new one?  For that matter, why didn't they put a redirect link on the old, discontinued microsoft excel programming forum link?  This is customarily done when a web page is moved.

    Monday, July 26, 2010 3:48 PM
  • Hi forger

    Glad you found some information :-)

    The old thing wasn't a forum, it was a newsgroup with a web interface. I agree, it would have been helpful if they'd at least put a message up in the old interface with a link to the new one.

    I suppose there would have been a way to programmatically move the message store to a forum. Whether this would have been useful (and worth the cost, as it could not have simply been "copied" over - different technology) is another question, entirely. There was a time limit on the MSFT newsgroup servers, after which messages were deleted. The content over the last fifteen years was never archived by Microsoft, only (as far as I know) by Google. So it was always more useful to search Google Groups for background information. The newsgroups were only good for a couple of months back...


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by Bessie Zhao Wednesday, July 28, 2010 9:03 AM
    Monday, July 26, 2010 6:49 PM
  • Hi Cindy,

    For anyone who is interested, I finally got my workbook recompiled with the code broken down into more modules.  I just got back to working on this, as some CAD projects had come up, which took precedence.

    Belatedly, I found the following item in Excel VBA Help:

    Module too large

    A module contains code within the project. This error has the following cause and solution:

    There is too much code in the module.

    Create a new module and move some of the procedures from this module to the new one. If the current module contains module-level declarations of data that must be visible to the procedures in the new module, declare that data as Public.

    This Help item would have been much more helpful if I had actually gotten the above error message, rather than Excel just crashing.  And the crash-submission dialog should have suggested this cause, if this was a known limitation.

    Wednesday, August 11, 2010 11:23 PM