Error Trapping & Logging in VBA

[dhilipkumar]

There's nothing worse than having your VBA exposed to your boss or client because of an untrapped runtime error. Especially if you are moving from one short project to another.

As soon as I start a new Sub or Function procedure, I add my error trapping boiler plate. In this case, it's for an Access form.

Code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err
'do some stuff that may generate an error. Hopefully not!
Form_Open_Exit:
    Exit Sub
     
Form_Open_Err:
    Cancel = True
    Call Error_Handler(strMod:="Form1", strProc:="Form_Open")
    Resume Form_Open_Exit
     
End Sub


Here is what my Error_Handler procedure looks like. Note that I am passing the name of the form and the name of the event, so I know where to look when my client sends me the error log.

Code:

'Purpose: Alerts user to a runtime error; writes error info to a text file.
'Calls: OpenLog
Public Sub Error_Handler(ByVal strMod As String, ByVal strProc As String)
   
   Dim lngErrNum As Long
   Dim strErrDesc As String
   Dim strErrMsg As String
   Dim strPrompt As String
   
   lngErrNum = Err.Number
   strErrDesc = Err.Description & vbNullString
   
   If Err.Number <> 2501 Then
        strPrompt = "Error in Object: "
        strPrompt = strPrompt & strMod
        strPrompt = strPrompt & vbCrLf
        strPrompt = strPrompt & "in Procedure: "
        strPrompt = strPrompt & strProc
        strPrompt = strPrompt & vbCrLf
        strPrompt = strPrompt & "Error: "
        strPrompt = strPrompt & Err.Number
        strPrompt = strPrompt & " : "
        strPrompt = strPrompt & Err.Description
        strPrompt = strPrompt & vbCrLf
        strPrompt = strPrompt & "These details have been written out to "
        strPrompt = strPrompt & vbCrLf
        strPrompt = strPrompt & GetCurrentDBPath & CONST_ERRLOG
        strPrompt = strPrompt & vbCrLf
        strPrompt = strPrompt & "Please call the database programmer, and refer to this file."
        MsgBox prompt:=strPrompt, buttons:=vbExclamation + vbOKOnly, Title:="Error"
    End If
     
    strErrMsg = Now()
    strErrMsg = strErrMsg & "; "
    strErrMsg = strErrMsg & CurrentDb.Name
    strErrMsg = strErrMsg & "; "
    strErrMsg = strErrMsg & strMod
    strErrMsg = strErrMsg & "; "
    strErrMsg = strErrMsg & strProc
    strErrMsg = strErrMsg & "; "
    strErrMsg = strErrMsg & lngErrNum
    strErrMsg = strErrMsg & "; "
    strErrMsg = strErrMsg & strErrDesc
     
    Call OpenLog(instrMsg:=strErrMsg, instrFile:=CONST_ERRLOG)
     
End Sub


Note the case of "Extreme String Building" - a phase that I went through a few years ago. It's a kind of "building blocks" approach. Looks a bit long, but it's easy to spot if something is missing.

[dhilipkumar]

The 2501 Error Number occurs when Form A opens Form B; Form B has a runtime error; you Cancel the opening of Form B, which generates a 2501, which I ignore.

Here is what the OpenLog procedure looks like.

Code:

'Purpose: Writes a string out to a text file.
'Called by: Error_Handler
Private Sub OpenLog(ByVal instrMsg As String, ByVal instrFile As String)
     
    On Error GoTo OpenLog_Err
                 
    Open GetCurrentDBPath & instrFile For Append As #1
     
    Print #1, instrMsg
     
OpenLog_Exit:
    Close #1
    Exit Sub
     
OpenLog_Err:
    Resume OpenLog_Exit
End Sub


This is what GetCurrentDBPath does:

Code:

'Purpose: Returns the path to the current database.
Public Function GetCurrentDBPath() As String

    Dim strDBPath As String
    Dim strDBFile As String

    On Error GoTo GetCurrentDBPath_Err
     
    strDBPath = CurrentDb.Name
    strDBFile = Dir(strDBPath)
    GetCurrentDBPath = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))

GetCurrentDBPath_Exit:
    Exit Function
     
GetCurrentDBPath_Err:
    GetCurrentDBPath = vbNullString
    Call Error_Handler(strMod:=CONST_MODMAIN, strProc:="GetCurrentDBPath")
    Resume GetCurrentDBPath_Exit
     
End Function


CONST_ERRLOG is just a constant:
Code:
Public Const CONST_ERRLOG As String = "ErrorLog.txt"


Note that I output the error log, which is just a text file, to the same folder as the database, so there's no guesswork from the program's point of view or from the client either.

In one of my projects, I was able to see that an my boss had generated an error log on a network drive. I opened it up, read it, and made the fix before she had time to call me.