Excel VBA 应用程序自发停止并显示消息“代码执行已停止”

Excel VBA App stops spontaneously with message "Code execution has been halted"

提问by Phil Whittington

From what I can see on the web, this is a fairly common complaint, but answers seem to be rarer. The problem is this:

从我在网上看到的情况来看，这是一个相当普遍的投诉，但答案似乎很少见。问题是这样的：

We have a number of Excel VBA apps which work perfectly on a number of users' machines. However on one machine they stop on certain lines of code. It is always the same lines, but those lines seem to have nothing in common with one another.

我们有许多 Excel VBA 应用程序，它们可以在许多用户的机器上完美运行。然而，在一台机器上，它们停在某些代码行上。它总是相同的线条，但这些线条似乎彼此之间没有任何共同之处。

If you press F5 (run) after the halt, the app continues, so it's almost like a break point has been added. We've tried selecting 'remove all breaks' from the menu and even adding a break and removing it again.

如果您在暂停后按 F5（运行），应用程序将继续运行，因此几乎就像添加了一个断点。我们尝试从菜单中选择“删除所有中断”，甚至添加中断并再次删除它。

We've had this issue with single apps before and we've 'bodged' it by cutting code out of modules, compiling and then pasting it back in etc.

我们以前在单个应用程序中遇到过这个问题，我们通过从模块中剪切代码、编译然后将其粘贴回等来“解决”它。

The problem now seems to relate to Excel itself rather than a single .xls, so we're a little unsure how to manage this.

现在的问题似乎与 Excel 本身有关，而不是与单个 .xls 相关，因此我们有点不确定如何管理它。

Any help would be gratefully received :)

任何帮助将不胜感激:)

Thanks,

谢谢，

Philip Whittington

菲利普·惠廷顿

回答by Stan

I have found a 2nd solution.

我找到了第二个解决方案。

1. Press "Debug" button in the popup.
2. Press Ctrl+Pause|Breaktwice.
3. Hit the play button to continue.
4. Save the file after completion.

1. 在弹出窗口中按“调试”按钮。
2. 按Ctrl+Pause|Break两次。
3. 点击播放按钮继续。
4. 完成后保存文件。

Hope this helps someone.

希望这可以帮助某人。

回答by user2052260

This problem comes from a strange quirk within Office/Windows.

这个问题来自 Office/Windows 中的一个奇怪的怪癖。

After developing the same piece of VBA code and running it hundreds of times (literally) over the last couple days I ran into this problem just now. The only thing that has been different is that just prior to experiencing this perplexing problem I accidentally ended the execution of the VBA code with an unorthodox method.

在过去几天开发了相同的 VBA 代码并运行了数百次（字面意思）之后，我刚刚遇到了这个问题。唯一不同的是，就在遇到这个令人困惑的问题之前，我不小心用一种非正统的方法结束了 VBA 代码的执行。

I cleaned out all temp files, rebooted, etc... When I ran the code again after all of this I still got the issue - before I entered the first loop. It makes sense that "press "Debug" button in the popup, then press twice [Ctrl+Break] and after this can continue without stops"because something in the combination of Office/Windows has not released the execution. It is stuck.

我清除了所有临时文件，重新启动等......当我在所有这些之后再次运行代码时，我仍然遇到问题 - 在我进入第一个循环之前。“按弹出窗口中的“调试”按钮，然后按两次 [Ctrl+Break]，然后可以不间断地继续”是有道理的，因为 Office/Windows 组合中的某些内容尚未释放执行。它被卡住了。

The redundant Ctrl+Break action probably resolves the lingering execution.

多余的 Ctrl+Break 操作可能会解决延迟执行的问题。

回答by Stan

One solution is here:

一种解决方案在这里：

The solution for this problem is to add the line of code “Application.EnableCancelKey = xlDisabled” in the first line of your macro.. This will fix the problem and you will be able to execute the macro successfully without getting the error message “Code execution has been interrupted”.

这个问题的解决方法是在你的宏的第一行添加一行代码“Application.EnableCancelKey = xlDisabled”。这将解决这个问题，你将能够成功执行宏而不会收到错误消息“Code执行已中断”。

But, after I inserted this line of code, I was not able to use Ctrl+Break any more. So it works but not greatly.

但是，在插入这行代码后，我无法再使用 Ctrl+Break。所以它有效但不是很大。

回答by James

I found hitting ctrl+break while the macro wasn't running fixed the problem.

我发现在宏未运行时按 ctrl+break 可以解决问题。

回答by Charles Williams

I would try the usual remedial things: - Run Rob Bovey's VBA Code Cleaner on your VBA Code - remove all addins on the users PC, particularly COM and .NET addins - Delete all the users .EXD files (MSoft Update incompatibilities) - Run Excel Detect & Repair on the users system - check the size of the user's .xlb file (should be 20-30K) - Reboot then delete all the users Temp files

