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?

Solution 1

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

Solution 2

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