2

I am dealing with a good amount of workbooks with many procedures. The deeper I go in my project, the more I try to write functions or sub to process common tasks. First of all, as the final user will be an average Excel user, I decided to protect and centralize all my code in a personal macro workbook. It sounds to me a good idea, but maybe some of you, Excel gurus, don't agree. Then I wrote a Public Sub ErrorHandler () to deal with errors in all my procédures. I am fully aware of the Error Bubble Up. My procedure will be soemthing like this:

' All déclarations are outside
Public Sub ErrorHandler ()
Lg1 = Err.Number ' Lg1 is Long
Select Case Lg1
        Case 1004
        MsgBox ("File is not fund. Please verify its path.")
        Exit Sub '!EDITED! <GoTo NextCode:> is much better indeed 
' ...more code with other errors...
End Select
NextCode: ' !EDITED!
' ...some cleaning and initialize variables to nothing or 0
Lg1 = 0
Err.Clear
End Sub

Then I would like to use this sub in other sub this way :

On Error Go To MyError: ' <On Error call ErrorHandler> is not permited
'...more code....
MyError:
call ErrorHandler

Now a few questions:

  • Is it a good practice ? If not, what would you recommend ?
  • In case 1004 for example, when I say Exit sub, will the ErrorHandler sub itself go to end and do the cleaning stuff, or will it stop too ?
  • Any good hints about common and useful user defined errors (513-65535) ?

Thank you for help.

Community
  • 1
  • 1
gabx
  • 472
  • 2
  • 7
  • 18
  • Seems perfectly reasonable to me. If you `exit sub` you will exit immediately, in your example to do the tidy-up code remove `exit sub` and you will fall out of the Case block. Unless there is a specific reason to override the default descriptive text you can use `err.description`. – Alex K. Nov 12 '13 at 11:49
  • I think @Oliver's answer properly points out the pitfalls with a "central error handler", especially one invoked from library-type routines. You might find this answer helpful as well: http://stackoverflow.com/a/4432413/58845 – jtolle Dec 20 '13 at 20:33

1 Answers1

1

When you do "Exit Sub" in the ErrorHandler(), it will of course leave the ErrorHandler() immidiately and return to the caller, so no cleanup is done. You can of course remove the "Exit Sub", so your cleanup will run, but it will always be the same for all cases.

The Problem I see in this approach comes after returning from ErrorHandler(). You return to the caller with absolutely no idea what ErrorHandler() did. How are you going to continue from there? Catching and Displaying the Error is only half the solution. You need to come up with a way to continue from there. If it's a "common Task" you are in, you need to cancel/abort/resume that in some way or at least return some error code to it, so it knows to end gracefully.

VBA itself is a bit bad at this situation. We use http://www.everythingaccess.com/vbwatchdog.htm in our projects to handle this problem as it provides a powerful generic error handling method, quite as you aim to do.

Oliver
  • 3,225
  • 1
  • 18
  • 12
  • First, I just wrote a modification to my code. In case 104 example, I think best is to NOT *Exit Sub* but rhater *GoTo NextCode*. The *NextCode* section will be placed AFTER all cases and do more things before exiting.@Oliver : for some reasons, I can not afford any paid extra soft. – gabx Nov 12 '13 at 12:42
  • @gabx: I totally understand that problem. We had to argue about a year with our employer to get it... The simple fact is that it's cheaper to buy than build yourself. But yes, I know, the developer is on the project anyway and all that. – Oliver Nov 14 '13 at 08:36