中断/中止 VBA 循环

Interrupt/Abort a VBA-Loop

提问by poeschlorn

In VBA on Excel, I have a loop over several thousands of cells, which takes some minutes.

在 Excel 上的 VBA 中，我对数千个单元格进行了循环，这需要几分钟时间。

Is it possible to abort a long term loop (if so, how) / can I build a button or something like that to interrupt this loop manually?

是否可以中止长期循环（如果可以，如何中止）/我可以构建一个按钮或类似的东西来手动中断这个循环吗？

Building a button and overlaying it with a macro is not a problem, only the code itself.

构建一个按钮并用宏覆盖它不是问题，只是代码本身。

回答by GSerg

When Excel is busy executing your macro, it won't respond to a button.
You have three options here:

当 Excel 忙于执行宏时，它不会响应按钮。
您在这里有三个选择：

1. Use Ctrl+Breakkeys (as apposed to a button)
2. Make your macro much faster (maybe setting Application.ScreenUpdatingto Falsewill help)
3. Make your macro much slower by inserting a DoEventsin the inner loop. This way, Excel will resond to buttons in the meantime. The macro this button would trigger would just set a global variable to True(obviously, your inner loop should check this variable on each iteration, and exit if it's True).

1. 使用Ctrl+Break键（与按钮相对应）
2. 使您的宏更快（也许设置Application.ScreenUpdating为 False会有所帮助）
3. 通过DoEvents在内部循环中插入 a 使您的宏变慢 。这样，Excel 将同时响应按钮。此按钮将触发的宏只是将全局变量设置为True（显然，您的内部循环应在每次迭代时检查此变量，如果是 则退出True）。

回答by shahkalpesh

Application.EnableCancelKeycould be the thing, you are looking for.
See the example code on the linked url, on passing the control to error handler when user presses escapes.

Application.EnableCancelKey可能是你正在寻找的东西。
请参阅链接 url 上的示例代码，在用户按下转义键时将控件传递给错误处理程序。

回答by evenprime

In addition to other answers,

除了其他答案，

Lets say you want to break the code and still perform certain actions in your code, You can do so by capturing the interrupt and handling it

假设您想破坏代码并仍然在代码中执行某些操作，您可以通过捕获中断并处理它来实现

You can use Err.Numberto capture the error number

您可以使用Err.Number来捕获错误号

Specifically, Err.Number = 18 refers to user breaks

具体来说，Err.Number = 18 指的是用户中断

Sub handleError()
On Error GoTo MyErrorHandler
 Application.EnableCancelKey = xlErrorHandler

 'Just an example to show something is running
 For i = 0 To 900000
 Range("A1") = i
 Next i

MyErrorHandler:
If Err.Number = 18 Then '18 =User interrupt
     MsgBox " You clicked Ctrl + Break "
     Exit Sub

End If
End Sub

回答by Kevin Whitefoot

• Add a call to DoEvents inside the loop.
• Add a public boolean module level flag variable
• Add a button to the UI
• In the event handler for the button set the flag true

• in the loop check the variable, if it is true abort the loop.

  I've used this in an Excel userform but not with buttons on a worksheet.

• 在循环内添加对 DoEvents 的调用。
• 添加公共布尔模块级别标志变量
• 向 UI 添加按钮
• 在按钮的事件处理程序中将标志设置为 true

• 在循环中检查变量，如果为真则中止循环。

  我在 Excel 用户表单中使用过它，但没有在工作表上使用过按钮。

回答by jainashish

No, a button won't stop the running macro. It's a developer thing and control shouldn't be in user's hands by default. You can try the below combination of keys to stop the macro.

不，按钮不会停止正在运行的宏。这是开发人员的事情，默认情况下控制权不应该掌握在用户手中。您可以尝试以下组合键来停止宏。

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

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

Sometimes, the right set of keys (Pause, Breakor ScrLk)are not available on the keyboard (mostly happens with laptop users) and pressing Esc2, 3 or multiple times doesn't halt the macro too.

有时，正确的键组（Pause，Break或ScrLk)在键盘上不可用（大多数发生在笔记本电脑用户身上）并且按Esc2、3 或多次也不会停止宏。

I got stuck too and eventually found the solution in accessibility feature of Windows after which I tried all the researched options and above 3 of them worked for me in 3 different scenarios.

我也被卡住了，最终在 Windows 的辅助功能中找到了解决方案，之后我尝试了所有研究过的选项，其中 3 个以上的选项在 3 个不同的场景中对我有用。

Step #01: If your keyboard does not have a specific key, please do not worry and open the 'OnScreen Keyboard' from Windows Utilities by pressing Win+ U.

步骤 #01：如果您的键盘没有特定键，请不要担心，然后按Win+从 Windows 实用程序打开“屏幕键盘” U。

Step #02: Now, try any of the above option and of them will definitely work depending on your system architecture i.e. OS and Office version

步骤 #02：现在，尝试上述任何选项，根据您的系统架构，即操作系统和 Office 版本，它们肯定会起作用

You will be put into break mode using the above key combinations as the macro suspends execution immediately finishing the current task. For eg., if it is pulling the data from the web then it will halt immediately before executing any next command but after pulling the data, following which one can press F5or F8to continue the debugging.

您将使用上述组合键进入中断模式，因为宏会立即暂停执行以完成当前任务。例如，如果它正在从网络中拉取数据，那么它会在执行任何下一个命令之前立即停止，但在拉取数据后，可以按F5或F8继续调试。