Error handling

Posted on 2013-01-01
Last Modified: 2013-01-05
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?
Question by:mlcktmguy
LVL 20

Expert Comment

ID: 38735726
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
LVL 20

Accepted Solution

clarkscott earned 200 total points
ID: 38735742
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
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 25 total points
ID: 38736208
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.

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 25 total points
ID: 38737330
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.



Author Closing Comment

ID: 38747437
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..

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question