Error handling in VBA: how to do it wrong

[Josef P.]

Addition to: https://www.access-programmers.co.uk/forums/threads/menus-on-a-form.326893/post-1868023
I'm starting a new thread since this doesn't fit the actual topic of the linked thread.

However, since I always see a similar structure in error handling, I wanted to show an example of how not to implement error handling.

This error handling is incorrect:

Public Sub TestMe()

On Error GoTo ErrHand

   CalcAndSaveResult 2, 1

ExitHere:
Exit Sub

ErrHand:
   HandleError "TestMe"
   Resume ExitHere

End Sub

Private Sub CalcAndSaveResult(ByVal a As Long, ByVal b As Long)

   Dim result As Long
   Dim InsertSql As String

On Error GoTo ErrHand

   result = CalcResult(a, b) '<--- an error occurs in this procedure and an incorrect result is returned.
                             '     From here on there is no error => code continues to run as if nothing had happened.

   InsertSql = "insert into TableABC (a, b, R) Values (" & Str(a) & ", " & Str(b) & ", " & Str(result) & ")"

   MsgBox "Result (a-b)/(b-1) = " & result & vbNewLine & vbNewLine & InsertSql
   ' ^
   ' |.. dummy replacement for data operation e.g:
   ' CurrentDb.Execute InsertSql, dbFailOnError

   ' Save 0 is correct?

ExitHere:
Exit Sub

ErrHand:
    HandleError "CalcAndSaveResult"
    Resume ExitHere

End Sub

Private Function CalcResult(ByVal a As Long, ByVal b As Long) As Long

On Error GoTo ErrHand

    CalcResult = (a - b) \ (b - 1)

ExitHere:
Exit Function

ErrHand:
   HandleError "CalcResult"
   Resume ExitHere

End Function

Private Sub HandleError(ByVal ProcName As String)
   ' dummy replacement for error logging (save in text file, table, ...)
   MsgBox "Error " & Err.Number & " in " & ProcName & ": " & Err.Description
End Sub

Is the error of the error handling recognizable?

The solution is simple:
Either pass the error up or include error handling for unexpected/unresolved errors in the top-level procedure only.

 

[MarkK]

Put error handling wherever you need to.

 

[The_Doc_Man]

With some exceptions, EVERY module should have its own private error handling.

The exceptions relate to subroutines that, of their nature, cannot generate an error, perhaps because they pre-screen the inputs before attempting to execute code that could generate an error. I.e. rather than trap the error, they avoid it. Also, something that only does bitwise logic using AND, OR, NOT, XOR, and EQV (bit-twiddling) cannot generate a math error. There can be other cases to represent the possible exceptions.

Allowing an error to "bubble up" to an upper subroutine layer will sometimes make sense, but more often than not, it merely asks for confusion.

 

[Josef P.]

First of all: please consider the artificially generated error in the example only symbolically. One will catch the division by 0 already completely above.
My point in the example is that the statement that every procedure must have error handling is wrong if the error handling consists only of logging the error and then continuing as if nothing had happened.

If you cannot fix an error in a procedure, you must not ignore it. You can only fix or avoid expected errors in a procedure.
The progress should be stopped in case of an unexpected error, unless it is known in the procedure code that it is possible to continue in case of any error. This should be done in this procedure and not by deleting errors in sub-procedures.

If you write clear procedures (only 1 task per procedure), error handling is usually not very complex.

Error handling is necessary if:
* procedure is the top call level (typical: ACommandbutton_Click or ATextbox_AfterUpdate procedure) ... + possibly error info to User
* data objects (recordsets) or similar are opened and should also be closed (pass error upwards after closing the recordset)
* an expected error is corrected (here nothing must be passed on upward, since the error was eliminated)

Procedure without error handling
* no expected error is known and the procedure is called by other procedures

In summary: Error handling only where it is necessary.

 

[isladogs]

Some time ago, I posted the dummy code below and asked people to state whether all the errors would be triggered.
If not, which would/would not and why

Private Sub cmdErrorTest_Click()

On Error GoTo Err_Handler

 'add your code here
    MsgBox "Click OK to raise error 11 in the main code"
   Err.Raise 11
  
Exit_Handler:
    MsgBox "Click OK to raise error 94 in the Exit Handler section"
    Err.Raise 94
    Exit Sub
  
Err_Handler:
    MsgBox "Error " & Err.Number & " in cmdErrorTest_Click procedure : " & vbCrLf & _
        Err.Description, vbCritical, "Program error"
    MsgBox "Click OK to raise error 7 in the Err Handler section"
    Err.Raise 7
    Resume Exit_Handler

End Sub

 

[The_Doc_Man]

