Visual Basic for Applications/Error Handling

Summary

The code module below shows one layout method for error handling. It uses a bit more space than the usual thing but has good clarity. It also includes error logging and a block for testing the code by raising errors. Only a few errors have been listed.

Notice that no formatting is done in the log writing procedure itself, and that a choice of block logs with line separation or serial logs with comma-separation are both included.

VBA Code

Option Explicit

Sub ErrorCodeShell()
    'time saving errors code shell
   

On Error GoTo ERR_HANDLER
    
    '===================================
    'Main body of procedure goes here...
    '===================================
    
    '===================================
    '   Raise Errors Here For Testing
    '===================================
    'Err.Raise 6  'overflow
    Err.Raise 11 'div zero
    'Err.Raise 53 'file not found
    'Err.Raise 70 'permission denied
    '===================================
    
    Exit Sub
ERR_HANDLER:
    If Err.Number <> 0 Then
        'LOG ERROR DETAILS
        
        'make error messages
        Dim sE1 As String, sE2 As String
        Dim oErr1 As ErrObject, oErr2 As ErrObject
        
        'make error messages
        Set oErr1 = Err: Set oErr2 = Err
        sE1 = Message1(oErr1) 'block style message
        sE2 = Message2(oErr2) 'serial style
        Set oErr1 = Nothing: Set oErr2 = Nothing
                
        'enable logging as block or serial format
        LogError3 sE1   'write to log block style
        'LogError3 sE2   'write to log serial style
                
        'write to immediate window
        Debug.Print sE1 'block style
        'Debug.Print sE2 'serial style
        
        'selective error handling
        Select Case Err.Number
        Case 53
            GoTo FileNotFound
        Case 70
            GoTo PermissionDenied
        Case Else:
            GoTo OtherErrors
        End Select
FileNotFound:
        'Handle the error
        Err.Clear
        Exit Sub
PermissionDenied:
        'Handle the error
        Err.Clear
        Exit Sub
OtherErrors:
        MsgBox sE1
        Err.Clear
        Exit Sub
    End If

End Sub

Function LogError3(sIn As String) As Boolean
    'logs parameter string to a text file
    'assumes same path as calling Excel workbook
    'makes file if does not exist
    'no layout or formatting - assumes external
    
    Dim sPath As String, Number As Integer
    
    Number = FreeFile 'Get a file number
    sPath = ThisWorkbook.Path & "\error_log3.txt" 'modify path\name here
    
    Open sPath For Append As #Number
    Print #Number, sIn
    Close #Number

    LogError3 = True
            
End Function

Function Message1(oE As ErrObject) As String
    'makes block style message for error
    
    Dim sEN As String, sSrc As String
    Dim sDesc As String, sDT As String
    
    'make date-time string
    sDT = Format(Now, "d mmm yyyy") & ", " & _
                   Format(Now, "dddd hh:mm:ss AMPM")
    
    'get error parts
    sEN = CStr(oE.Number)   'number of error
    sSrc = oE.Source        'source of error
    sDesc = oE.Description  'description of error
    
    'make block message with line separations
    Message1 = sDT & vbNewLine & _
        "Error number: " & sEN & vbNewLine & _
        "Source: " & sSrc & vbNewLine & _
        "Description: " & sDesc & vbNewLine

End Function

Function Message2(oE As ErrObject) As String
    'makes serial style message for error
    
    Dim sEN As String, sSrc As String
    Dim sDesc As String, sDT As String
    
    'make date-time string
    sDT = Format(Now, "dddd yyyy mmm d hh:mm:ss")
    
    'get error parts
    sEN = CStr(oE.Number)   'number of error
    sSrc = oE.Source        'source of error
    sDesc = oE.Description  'description of error
    
    'make serial message with comma separations
    Message2 = sDT & ",Error " & sEN & "," & sSrc & "," & sDesc

End Function