Excel Crashes When Opening Trusted Document With Macros Or Enabling Macros

user_4691433 0
Viewed 72887

As the title says, I am unable to open a trusted document with macros - Excel crashes immediately. Making a copy of the document allows it to open (because the copy is untrusted), and I can inspect the macros in the VB Editor, but enabling macros causes another crash. Why is this happening and what can I do to fix it?

I experienced a similar situation today where a "Can't find project or library" dialog box displays before a crash as well, even though no projects or libraries are actually missing.

vba excel
2 Answers

Some Background/Findings

(see solution below if you don't care about this :) )
For us it happened in code that compiles and runs successful in Excel 2010, 2016 and 2019. So it must be some pretty bad compiled-code processing bug.
Our problem occurs at definite, always reproducible code locations. When one debugs/inspects the variables, strange things happen (other objects shown than expected, visual sections of excel elements get completely white/broken) ... as if it slowly internally crashes before it will disappear automatically (also from task manager) (sometimes without the immediate auto-restart dialog).

Solution (save with uncompiled code)

Our final 6 to 8 step solution is quite simple since it seems to be a bug with the runtime on compiled macro code workbooks that have been saved and opened anew:

  1. rename the workbook file, e.g. foo.xlsm to foo2.xlsm (untrusted name/file)
  2. open foo2.xlsm and do not enable macros
  3. open VBE editor (ALT+F11)
  4. (once/first time per file, e.g. foo2.xlsm):
    insert some CrashFix module
    • (right click project => insert module)

  5. write or update some only-existing comment line inside, e.g. 

    'excel crash fix from https://stackoverflow.com/a/59876944/1915920: 2020-01-23 10:00
    • if already existing: just change anything there, e.g. change to current time
    • (=> the code, although only changed on comment-level, will be internally marked as uncompiled and will be recompiled on next open with enabled macros)
  6. close and save foo2.xlsm
  7. rename back foo2.xlsm to foo.xlsm
  8. optional: write protect file on file-level
    • may be only useful if updates are infrequent
    • prevents it from beeing saved accidentially with compiled code causing the crash on next open again

Now you should be able to open it without crashing (since obviously the initial compile run on startup prevents the crash somehow).

For others using the file (e.g. in a network share environment) it should now open without any macro-enable dialog. (for your own env it may ask again, since Excel "knows" somehow it was renamed inbetween on your system)

user_19159200
Related