@isladogs - your sample code would raise error 11, which would then raise error 7. However, error 94 would not be raised because error 7 would be raised by an active handler, which would automatically "bump" up to the next call level. (Technically called a "stuck unwind.") The code following label Exit_Handler would not be executed so the Err.Raise 94 will be skipped. Error 11 would be HANDLED by the code after label Err_Handler as shown. Error 7 would be HANDLED by the next error handler available from higher on the call stack. Since we don't see whether there is an OnError entry for the cmdErrorTest_Click() routine's containing form, it is ambiguous as to WHERE error 7 would be handled. If there is no OnError entry for the form in question, the error would be handled by the Access "last chance" error handler, which would give you the message box that includes the "Debug" and "Reset" options - and the "Continue" option will probably be grayed out in that case.

I agree with @Josef P. that having an error handler that ONLY logs an error but does nothing else with it is probably not such a good idea. Though I might disagree to this extent: While developing and debugging, an error handler in every routine DOES help you to identify situations that will need handling. But there are routines that just don't need error handlers, which is why in my earlier comment, I said "with some exceptions." And to the extent that they would help you realize you had a possible error condition in a given routine that you THOUGHT was impregnable, even just an error log can be useful for the developer. For the end user, not so much.

 

[Josef P.]

While developing and debugging, an error handler in every routine DOES help you to identify situations that will need handling.

In development, it doesn't bother me that much because I keep the option "Break on All Errors" enabled.
The callstack is also interesting to me for error reports from the users. This is done by vbWatchdog for me. Without vbWatchdog this is very cumbersome to write in VBA.
Before I used vbWatchdog, it looked something like this (code simplified):

Public Sub TestMe()

On Error GoTo ErrHand
   CalcAndSaveResult 2, 1

ExitHere:
Exit Sub

ErrHand:
   HandleError "TestMe", "TestMe->" & Err.Source
   Resume ExitHere

End Sub

Private Sub CalcAndSaveResult(ByVal a As Long, ByVal b As Long)

   Dim result As Long
   Dim InsertSql As String

On Error GoTo ErrHand

   result = CalcResult(a, b) '<--- err raised
   InsertSql = "insert into TableABC (a, b, R) Values (" & Str(a) & ", " & Str(b) & ", " & Str(result) & ")"
   MsgBox "Result (a-b)/(b-1) = " & result & vbNewLine & vbNewLine & InsertSql

ExitHere:
Exit Sub

ErrHand:
    Err.Raise Err.Number, "CalcAndSaveResult->" & Err.Source, Err.Description

End Sub

Private Function CalcResult(ByVal a As Long, ByVal b As Long) As Long

On Error GoTo ErrHand
    CalcResult = (a - b) \ (b - 1)

ExitHere:
Exit Function

ErrHand:
   Err.Raise Err.Number, "CalcResult->" & Err.Source, Err.Description

End Function

Private Sub HandleError(ByVal Source As String, ByVal SourceTree As String)
   ' dummy replacement for error logging (save in text file, table, ...)
   MsgBox "Error " & Err.Number & " in " & Source & ": " & Err.Description & vbNewLine & vbNewLine & SourceTree
End Sub

... and that would output only the callstack without variable contents, etc.

With a HandleError procedure (with logging etc.) it looked something like this:

...
ExitHere:
   Exit Function

HandleErr:
   Dim ErrNo As Long, ErrSource As String, ErrDesc As String
   Select Case HandleError(ErrNo, ErrSource, ErrDesc, "ProcName", DefaultErrorHandlerMode)
      Case ErrorHandlerMode.ErrExitProc
         Resume ExitHere
      Case ErrorHandlerMode.ErrContinue
         Resume
      Case ErrorHandlerMode.ErrNext
         Resume Next
      Case Else
         Err.Raise ErrNo, ErrSource, ErrDesc
   End Select

 

[Hmetz]

I have a program that is 100 % error intolerant. The only possible errors are data corruption, a corrupted program or OS problems. The only fix will be replacing the program

Therefore I need a msgbox to display "Error occurred. Application will now close. If this error occurs again contact xxx@zxe.com" if any errors occur. Where should this error handling occur?

 

[GPGeorge]

I have a program that is 100 % error intolerant. The only possible errors are data corruption, a corrupted program or OS problems. The only fix will be replacing the program

Therefore I need a msgbox to display "Error occurred. Application will now close. If this error occurs again contact xxx@zxe.com" if any errors occur. Where should this error handling occur?

In every Sub or Function.

 

[Hmetz]

Here is why I ask. In one module I have 23 onclick, dbclick, and keydown subs like the following that call one of two processing subs.

Private Sub ChooseRange_AfterUpdate()
RunSearch
End Sub

It makes sense to put error handling in the subs that do processing, but it will nearly triple the code to add error handling in the 'calling' subs.

If there is a way to make error at a module level it would make more sense to me.

 

[GPGeorge]

Here is why I ask. In one module I have 23 onclick, dbclick, and keydown subs like the following that call one of two processing subs.

Private Sub ChooseRange_AfterUpdate()
RunSearch
End Sub

It makes sense to put error handling in the subs that do processing, but it will nearly triple the code to add error handling in the 'calling' subs.

If there is a way to make error at a module level it would make more sense to me.

I think that it would be easier, for sure, but I am afraid that's not an option. The module is a container for one or more procedures, not a procedure with events itself.

