Error Handling via an Error Class

A while ago I read an answer on stackoverflow about error handling. I can’t seem to find that question now, so you’ll have to take my word for it. The question was asking about error handling best practices in VBA and I found one of the answers interesting. The answerer said that you could use the Terminate event of a custom class module as your error logger. I had never thought of that.

I’ve been using the Professional Excel Development error handling method with great success for many years. This method controls the error as it moves back up the call stack, either by returning a Boolean to the calling procedure or by rethrowing the error. Without error handling, VBA sends the error back up the call stack automatically until it is handled. So, for instance, if you only handled errors in your entry point procedures, you would still have an error handler. You just wouldn’t have the information about the stack that would be critical to finding out where the error occurred.

The class method of error handling takes advantage of the built-in ability of VBA to pull an error back up the stack. It uses the fact that local variables go out of scope when the procedure is complete, such as when an unhandled error occurs and the cursor is sent to the calling procedure. If you had a local variable pointing to a class, that class’ Terminate event would fire when an unhandled error occurred.

Borrowing the setup from PED2, let’s see how this would work.

Sub EntryPoint()
   
    Dim clsError As CError
   
    On Error GoTo ErrorHandler
   
    Set clsError = New CError: clsError.SetLoc "Module1", "EntryPoint"
    SubProc1
   
ErrorExit:
    Exit Sub
   
ErrorHandler:
    If gbDebugMode Then
        Stop: Resume
    Else
        Set clsError = Nothing
        MsgBox Err.Description
        Resume ErrorExit
    End If
   
End Sub

Sub SubProc1()
   
    Dim clsError As CError
   
    Set clsError = New CError: clsError.SetLoc "Module1", "SubProc1"
               
    SubProc2
   
End Sub

Sub SubProc2()
   
    Dim clsError As CError
   
    Set clsError = New CError: clsError.SetLoc "Module1", "SubProc2"
   
    Debug.Print 1 / 0
   
End Sub

EntryPoint calls SubProc1. SubProc1 calls SubProc2. An error occurs in SubProc2. Only EntryPoint has error handling. It uses On Error Goto ErrorHandler to route program flow to the error handling section. Neither SubProc1 nor SubProc2 have any error handling. We’re going to let VBA kick the error back up to the call stack until it gets to EntryPoint.

Each procedure has a local variable that points to an instance of CError. CError is a custom class whose Terminate event I’ll be using to log the error as it moves back up the stack. When the error occurs in SubProc2, the clsError variable in SubProc2 goes out of scope and its Terminate event fires. The error is passed up to SubProc1 by VBA by design. Because there is no error handling in SubProc1, that error causes the instance of clsError in SubProc1 to go out of scope and its Terminate event fires.

Once again, VBA does it’s thing by passing control back up the stack, error in tow. EntryPoint does have error handling, so when program control reaches it, the ErrorHandler section goes into action. Assuming we’re not in debug mode, the first thing to do is terminate clsError by setting it to nothing. By the time we exit this procedure, the built-in Err object will have been reset and we won’t have anything to log. By setting clsError in EntryPoint to Nothing, we get the last entry in our log. After that, the error is displayed and program control is sent back to ErrorExit for any clean up (no clean up in this example, just the Exit Sub).

The log looks like this:

01 Jan 14 21:40:40 [errorclass2.xlsm]Module1.SubProc2, Error 11: Division by zero
01 Jan 14 21:40:40 [errorclass2.xlsm]Module1.SubProc1, Error 11: Division by zero
01 Jan 14 21:40:40 [errorclass2.xlsm]Module1.EntryPoint, Error 11: Division by zero

Of course I made it virtually identical to PED’s log entry.

Instead of putting error handling in all of the downstream procedures, I just put a local variable that will terminate when an error occurs. The class looks like this:

Private mlErrorID As Long
Private msProcedureName As String
Private msModuleName As String

Private Sub Class_Terminate()
   
    If Err.Number > 0 Then
        Debug.Print Format(Now, "dd mmm yy hh:mm:ss") & Space(1) & Me.Location & ", " & Me.ErrDescription
    End If
   
End Sub
Public Property Let ModuleName(ByVal sModuleName As String): msModuleName = sModuleName: End Property
Public Property Get ModuleName() As String: ModuleName = msModuleName: End Property
Public Property Let ErrorID(ByVal lErrorID As Long): mlErrorID = lErrorID: End Property
Public Property Get ErrorID() As Long: ErrorID = mlErrorID: End Property
Public Property Let ProcedureName(ByVal sProcedureName As String): msProcedureName = sProcedureName: End Property
Public Property Get ProcedureName() As String: ProcedureName = msProcedureName: End Property

Public Sub SetLoc(ByVal sModule As String, ByVal sProc As String)
   
    Me.ModuleName = sModule
    Me.ProcedureName = sProc
   
End Sub

Public Property Get Location() As String
   
    Location = "[" & ThisWorkbook.Name & "]" & Me.ModuleName & "." & Me.ProcedureName
   
End Property

Public Property Get ErrDescription() As String
   
    ErrDescription = "Error " & Err.Number & ": " & Err.Description
   
End Property

I’ve kept the logging pretty simple for this example. In the Class_Terminate event, I first check to see if Err.Number is zero. This method relies on the fact that the Terminate event will fire when an error occurs. But in reality, the Terminate event will fire when the subprocedure finishes without error too. It fires whenever my local variable goes out of scope and that happens when there’s an error or when the subprocedure completes. We only want to log when an error occurs, so we have to check that.

