vba 使用宏打开受信任的文档或启用宏时 Excel 崩溃

Excel Crashes When Opening Trusted Document With Macros Or Enabling Macros

提问by puzzlepiece87

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?

正如标题所说，我无法使用宏打开受信任的文档 - Excel 立即崩溃。制作文档的副本允许它打开（因为副本不受信任），我可以在 VB 编辑器中检查宏，但启用宏会导致另一次崩溃。为什么会发生这种情况，我可以做些什么来解决它？

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.

我今天遇到了类似的情况，在崩溃前也会显示“找不到项目或库”对话框，即使实际上没有丢失任何项目或库。

回答by puzzlepiece87

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):

这是由带有编译错误的宏引起的。要修复它，请使用以下步骤（全部归功于Ozgrid 的 alulla，为了清晰起见，我对其方向进行了轻微调整）：

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

1. 打开一个新的 Excel 工作簿，而不是崩溃的工作簿
2. 转到开发人员选项卡，然后单击功能区代码部分中的“宏安全性”
3. 单击标题为“通过通知禁用所有宏”的项目符号选项
4. 转到左侧的受信任位置选项卡，然后选中底部的“禁用所有受信任位置”框
5. 转到受信任的文档并选中“禁用所有受信任的文档”框
6. 单击“确定”并关闭新工作簿
7. 打开崩溃的 Excel 文件
8. 不要单击“启用宏” - 而是转到“开发人员”选项卡并打开 Visual Basic 编辑器
9. 在 VB Editor 中，保存项目，然后单击 Debug > Compile VBAProject
10. 更正发生的任何编译错误
11. 再次保存 VB 项目，然后保存 Excel 工作簿
12. 还原您在步骤 3-5 中对宏安全性所做的任何更改
13. 关闭 Excel，重新打开之前崩溃的工作簿，然后启用宏

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.

我今天体验到，这也可以解决“找不到项目或库”崩溃，其中实际上没有引用丢失，并且 Excel 在您甚至可以首先进入引用对话框之前崩溃。

回答by Andreas Dietrich

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).

（如果您不关心这个，请参阅下面的解决方案:)）
对我们来说，它发生在在Excel 2010、2016 和 2019 中成功编译和运行的代码中。所以它一定是一些非常糟糕的编译代码处理错误。
我们的问题发生在确定的、始终可重现的代码位置。当调试/检查变量时，会发生奇怪的事情（显示的其他对象超出预期，excel 元素的可视部分完全变白/损坏）……好像它在自动消失之前缓慢内部崩溃（也从任务管理器中）（有时没有立即自动重启对话框）。

Solution (save with uncompiled code)

解决方案（用未编译的代码保存）

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

我们最后的6 到 8 步解决方案非常简单，因为它似乎是已保存并重新打开的已编译宏代码工作簿的运行时错误：

1. renamethe workbook file, e.g. foo.xlsmto foo2.xlsm(untrusted name/file)
2. openfoo2.xlsmand do not enable macros
3. open VBE editor (ALT+F11)
4. (once/first timeper file, e.g. foo2.xlsm):
   insert some CrashFixmodule
   • (right click project => insert module)

5. write or updatesome only-existing commentline 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 savefoo2.xlsm
7. rename backfoo2.xlsmto 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

1. 重命名工作簿文件，例如foo.xlsm为foo2.xlsm（不受信任的名称/文件）
2. 打开foo2.xlsm并且不启用宏
3. 打开 VBE 编辑器（ALT+F11）
4. （每个文件一次/第一次，例如foo2.xlsm）：
   插入一些CrashFix模块
   • （右键项目 => 插入模块）

5. 在里面写或更新一些唯一存在的注释行，例如

   'excel crash fix from https://stackoverflow.com/a/59876944/1915920: 2020-01-23 10:00
   

   • 如果已经存在：只需更改那里的任何内容，例如更改为当前时间
   • （=> 代码，虽然只在注释级别更改，将在内部标记为未编译，并将在下次打开时使用启用的宏重新编译）
6. 关闭并保存foo2.xlsm
7. 重命名回foo2.xlsm至foo.xlsm
8. 可选：在文件级写保护文件
   • 可能仅在更新不频繁时才有用
   • 防止它被编译后的代码意外保存，导致下次再次打开时崩溃

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)

对于使用该文件的其他人（例如在网络共享环境中），它现在应该在没有任何启用宏的对话框的情况下打开。（对于您自己的环境，它可能会再次询问，因为 Excel 以某种方式“知道”它在您的系统上被重命名了）