Perhaps one of the VBA gurus, though, might have a trick up their sleeve.

 

[The_Doc_Man]

If your error message is ALWAYS THE SAME then you can perhaps build a general module subroutine to always print out the same message. You would still have to call that routine from every top-level event handler (i.e. every event that is called because there is a form or report event property that points directly to that handler.) The logic branches coming out of that event handler could simply trap up-the-line to their parent handler - and in fact, WILL do that. But those event handler entry points are like a brick wall that you can't get through.

The problem is that you didn't write MSACCESS.EXE, you bought the black-box program that you can't get into unless it allows you to do so... which usually it doesn't. You didn't write the MAIN routine and have no handles on its inner workings except for the event linkages and macro linkages and stuff like that. Therefore, if you want to trap errors, you have to trap the ones you can see. BUT there is a common error handler that will crash Access for you. It is the "Last Chance" error handler - the one that comes up and gives you the END or DEBUG options. (Usually no other options.) If you hit that guy, your app is NOT going to finish nicely.

 

[Galaxiom]

In every Sub or Function.

Putting error handling message in every Function can be a bad idea if the function is being applied to the data. It will throw the error repeatedly if there is a problem.

Often it is better to return the error from the function as an out of scope return value. For example, if all expected returns are positive then return a negative integer for errors. Or just return the word "Error" if the function returns a string. The pattern of the problem can be immediately seen on each record that caused an error.

 

[KitaYama]

I think that it would be easier, for sure, but I am afraid that's not an option. The module is a container for one or more procedures, not a procedure with events itself.

Perhaps one of the VBA gurus, though, might have a trick up their sleeve.

@GPGeorge
You had a video about correcting how Northwind2 handles errors.
In the video you were explaining that based on @Josef P.'s suggestion, a new version of Northwind will handle CallStack to track back errors.
At the time, I followed the direction in the video and it helped a lot to track errors.

First thanks for the video, but is it almost implemented in Northwind2?

Thanks.

 

[GPGeorge]

@GPGeorge
You had a video about correcting how Northwind2 handles errors.
In the video you were explaining that based on @Josef P.'s suggestion, a new version of Northwind will handle CallStack to track back errors.
At the time, I followed the direction in the video and it helped a lot to track errors.

First thanks for the video, but is it almost implemented in Northwind2?

Thanks.

Well, I am currently only the chapter president and meeting host for Access Pacific. The presentation credit goes to Tom VanStiphout and Kim Young, who did the heavy lifting.

Version 2.3 is in the current template. I believe Tom implemented the enhanced error handling in it.

We are working on a new release of NW, to be available in the near future, primarily for some additional accessibility enhancements. I hadn't really been all that aware of how important accessibility can be until we found ourselves addressing things called out by Microsoft's testers. Little things that many of us take for granted.

Go ahead and instantiate the current template and check out the error handling in it, though. You can cannibalize it if you find it helpful.

 

[GPGeorge]

Putting error handling message in every Function can be a bad idea if the function is being applied to the data. It will throw the error repeatedly if there is a problem.

Often it is better to return the error from the function as an out of scope return value. For example, if all expected returns are positive then return a negative integer for errors. Or just return the word "Error" if the function returns a string. The pattern of the problem can be immediately seen on each record that caused an error.

Good point. I should have clarified that the error handler doesn't need to raise the error messages. A global error handler is more practical. But there needs to be a way to invoke it in each procedure.

 

[Pat Hartman]

Putting error handling message in every Function can be a bad idea if the function is being applied to the data. It will throw the error repeatedly if there is a problem.

Isn't that the point? If you want to ignore certain error codes, then you can but you probably don't want to mask all errors.

 

[Hmetz]

If your error message is ALWAYS THE SAME then you can perhaps build a general module subroutine to always print out the same message. You would still have to call that routine from every top-level event handler (i.e. every event that is called because there is a form or report event property that points directly to that handler.) The logic branches coming out of that event handler could simply trap up-the-line to their parent handler - and in fact, WILL do that. But those event handler entry points are like a brick wall that you can't get through.

The problem is that you didn't write MSACCESS.EXE, you bought the black-box program that you can't get into unless it allows you to do so... which usually it doesn't. You didn't write the MAIN routine and have no handles on its inner workings except for the event linkages and macro linkages and stuff like that. Therefore, if you want to trap errors, you have to trap the ones you can see. BUT there is a common error handler that will crash Access for you. It is the "Last Chance" error handler - the one that comes up and gives you the END or DEBUG options. (Usually no other options.) If you hit that guy, your app is NOT going to finish nicely.

 

[Hmetz]

Thanks!!
Makes sense,
The main routine is above my pay grade.
Rather than respond to all of great advice I have received, I will put my resolution here.
There is no data modification so errors cannot affect data. Being a simple and low cost application I think putting error handling in the few likely places and respond to any users who have the app blow up when they contact me to complain.
Not good PR and when I have a chance I will add error handling in the dozens of one step subs. That the purpose of version control.
Thanks you all!!!!!!!!!!!!!!!!!!!!!!!!!!!