How to automatically stop a VBA macro?

Question

I know that you can manually stop the VBA macro being executed using Ctrl + Break , but is there a way to automatically stop the code if a certain condition is met? exit function / exit sub do not work because they only complete the method that they call internally.

For instance,

 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 into function and use return codes to find out if this method succeeds, but is there an easier way to do this?

Answer 1

You can raise your own custom error with err.raise . This is similar to your example, but a bit more powerful in that it is an actual error that will stop code execution, even if it applies to a nested call.

For instance,

 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 string err.raise will send the control to the exitCode: label, although it was called outside of a . You can use any conditions to check whether this custom error should be selected.

Learn more about 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

Answer 2

I believe that you want this to again cause an error in the error handling code. Try something like this (not verified):

 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 into the error handler, even if there are no errors.

Your first error handling does not detect actual errors that may occur at runtime other than what you are testing. In addition, there is no convenient way to notify the calling function that something went wrong if you did not add checks to all calling procedures.

Please note that it is usually considered a poor design, although it is used only for flow control to increase the error:

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