如何停止运行 VBA 代码？

How to stop VBA code running?

提问by Kirill Leontev

Say I have a button embedded into my spreadsheet that launches some VBA function.

假设我的电子表格中嵌入了一个按钮，可以启动一些 VBA 函数。

Private Sub CommandButton1_Click()
    SomeVBASub
End Sub

Private Sub SomeVBASub
    DoStuff
    DoAnotherStuff
    AndFinallyDothis
End Sub

I'd like to have an opportunity to have some sort of a "cancel" button that would stop SomeVBASubexecution at an arbitrary moment, and I'm not into involving Ctrl+Breakhere, 'cause I'd like to do it silently.

我想有机会拥有某种“取消”按钮，可以SomeVBASub在任意时刻停止执行，我不Ctrl+Break打算在这里参与，因为我想默默地做。

I guess this should be quite common issue, any ideas?

我想这应该是很常见的问题，有什么想法吗？

Thanks.

谢谢。

回答by Jamie Treworgy

Add another button called "CancelButton" that sets a flag, and then check for that flag.

添加另一个名为“CancelButton”的按钮来设置一个标志，然后检查该标志。

If you have long loops in the "stuff" then check for it there too and exit if it's set. Use DoEvents inside long loops to ensure that the UI works.

如果“东西”中有很长的循环，那么也在那里检查它，如果设置了就退出。在长循环内使用 DoEvents 以确保 UI 正常工作。

Bool Cancel
Private Sub CancelButton_OnClick()
    Cancel=True
End Sub
...
Private Sub SomeVBASub
    Cancel=False
    DoStuff
    If Cancel Then Exit Sub
    DoAnotherStuff
    If Cancel Then Exit Sub
    AndFinallyDothis
End Sub

回答by Faheem

How about Application.EnableCancelKey - Use the Esc button

Application.EnableCancelKey 怎么样 - 使用 Esc 按钮

On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler
MsgBox "This may take a long time: press ESC to cancel"
For x = 1 To 1000000    ' Do something 1,000,000 times (long!)
    ' do something here
Next x

handleCancel:
If Err = 18 Then
    MsgBox "You cancelled"
End If

Snippet from http://msdn.microsoft.com/en-us/library/aa214566(office.11).aspx

摘自http://msdn.microsoft.com/en-us/library/aa214566(office.11​​).aspx

回答by simpLE MAn

Or, if you want to avoidthe use of a global variableyou could use the rarely used .Tagproperty of the userform:

或者，如果您想避免使用全局变量，您可以使用用户.Tag表单的很少使用的属性：

Private Sub CommandButton1_Click()
    Me.CommandButton1.Enabled = False 'Disabling button so user cannot push it
                                      'multiple times
    Me.CommandButton1.caption = "Wait..." 'Jamie's suggestion
    Me.Tag = "Cancel"
End Sub

Private Sub SomeVBASub
    If LCase(UserForm1.Tag) = "cancel" Then
        GoTo StopProcess
    Else
        'DoStuff
    End If

Exit Sub
StopProcess:
    'Here you can do some steps to be able to cancel process adequately
    'i.e. setting collections to "Nothing" deleting some files...
End Sub

回答by Kenson Gurney

I do this a lot. A lot. :-)

我经常这样做。很多。:-)

I have got used to using "DoEvents" more often, but still tend to set things running without really double checking a sure stop method.

我已经习惯于更频繁地使用“DoEvents”，但仍然倾向于在没有真正仔细检查确定的停止方法的情况下设置运行。

Then, today, having done it again, I thought, "Well just wait for the end in 3 hours", and started paddling around in the ribbon. Earlier, I had noticed in the "View" section of the Ribbon a "Macros" pull down, and thought I have a look to see if I could see my interminable Macro running....

然后，今天，又做了一遍，我想，“好吧，等 3 小时后结束吧”，然后开始在丝带上划船。早些时候，我在功能区的“视图”部分注意到一个“宏”下拉，并想看看我是否可以看到我的无休止的宏正在运行......

I now realise you can also get this up using Alt-F8.

我现在意识到你也可以使用 Alt-F8 来解决这个问题。

Then I thought, well what if I "Step into" a different Macro, would that rescue me? It did :-) It also works if you step into your running Macro (but you still lose where you're upto), unless you are a very lazy programmer like me and declare lots of "Global" variables, in which case the Global data is retained :-)

然后我想，如果我“步入”一个不同的宏，那会拯救我吗？它确实:-) 如果您进入正在运行的宏，它也可以工作（但您仍然会迷失自己的位置），除非您像我一样是一个非常懒惰的程序员并声明了许多“全局”变量，在这种情况下，全局变量数据被保留:-)

K

钾

回答by Paul Clint

~ For those using custom input box

~ 对于那些使用自定义输入框的人

Private Sub CommandButton1_Click()

DoCmd.Close acForm, Me.Name
End

End Sub

回答by PGSystemTester

This is an old post, but given the title of this question, the ENDoption should be described in more detail. This can be used to stop ALL PROCEDURES(not just the subroutine running). It can also be used within a function to stop other Subroutines (which I find useful for some add-ins I work with).

这是一篇旧帖子，但鉴于此问题的标题，应该更详细地描述END选项。这可用于停止所有程序（不仅仅是正在运行的子程序）。它也可以在函数中使用以停止其他子例程（我发现这对我使用的某些加载项很有用）。

As Microsoft states:

正如微软所说：

Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement, and to clear variables*. I noticed that the END method is not described in much detail. This can be used to stop ALL PROCEDURES (not just the subroutine running).

立即终止执行。本身从不需要，但可以放置在过程中的任何位置以结束代码执行、关闭用 Open 语句打开的文件以及清除变量*。我注意到 END 方法没有详细描述。这可用于停止所有程序（不仅仅是正在运行的子程序）。

Here is an illustrative example:

这是一个说明性示例：

Sub RunSomeMacros()

    Call FirstPart
    Call SecondPart

    'the below code will not be executed if user clicks yes during SecondPart.
    Call ThirdPart
    MsgBox "All of the macros have been run."

End Sub

Private Sub FirstPart()
    MsgBox "This is the first macro"

End Sub

Private Sub SecondPart()
    Dim answer As Long
    answer = MsgBox("Do you want to stop the macros?", vbYesNo)

    If answer = vbYes Then
        'Stops All macros!
        End
    End If

    MsgBox "You clicked ""NO"" so the macros are still rolling..."
End Sub

Private Sub ThirdPart()
    MsgBox "Final Macro was run."
End Sub

回答by Beth

what jamietre said, but

杰米特说了什么，但是

Private Sub SomeVBASub
    Cancel=False
    DoStuff
    If not Cancel Then DoAnotherStuff
    If not Cancel Then AndFinallyDothis
End Sub