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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With