Error handling

I have an error handler in my app.  I used VBA MZ Tool 3.0 to set up my error handling in all the modules in my app.

This puts a standard on error clause and logic at the end of each function or subroutine like this:

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Function
                               sysErrorHandler Err.Number, Err.Description, "GetNextWorkID", "Common", "Module"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

All errors are thrown to my common error handler shown here:

Public Sub sysErrorHandler(passedErrNum As Long, _
                            passedErrDesc As String, _
                            passedRoutine As String, _
                            passedModuleName As String, _
                            passedModuleType As String)

' Blank tab on spreadsheet, which is OK 3673
'If passedErrNum = 3673 Then
'    Err.Clear
'    Exit Sub
'End If

gResponse = MsgBox("An error has occurred in your application.  The specific error is:  " & vbCrLf & vbCrLf & _
   "Error " & passedErrNum & " (" & passedErrDesc & ") in Procedure " & passedRoutine & " of " & passedModuleType & Space(1) & passedModuleName & vbCrLf & vbCrLf & _
   "Please copy down the error information on the above lines or take a screen snapshot so you can supply this information when you contact tech support." & vbCrLf & vbCrLf & _
   "Tech support email: ." & vbCrLf & _
   "When you press the ""OK"" button you will exit the system.", vbCritical + vbOKOnly, "Unrecoverable System Condition")
      Application.Quit acQuitSaveNone

'   End If
End Sub

I commented out the logic that I was trying to implement because it wasn't working the way I want.

I am importing multiple tabs on a spreadhseet.  Some of the tabs don't contain any data, which is OK.  When no data is pulled I get a 3673 error.  As you can see I tried to modify my error handler to make 3673 'acceptable'.  

What I want to happen when a 3673 errror occurs is nothing.  I want processing to continue.

Putting in the commented logic was not doing that.  With the logic I would enter the error handling routine and hit the 'If passedErrNum = 3673 Then' statement, execute the Err.Clear and leave the error handler.  So far so good.  However, once I left the error handler the program didn't continue to process, it just kind of hung.  No errors were shown but nothing was done.

I tried adding a resume next in the logic and I got an error on that.

How do I structure the logic in the error handler to work as desired.
The transferspreadsheet statement is executed, the error 3673 is thrown and processing continues with the next statement after the transferspreadsheet?
Who is Participating?
At the end of each procedure... give yourself an "exit door" and use it every time (Progout:)

on error resume next
'---- close all opened databases --
set rst = nothing
set db = nothing
if WhatToDo = 3 then
end if
exit sub '(or function)

                               sysErrorHandler Err.Number, Err.Description, "GetNextWorkID", "Common", "Module", WhatToDo
'------ this is new ---------
Select case WhatToDo
             case 1
                        WhatToDo = 0
                        resume next
             case 2
                        WhatToDo = 0
             case 3
                       '---- do clear WhatToDo so it quits in the Progout section
                       goto Progout  
end select

Public Sub sysErrorHandler(passedErrNum As Long, _
                            passedErrDesc As String, _
                            passedRoutine As String, _
                            passedModuleName As String, _
                            passedModuleType As String,_
                            WhatToDo as long)

'--- 1 = ignore ---     resume next
'--- 2 = try again --- resume
'--- 3 = quit app ---  application .quit

' Blank tab on spreadsheet, which is OK 3673
If passedErrNum = 3673 Then
    WhatToDo = 1
    Exit Sub
End If

'--- add the rest of your error sub and assign a value to WhatToDo and then return to original procedure.

Scott C
A couple things:
1.  If you have a legitimate error, your error handler quits the application (Application.quit in the Public Sub sysErrorHandler).   This is not a good way to handle this.  If you have any objects, such as Excel or others running, or databases open, you are NOT setting them to "nothing".

2. What happens in each module during the error handler "GetNextWorkID_Error:" ?  It looks like any/every error causes your app to quit.

You should change your "error sub" to return a value with the decision to either quit the app or RESUME NEXT.  If you quit the app,  you should make sure any and all opened objects are closed.

This is much busier since this code should be in every procedure that contains the error handler sub Public Sub sysErrorHandler.

Scott C
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
in addition to what's been said, also consider using MZ tools to add line numbers, then use the undocumented VBA.ERL() call as part of the call to the error handler.

 This pin points the line the error occured on.

Jeffrey CoachmanMIS LiasonCommented:
Scott brings up a great points about generic error handlers
Other things that wreak havoc when the EH just "exits".
If SetWarnings are turned off
If Echo is turned off (This is a nightmare because the app appears to be "Locked")
If variables need to be set/re-initialized

Perhaps I am old school, but I just put a standalone EH in each of my subs.
(In my apps), I never had a real "need" for a global EH, especially if it does not do what I need it to, and I don't know how to modify it so that it does.

Another thing to consider would be to try to avoid the error in the first place.
For example, why not just delete any blank sheets...?, then "theoretically" Error 3673  should not occur.

    Dim wrksht As Worksheet
    For Each wrksht In Worksheets
        If WorksheetFunction.CountA(wrksht.Cells) = 0 Then
            Application.DisplayAlerts = False
            Application.DisplayAlerts = True
        End If
    Next wrksht
(Ironically this sub itself needs an EH to turn DisplayAlerts back on in case of an error...)

FWIW, the Basic EH I use is something like this:

On Error GoTo Err_YourSubName

********** Your Code Goes Here **********

    Exit Sub

    If Err.Number = n Then
        'Reset Something
        Resume Exit_YourSubName
        MsgBox "There was an error executing the command." _
        & vbCrLf & "Error " & Err.Number & ": " _
        & vbCrLf & Error, vbExclamation
        Resume Exit_YourSubName
    End If

Again, I am not opposed to Global EH's, (I have used on in a few of my apps at a customers request) it's just that I like to keep my own code simple, understandable and portable.
And finally, I am not even opposed to just using : On Error Resume Next
Some might view this as "sloppy", but if the app/sub is simple, I see nothing wrong with it...

As always, with anything in the world of App Dev...
   "It depends on your environment, what you are doing, and your exact needs"
So there is no real Right or Wrong here.


mlcktmguyAuthor Commented:
Thanks to all that responded.  I now understand why my logic wasn’t working.  As always with EE some very good answers and suggestions.

I realized that my central error handler exits the app on any unexpected error.  Any errors that I expect or I am aware are possible will be handled in the modules that they occur.  Anything that doesn’t come up in our QC testing will indeed get sent to the generic error handler and exit the application.

If something gets to the error handler I want to display the error and end it all.  This is because the error is totally unexpected and I really don’t know the appropriate action to take if I want to continue processing.  In my experience, continuing to process after unexpected errors usually leads to more errors.  

It is rare that I get a user report of an error that gets to the app wide error handler and kicks the user out of the application.  The EH is there as a catch all net, especially when we distribute MDE’s instead of MDB’s.

When errors are reported, I research the cause(s) and if appropriate, add specific error handling for those situations.  Line numbers would definitely be helpful in locating errors.

This one size fits all error handler called from all over the app does have the big flaw of not setting closing any objects that are open or in use at the time of the error.  When I originally created the appwide error handler I tried to develop routines to automatically close all open forms etc.. but they never worked correctly, so I abandoned them.

Even with an error handler in a subroutine or function, there are so many different exit points possible it would is difficult to know exactly what has been opened or set at every moment during the routines execution.  Trying to close files that haven’t been opened leads to errors also.

I will be posting another question related to this issue.  How to determine what object has been opened or is in use at any give time so the appropriate objects can be reset closed, etc..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.