The logging is a simple Debug.Print statement. To replicate the PED method, that would need to be expanded to write to a log file.

This is a very simple example that I put together to see how this might be setup. There might be some problems with this method that I haven’t encountered. I’m not advocating that you use this method, but I am intrigued by its simplicity. If you have any thoughts on this method of error handling or on error handling in general, leave a comment below.

You can download errorclass2.zip

14 Comments

  1. Cyril says:

    Hi Dick,

    Happy new year, and thanks for your very good article.
    I’m using PED error handler too and i’m a big fan.
    Your method seems really great too, but for example, I’m using lot of functions in my code and don’t you think that calling for each of them a new clserror object won’t be too much for memory (even if your class is pretty small) ?
    I’ll try your way instead of PED for my next development and give you a feedback.

    Thanks for sharing.
    Cyril

  2. Dick Kusleika says:

    Cyril: Good point about excessive memory. They’re pretty small classes, as you say, but if you get too many levels deep into the code that could add up. It would be interesting to see what kind of effect that has.

  3. […] Good news – the Daily Dose of Excel blog is up and running again, after some server problems. Dick Kusleika takes a look at error handling via an error class. […]

  4. Charlie says:

    Fascinating – and thanks for writing it up.

    I see a couple of issues.

    First I like to get the line number of the vba statement that experienced the error – currently with PED that is passed in as Erl – can the class approach support it?

    Second, as per PED, there are usually clean up statements before exiting any procedure – and those clean up statements are preceded by “On Error Resume Next” just in case there is an error that we didn’t bother to handle. What if an unhandled but resumed error happens just before the subroutine terminates the class – will the err number appear in the class termination code and hence get processed like it actually happened?

    And finally, I wonder about the overhead as mentioned earlier – with the non-class approach the error handling does not impact memory and performance until an error occurs – except I guess the first “On Error Goto ErrHandler” statement. Which is how it should be – we don’t want to slow down normal processing to handle unexpected and rarely occurring errors. It would be great to hear back if there are any memory and performance issues.

    Thanks for posting – it is always worthwhile challenging the standard way of handling things just in case there is a better way. I particularly liked the concept of SystemState that does use the class concept – it cleaned up my code immensely and now I use it all the time (where appropriate).

    –Charlie

  5. Dick Kusleika says:

    Yes, Erl will work here too.

    Yes, errors in the clean up procedure will be logged, but not displayed.

    What do you guys think is a good test of the memory cost? How many levels deep do I have to go before it’s even significant? There is a cost to the PED way too. All of your code gets compiled and loaded into memory and the PED way uses far more lines per procedure than this way. I’m guessing that neither is significant, but I think it’s time for a test. I’m on it.

  6. Dick Kusleika says:

    Fifteen subprocedures under an entry point procedure (16 in the call stack) and the memory shown by Task Manager went up by … wait for it … zero.

  7. Dick Kusleika says:

    Thanks to SC for finding the Stack Overflow question: http://stackoverflow.com/questions/3792134/get-name-of-current-vba-function

    And here’s a Wikipedia entry about it: http://en.wikipedia.org/wiki/RAII

  8. Peter Albert says:

    Interesting approach!

    I like the fact that you don’t need an error handler in each routine ( i.e. -3 lines when using : ) but instead only add 2 lines of code (or actually 1 with : )!

    However, the huge advantage of the PED error handler is the Stop: Resume statement if in debug mode in each routine! This makes debugging so much easier. So I guess I’d end up up with the error handler anyway – and then the class approach doesn’t add any additional value. :-(

    Or am I missing something here?

  9. snb says:

    Wouldn’t this code produce the same functionality/result as the PED approach ?

    Dim x01

    Sub EntryPoint()
        On Error Resume Next
        x01 = "Module1.Entrypoint"
        SubProc1
       
        If Err.Number <> 0 Then MsgBox x01
    End Sub

    Sub SubProc1()
        x01 = "Module1.SubProc1"
    '    Debug.Print 1 / 0
       
        SubProc2
    End Sub

    Sub SubProc2()
        x01 = "Module1.SubProc2"
       
        Debug.Print 1 / 0
    End Sub
  10. Peter Albert says:

    @snb: It would, but only in this special case. Imagine the error would be caused in SubProc1 _after_ calling subProc2. Then x01 would hold the wrong information…

  11. Dick Kusleika says:

    I knew there was going to be a fatal flaw this method and the Stop:Resume piece is it. Even if you put a Stop in the Terminate event, the offending procedure has already popped off the stack, so you can’t even get to it that way.

  12. Jase says:

    Hi Dick

    I’ve just returned to vba programming after several years away and just wonder at the speed you can be up and running with something functional in no time at all. Anyway, I’ve been going over the site in the last few weeks and have to congratulate you guys on the choice of topics and excellent examples. I’m almost ‘classed’ out but having read your error handling classes I feel I have room for more.

    I love the idea of this approach and wonder about yours and Peter’s comments about the advantages of debug stop. Without sounding like a total fool I feel like I’m missing something too – surely the error log should provide all the information you need. What is the most important thing here? debugging or elegant, by design error trapping?

  13. Dick Kusleika says:

    Jase: The problem with the error log is that it doesn’t give you a line number. So it’s very difficult to determine which line caused the error.

    Okay, but what if I have a debug flag set. If I’m in debug mode, the class never gets created and the code errors right where it errors. I’ll have to think about this.

  14. […] them does not perform well. I’ve written a small article on my site, inspired by a post here: Error Handling via an Error Class. I needed a quick way to log and report performance of subroutines and I also wanted to see how […]

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: