如何自动停止VBA宏？ [英] How to stop VBA macro automatically?

问题描述

我知道你可以用 Ctrl + Break 手动停止一个正在运行的VBA宏，但是如果满足某个条件，是否有任何方法让代码自动停止？ 退出函数 / exit sub 不工作，因为它们只是终止他们调用的方法。 >

例如，

  sub a 
 call b 
 msgboxa complete
 end sub 
 
 sub b 
 call c 
 msgboxb complete'这个msgbox仍然会显示在`exit sub`之后'c'
 end sub 
 
 sub c 
 msgboxenter c
 exit sub'这只会退出sub`'c'，而不是'a '或'b'
 msgboxexit c
 end sub 
 
'OUTPUT：
 
'进入c 
'b完成
'一个完整的
  

我想我可以把这些 sub 进入函数，并利用返回代码知道方法是否成功执行，但是有更简单的方法吗？

解决方案

您可以用呃提出自己的用户定义的错误r.raise 。这与你的例子相似，但是有一点更强大，因为它是一个实际的错误，将停止代码执行，即使它应用于嵌套的调用。

例如，

  sub a 
 on error goto exitCode 
 call b 
 msgbox一个完整的
 exit sub你需要这个来防止错误处理代码总是运行
 exitCode：
 msgbox代码正在退出...
'清理代码在这里
 end sub 
 
 sub b 
 call c 
 msgboxb complete
 end sub 
 
 sub c 
 msgbox 进入c
 err.raise 555，foo，发生错误
 msgbox退出c
 end sub 
 
'OUTPUT：
 
'进入c 
'代码正在退出... 
  

err.raise 行会将控件发送到 exitCode：标签，即使它在<$ c $之外调用C> A 。您可以使用任何条件来测试是否应该抛出此自定义错误。

更多关于 err 对象和VBA错误处理 -

https://msdn.microsoft.com/en-us/library/ka13cy19（v = vs90）.aspx

http://www.cpearson.com/excel/errorhandling.htm

I know you can manually stop a running VBA macro with Ctrl+Break, but is there any way to have the code stop automatically if a certain condition is met? exit function / exit sub are not working, because they are only terminating the method they are called within.

For example,

sub a
    call b
    msgbox "a complete"
end sub

sub b
    call c
    msgbox "b complete"    'this msgbox will still show after the `exit sub` in 'c'
end sub

sub c
    msgbox "entering c"
    exit sub               'this will only `exit sub` 'c', but not 'a' or 'b'
    msgbox "exiting c"
end sub

'OUTPUT:

'entering c
'b complete
'a complete

I suppose I could turn these sub's into function's and utilize return codes to know if the method executed successfully, but is there a simpler way to do this?

解决方案

You can raise your own user-defined error with err.raise. This is similar to your example, but a little more powerful in that it is an actual error that will halt code execution, even if it's applied to a nested call.

For example,

sub a
on error goto exitCode
    call b
    msgbox "a complete"
    exit sub       'you need this to prevent the error handling code from always running
exitCode:
    msgbox "code is exiting..."
    'clean up code here
end sub

sub b
    call c
    msgbox "b complete"
end sub

sub c
    msgbox "entering c"
    err.raise 555, "foo", "an error occurred"
    msgbox "exiting c"
end sub

'OUTPUT:

'entering c
'code is exiting...

The err.raise line will send the control to the exitCode: label even though it was called outside of a. You could use any conditions to test if this custom error should be thrown.

More on the err object and VBA error handling-

https://msdn.microsoft.com/en-us/library/ka13cy19(v=vs.90).aspx

http://www.cpearson.com/excel/errorhandling.htm

这篇关于如何自动停止VBA宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！