我会尝试通常的补救措施： - 在您的 VBA 代码上运行 Rob Bovey 的 VBA 代码清理器 - 删除用户 PC 上的所有插件，尤其是 COM 和 .NET 插件 - 删除所有用户 .EXD 文件（MSoft 更新不兼容） - 运行 Excel在用户系统上检测和修复 - 检查用户的 .xlb 文件的大小（应该是 20-30K） - 重新启动然后删除所有用户的临时文件

回答by Phil Whittington

Thanks to everyone for their input. This problem got solved by choosing REPAIR in Control Panel. I guess this explicitly re-registers some of Office's native COM components and does stuff that REINSTALL doesn't. I expect the latter just goes through a checklist and sometimes accepts what's there if it's already installed, maybe. I then had a separate issue with registering my own .NET dll for COM interop on the user's machine (despite this also working on other machines) though I think this was my error rather than Microsoft. Thanks again, I really appreciate it.

感谢大家的投入。通过在控制面板中选择 REPAIR 解决了这个问题。我猜这会显式地重新注册一些 Office 的本机 COM 组件，并执行 REINSTALL 没有的操作。我希望后者只是通过一个清单，有时如果它已经安装，有时会接受那里的东西，也许。然后，我在为用户机器上的 COM 互操作注册我自己的 .NET dll 时遇到了一个单独的问题（尽管这也适用于其他机器），尽管我认为这是我的错误而不是 Microsoft。再次感谢，我真的很感激。

回答by bazinac

I have came across this issue few times during the development of one complex Excel VBA app. Sometimes Excel started to break VBA object quite randomly. And the only remedy was to reboot machine. After reboot, Excel usually started to act normally.

在开发一个复杂的 Excel VBA 应用程序的过程中，我多次遇到过这个问题。有时 Excel 开始随机破坏 VBA 对象。唯一的补救办法是重新启动机器。重新启动后，Excel 通常开始正常运行。

Soon I have found out that possible solution to this issue is to hit CTRL+Break once when macro is NOT running. Maybe this can help to you too.

很快我发现这个问题的可能解决方案是在宏未运行时按一次 CTRL+Break。也许这对你也有帮助。

回答by Carlito

I have had this problem also using excel 2007 with a foobar.xlsm (macro enabled ) workbook which would get the "Code execution has been interrupted" by simply trying to close the workbook on the red X in the right corner with no macros running at all, or any "initialize" form, workbook, or workheet macros either. The options I got were "End" or "Continue", Debug was always greyed out. I did as a previous poster suggested Control Panel->Programs and Features-> right click "Microsoft Office Proffesional 2007" (in my case) ->change->repair.

我也遇到了这个问题，使用 foobar.xlsm（启用宏）工作簿的 excel 2007 会得到“代码执行已被中断”，只需尝试关闭右上角的红色 X 上的工作簿，没有运行宏所有或任何“初始化”表单、工作簿或工作表宏。我得到的选项是“结束”或“继续”，调试总是灰色的。我按照以前的海报建议控制面板-> 程序和功能-> 右键单击​​“Microsoft Office Proffesional 2007”（在我的情况下）-> 更改-> 修复。

This resolved the problem for me. I might add this happened soon after a MS update and I also found an addin in Excel called "Team Foundation" from Microsoft which I certainly didnt install voluntarily

这为我解决了问题。我可能会添加这发生在 MS 更新后不久，我还在 Excel 中找到了一个来自 Microsoft 的名为“Team Foundation”的插件，我当然不是自愿安装的

回答by jainashish

I would like to add more details to Stan'sanswer #2for below reasons:

由于以下原因，我想在Stan 的答案 #2 中添加更多详细信息：

• I faced this issue myself more than dozen times and depending on project conditions, I chose between stan's voodoo magic answer #1or #2. When I kept on facing it again, I become more inquistive that why it happens in first place.

• I'd like to add answer for Mac users too.

