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’s
Terminate event would fire when an unhandled error occurred.
Borrowing the setup from PED2, let’s see how this would work.
Dim clsError As CError
On Error GoTo ErrorHandler
Set clsError = New CError: clsError.SetLoc "Module1", "EntryPoint"
If gbDebugMode Then
Set clsError = Nothing
Dim clsError As CError
Set clsError = New CError: clsError.SetLoc "Module1", "SubProc1"
Dim clsError As CError
Set clsError = New CError: clsError.SetLoc "Module1", "SubProc2"
Debug.Print 1 / 0
SubProc2. An error occurs in
EntryPoint has error handling. It uses
On Error Goto ErrorHandler to route program flow to the error handling section. Neither
SubProc2 have any error handling. We’re going to let VBA kick the error back up to the call stack until it gets to
Each procedure has a local variable that points to an instance of
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
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
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
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
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
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
Public Property Get Location() As String
Location = "[" & ThisWorkbook.Name & "]" & Me.ModuleName & "." & Me.ProcedureName
Public Property Get ErrDescription() As String
ErrDescription = "Error " & Err.Number & ": " & Err.Description
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