How to stop VBA macro automatically?

Question

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?

Just as a general tip, `goto` is bad. Don't use it. – Andrew Oct 14 '14 at 19:38 — Andrew, Oct 14 '14 at 19:38
http://stackoverflow.com/q/46586/436282 – Andrew Oct 14 '14 at 20:03 — Andrew, Oct 14 '14 at 20:03

score 9 · Accepted Answer · edited May 08 '18 at 21:48

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

score 1 · Answer 2 · edited Aug 08 '16 at 16:06

I believe want you want to do is raise the error again in your error handling code. Try something like this (not tested):

Private Sub Test
    On Error Goto bad
    x = 0
    debug.print 1/x
    Exit Sub

    bad:
        'Clean up code
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub

When using On Error Goto, you need an Exit Sub before the label. Otherwise your code will fall through to the error handler even when there is no error.

Your first error handling doesn't catch actual errors that might occur during runtime other than what you are testing for. Also, there is not a convenient way to signal the calling function that something went wrong unless you add checks in all the calling routines.

Note that it's generally considered bad design though to raise an error only for flow control:

Private Sub Test
    If x = 0 Then
        Err.Raise 
    End If
End Sub

This is what we did for our vb6 projects. You may not need it for every function though, just the ones where you want to run clean up code. It's really up to you what you want to use. — Ceres, Oct 14 '14 at 20:29

score 0 · Answer 3 · answered Mar 14 '19 at 04:53

0

You can create Macro to perform below steps/operation and call whenever your code meets certain condition to abort the other running macros:

Step 1-- Menu bar -> Run -> Reset

Step 2-- Menu bar -> Run -> Break

answered Mar 14 '19 at 04:53

meMadhav

233
2
12

How to stop VBA macro automatically?

3 Answers3

Linked