A Better VBA Error-Handler

Today I came across something extremely interesting.

In an article by Mike Groh, editor of Access VBA Editor magazine, excerpted in an on-line teaser at VBA Error-Handling Basics, there is an intriguing piece of VBA code put forward as an example of effective error-handling (the interesting bit is bolded):

Public Sub MyProc()
On Error GoTo HandleError
1000
'A guaranteed error:
2000
Debug.Print 1 / 0
ExitHere:
Exit Sub
HandleError:
Call LogError( _
plngNumber:=Err.Number, _
pstrDescription:=Err.Description, _
pstrModuleName:="ThisModuleName, _
pstrProcedureName:="MyProc", _
plngLineNumber:=Erl)
Resume ExitHere
End Sub

The constant Erl returns the line number in the procedure where the error occurs, but as I discovered after playing around with the code in Access 2000, only if line numbers exist in the procedure. I revised the procedure to:

Public Sub MyProc()
10 On Error GoTo HandleError
'A guaranteed error:
20 Debug.Print 1 / 0

ExitHere:
30 Exit Sub

HandleError:
40 Debug.Print "Error No: " & Err.Number
50 Debug.Print "Error Description: " & Err.Description
60 Debug.Print "Error from Line No: " & Erl
70 Resume ExitHere

End Sub

which returns:

Error No: 11
Error Description: Division by zero
Error from Line No: 20

in the Immediate Window.

So you now have an error-handler that returns the line number in which the error occurred!

Implementing this technique in your VBA applications, new AND existing, is easy using a great freeware Office VBA Editor add-in, MZ-Tools 3.0 VBA , which adds line numbering to a procedure in two mouse-clicks.