Solved

Error handling

Posted on 2013-01-01
5
418 Views
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
GetNextWorkID_Error:
                               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?
0
Comment
Question by:mlcktmguy
5 Comments
 
LVL 20

Expert Comment

by:clarkscott
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
0
 
LVL 20

Accepted Solution

by:
clarkscott earned 200 total points
ID: 38735742
Example:
At the end of each procedure... give yourself an "exit door" and use it every time (Progout:)


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


GetNextWorkID_Error:
                               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
                        resume
             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
0
 
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.

Jim.
0
 
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
...etc

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
            wrksht.Delete
            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_YourSubName:
    Exit Sub

Err_YourSubName:
    If Err.Number = n Then
        'Reset Something
        Resume Exit_YourSubName
    Else
        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.

;-)

JeffCoachman
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
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..
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now