Avatar of mlcktmguy
mlcktmguy
Flag for United States of America asked on

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
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?
Microsoft Access

Avatar of undefined
Last Comment
mlcktmguy

8/22/2022 - Mon
clarkscott

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
ASKER CERTIFIED SOLUTION
clarkscott

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jeffrey Coachman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mlcktmguy

ASKER
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..
Your help has saved me hundreds of hours of internet surfing.
fblack61