Excel VBA Global error handling

Question

Is there a way to do global error handling?

Can I put some code in the Workbook code that will catch any errors that occur within all modules?

I could put the same error handler in each module but I'm looking for something more general.

I ask because I have sheet names that are stored as global variables like this Sheets(QuoteName). If there is an error then these global variables are lost. I have a macro that will rename the global variables but I put this within Workbook_BeforeSave.

I want it to go to the global error handler and rename the global variable if I get a Subscript out of range error for Sheets(QuoteName)

Answer 1

As Sid already mentioned in the comment, there is no central error handler.

Best practice is to have a central error handling routine that gets called from the local error handlers. Take a look at the great MZ-Tools: it has the possibility to define a default error handler at the press of a button (Ctrl-E). You can customize this error handler - and it can also contain module and/or sub name!

Additionally, check out this post at Daily Dose of Excel. It is Dick Kusleika's OO version of the error handler proposed in this book (which I can highly recommend).