excel-vba Excel-VBA Optimization Optimizing Error Search by Extended Debugging
Example
Using Line Numbers ... and documenting them in case of error ("The importance of seeing Erl")
Detecting which line raises an error is a substantial part of any debugging and narrows the search for the cause. To document identified error lines with a short description completes a successful error tracking, at best together with the names of module and procedure. The example below saves these data to a log file.
Back ground
The error object returns error number (Err.Number) and error description (Err.Description), but doesn't explicitly respond to the question where to locate the error. The Erl function, however, does, but on condition that you add *line numbers ) to the code (BTW one of several other concessions to former Basic times).
If there are no error lines at all, then the Erl function returns 0, if numbering is incomplete you'll get the procedure's last preceding line number.
Option Explicit
Public Sub MyProc1()
Dim i As Integer
Dim j As Integer
On Error GoTo LogErr
10 j = 1 / 0 ' raises an error
okay:
Debug.Print "i=" & i
Exit Sub
LogErr:
MsgBox LogErrors("MyModule", "MyProc1", Err), vbExclamation, "Error " & Err.Number
Stop
Resume Next
End Sub
Public Function LogErrors( _
ByVal sModule As String, _
ByVal sProc As String, _
Err As ErrObject) As String
' Purpose: write error number, description and Erl to log file and return error text
Dim sLogFile As String: sLogFile = ThisWorkbook.Path & Application.PathSeparator & "LogErrors.txt"
Dim sLogTxt As String
Dim lFile As Long
' Create error text
sLogTxt = sModule & "|" & sProc & "|Erl " & Erl & "|Err " & Err.Number & "|" & Err.Description
On Error Resume Next
lFile = FreeFile
Open sLogFile For Append As lFile
Print #lFile, Format$(Now(), "yy.mm.dd hh:mm:ss "); sLogTxt
Print #lFile,
Close lFile
' Return error text
LogErrors = sLogTxt
End Function
'Additional Code to show log file
Sub ShowLogFile()
Dim sLogFile As String: sLogFile = ThisWorkbook.Path & Application.PathSeparator & "LogErrors.txt"
On Error GoTo LogErr
Shell "notepad.exe " & sLogFile, vbNormalFocus
okay:
On Error Resume Next
Exit Sub
LogErr:
MsgBox LogErrors("MyModule", "ShowLogFile", Err), vbExclamation, "Error No " & Err.Number
Resume okay
End Sub
