How to Exit Sub if called function has error?

[Rerock]

Hi, below is one example of several macros I'm using in a workbook. Instead of several macros all having duplicate lines of code, I've decided to use called functions; it makes my life much easier for updating things...

However, one thing I've encountered, is if there is an error in one of the called functions, the macro will continue to run to the macro completion. How can I make the code below stop itself dead in it's tracks if one of the called functions results in an error?

Also, each called function has the same errorhandling as this macro, and it does work. Thus I will know what line in which called function the error came from. I would just like the TEST_MACRO to stop running once an error is encountered.

If at all possible, I would like to accomplish this while retaining the error reporting for TEST_MACRO as well.

Thanks in advance for any help!

Please Login or Register  to view this content.

[TMS]

I usually define a Public Global boolean variable, say, bError. Set it to False before calling your subroutines/functions. In the error handler of each subroutine/function, set it to True (if the error is terminal). After each subroutine/function call, test the variable. Something like: If bError Then Exit Sub ... or If bError Then GoTo lblMainErrorHandler


Regards, TMS

[Rerock]

Hi TMS, thanks for your reply! I comprehend what you're saying to do, but I'm not entirely positive I know how to execute it. I know enough about VBA to be dangerous, but far from what I'd considered knowledgeable...

Something like this perhaps???

Please Login or Register  to view this content.

[TMS]

Close

Please Login or Register  to view this content.

[Rerock]

Aah, okay!

So does the "Public bError As Boolean" go above each macro that has called functions, or simply in the module somewhere?

[TMS]

"Public bError As Boolean" appears once, and once only, before any of your procedures. or it could go in a separate module.

Because it is defined outside all the functions and subroutines, its scope is Global and can be seen and modified by any of the routines.


Regards, TMS

[Rerock]

Got it; thanks for the clarification. One more question, regarding the "bError = True" statement in the called functions, so I put that once at the top of each function?

Thanks!

[TMS]

regarding the "bError = True" statement in the called functions, so I put that once at the top of each function?

No, that goes in the error handler in the subroutine. So, if you get an error in the subroutine, it does whatever it needs to do to tidy up, display an error message, whatever AND sets the bError flag to True so that it can be picked up in the main routine.

Regards, TMS

[Rerock]

So something like this?

Please Login or Register  to view this content.

[TMS]

Yep, that looks about right.

[Rerock]

Great! I'll go doctor up a few macros and functions, run some tests, then follow up with my results.

With any luck, my next post will be reveling in your awesomeness!

Thanks for the help so far!

[TMS]

Actually, if you have a common error handler, you could set the flag in that. No need to do it in each individual error handler.

[Rerock]

Thanks TMS! That worked exactly like you said it would!

As a bonus, I also learned that I can add other steps/ tasks in the ErrorHandler; very nice!

I'll mark this as solved.

[TMS]

You're welcome. Thanks for the rep.

[Rerock]

Right now the error handler at the bottom of each macro & function calls a macro that generates a pop up window with the details of the error, then automatically generates me an email with the same information, along with a hyperlink of the filepath.

I appreciate the suggestion, and will keep that in mind if/ when I go through and revise how everything fits together. I have this thread bookmarked for future reference.

Thanks!