VBA - Error Handling

Question

Hello everyone,

I'm trying to set up some error handling for a tool I've made. I've got loads of macros going on with this one so putting error handling into each macro would be impractical.

Question:

How can I add 1 error handling station that will handle all macro errors on the workbook. My plan is to place a string into each macro in order to record the macro name (eg, Sub Macro1() will have a string inside called SubName = "Macro1"). The error handler will say an error was found, will perform a standard procedure and then notify me by email.

What I want to achieve:

Email me when a error has been found with the "Microsoft Excel Objects/UserForm/Class Module/Modules" name, sub name, the error line number within that sub, error number and description.

Any ideas?

Thanks,

Daniel

Answer 1

It probably don't even have to be a function - that's just how Craig implementeted it...

You could also just make a normal Sub/macro in a normal code module with something like this:

      Sub MyErrorRoutine ( ErrorCode, SubName , SendMail )

            ...Your error macro here...

      End Sub

And, then call this macro with something like:

      Call MyErrorRoutine ( Err.Number , "MyMacro" , True )

In each macro, your would have to have something like:

      On Error GoTo ErrorHandler:

...at the begining of the Sub/macro.

And, your error handler could then be just that code line:

      ErrorHandler:

      Call MyErrorRoutine ( Err.Number , "MyMacro" , True )

...at the end of the Sub/macro (maybe after an 'Exit Sub')

Answer 2

That's perfect. I thought this would be how I'd have to handle it. This way I can write a conditions for all error codes that are available (or at least most of them!)

Thanks,

Daniel

Answer 3

Hi Dan,

I understand what you're trying to say. I've never made a UDF before. Would you be able to make a small example for me? I'm not even sure how I would call the function on error. Where would I store the function as well (ThisWorkbook, Module, etc)?

Thanks,

Daniel

Answer 4

Craig Hatmaker had a pretty nifty routine for doing this on his website, but I just visited his website to provide you with the link, but for some reason, he has removed that article, including the download, from his website.

But, basically, here's how it worked:

You create an UDF, that does everything, that you want your error handling routine to to.

In you macros 'On Error ...' statement, you make sure, that this UDF is called, with the arguments needed (like, the name of the Sub calling the UDF, shoud a mail be set, display message to the use, etc.)

That way you can have all your macros call the same error handling. and use the arguments of the function to control what to do.

Pretty nifty, and I really don't know why he has removed it from his website again.

But, now, you at least know the idea behind, how to do it - so, create your own UDF to do the stuff, you want, and let your 'On Error ...' statement point to the UDF.