• There are limitations with both these possible answers:

  • if the code is protected (and you don't know password) then answer #1won't help.
  • if the code is unprotected then answer #2won't let you debug the code.

• 我自己多次遇到这个问题，根据项目条件，我在 stan 的巫毒魔法答案 #1或 #2之间进行了选择。当我再次面对它时，我变得更加好奇它为什么会首先发生。

• 我也想为 Mac 用户添加答案。

• 这两种可能的答案都有局限性：

  • 如果代码受到保护（并且您不知道密码），那么回答 #1将无济于事。
  • 如果代码不受保护，那么回答 #2将不会让您调试代码。

---

---

1. It may happen due to any of the below reasons:

   • Operating system not allocating system resources to the Excel process. (Solution:One needs to just start the operating system - success rate is very low but has known to work many times)

   • P-code is the intermediate code that was used in Visual Basic (before .NET) and hence it is still used in the VBA. It enabled a more compact executable at the expense of slower execution. Why I am talking about p-code? Because it gets corrupted sometimes between multiple executions and large files or just due to installation of the software (Excel) went corrupt somewhere. When p-code corrupts. the code execution keeps getting interrupted. Solution: In these cases, it is assumed that your code has started to corrupt and chances in future are that your Excel workbook also get corrupt giving you messages like "excel file corrupted and cannot be opened". Hence, as a quick solution, you can rely on answer #1 or answer #2 as per your requirements. However, never ignore the signs of corruption. It's better to copy your code modules in notepad, delete the modules, save & close the workbook, close the excel. Now, re-open the workbook and start creating new modules with the code copied earlier to notepad.

2. Mac users, try any of the below option and of them will definitely work depending on your system architecture i.e. OS and Office version

   • Ctrl+ Pause
   • Ctrl+ ScrLk
   • Esc+ Esc(Press twice consecutively)

1. 它可能由于以下任何原因而发生：

   • 操作系统未将系统资源分配给 Excel 进程。（解决方法：只需要启动操作系统——成功率很低，但已经知道可以工作很多次了）

   • P-code 是在 Visual Basic（.NET 之前）中使用的中间代码，因此它仍然在 VBA 中使用。它以执行速度变慢为代价实现了更紧凑的可执行文件。为什么我在谈论 p 代码？因为它有时会在多次执行和大文件之间损坏，或者只是由于安装了软件 (Excel) 在某处损坏了。当 p 代码损坏时。代码执行不断中断。解决方案：在这些情况下，假设您的代码已开始损坏，并且将来您的 Excel 工作簿也可能会损坏，并显示“excel 文件已损坏且无法打开”之类的消息。因此，作为快速解决方案，您可以根据您的要求依赖答案 #1 或答案 #2。但是，永远不要忽视腐败的迹象。最好在记事本中复制代码模块，删除模块，保存并关闭工作簿，关闭 excel。现在，重新打开工作簿并使用之前复制到记事本的代码开始创建新模块。

2. Mac 用户，请尝试以下任一选项，根据您的系统架构，即操作系统和 Office 版本，它们肯定会起作用

   • Ctrl+ Pause
   • Ctrl+ ScrLk
   • Esc+ Esc（连续按两次）

You will be put into break mode using the above key combinations as the macro suspends execution immediately finishing the current task. This is replacement of Step 2.

您将使用上述组合键进入中断模式，因为宏会立即暂停执行以完成当前任务。这是第 2 步的替换。

1. Solution:To overcome the limitation of using answer #1and answer #2, I use xlErrorHandleralong with Resumestatement in the Error Handler if the error code is 18. Then, the interrupt is sent to the running procedure as an error, trappable by an error handler set up with an On Error GoTo statement. The trappable error code is 18. The current procedure is interrupted, and the user can debug or end the procedure. Microsoftgives caution that do not use this if your error handler has resume statement else your error handler always returns to the same statement. That's exactly we want in unwanted meaningless interruptions of code execution.

3. 解决方案：为了克服使用answer #1和answer #2的限制，如果错误代码为 18 ，我在 Error Handler 中使用 xlErrorHandlerwithResume语句。然后，中断作为错误发送到正在运行的程序，可被错误捕获使用 On Error GoTo 语句设置的处理程序。可捕获的错误代码为 18。当前程序被中断，用户可以调试或结束程序。如果您的错误处理程序有 resume 语句，Microsoft警告不要使用它，否则您的错误处理程序总是返回到相同的语句。这正是我们想要的代码执行的不必要的无意义中断。

---

---

回答by blablubbb

My current reputation does not yet allow to post this as a comment. Stans solution to enter the debug mode, press twice Ctrl+Break, play on, save did solve my problem, but I have two unexpected twists:

我目前的声誉还不允许将此作为​​评论发布。Stans解决方案进入调试模式，按两次Ctrl+Break，play on，save确实解决了我的问题，但我有两个意想不到的曲折：

1. My project struture is password protected, so in order to get into the Debug Mode I had to first enter Developer mode, click on the project structure and enter the password.

2. My project is a template file (.xmtl). I opened the file via double click which opens it as .xml with a "1" at the end of the previous file name. I fixed the bug as by Stans instruction and saved it as that ...1.xml file. When I then opened the template again, this time as template, and wanted to apply the same bug fix to that file, the bug was gone! I did not change this file and still no bug at executing the Macro. This means to me that the bug is not actually in the file, but in a (hidden) setting in Excel.

1. 我的项目结构受密码保护，所以为了进入调试模式我必须先进入开发者模式，点击项目结构并输入密码。

2. 我的项目是一个模板文件 (.xmltl)。我通过双击打开文件，将其打开为 .xml，在前一个文件名的末尾带有“1”。我按照 Stans 指令修复了错误并将其保存为 ...1.xml 文件。当我再次打开模板时，这次是作为模板，并想对那个文件应用相同的错误修复，错误消失了！我没有更改这个文件，在执行宏时仍然没有错误。这对我来说意味着错误实际上不在文件中，而是在 Excel 中的（隐藏）设置中。