Visual Basic for Applications/Error Handling
Summary
editThe 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
editOption 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