Excel Crashes When Opening Trusted Document With Macros Or Enabling Macros

Question

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.

Answer 1

This is caused by a macro with a compile error. To fix it, use the following steps (all credit to alulla at Ozgrid, whose directions I've lightly adapted for clarity):

1. Open a new Excel workbook, not your crashing workbook
2. Go to the Developer Tab and click “Macro Security” in the Code section of the Ribbon
3. Click the bullet option entitled “Disable all macros with notification”
4. Go to the Trusted Locations tab on the left and check the “Disable all Trusted Locations” box at the bottom
5. Go to Trusted Documents and check the “Disable all Trusted Documents” box
6. Click OK and close the new workbook
7. Open your Excel file that was crashing
8. Do not click "Enable Macros" - instead go to the Developer Tab and open the Visual Basic Editor
9. In the VB Editor, save the project and then click Debug > Compile VBAProject
10. Correct any compile errors that occur
11. Save the VB project again and then save the Excel workbook
12. Revert any changes you made to Macro Security in steps 3-5
13. Close Excel, re-open the previously crashing workbook, and Enable Macros

I experienced today that this can also resolve "Can't find project or library" crashes where no reference is actually missing and Excel crashes before you can even get to the reference dialog in the first place.