How to create log file when runtime error occurs?

Topic

New Reply

Some times run time errors occurs, either because of a bug, or because of user=specific scenario.
It is hard to debug remotely.

is there a way to catch a trigger of run time error, and create a log file with information like: code line, certain variable values, error code, etc?

Reply | Quote

Replies

Yigal,

This can all be done in your On Error routines but you have to do the coding to make it happen in each module. Capturing which line can be a bear since you’ll have to keep a counter that’s incremented after each line so you can write it out in the Error Handler. I think you’ll find that trying to do this will be a lot more work than is justified. As for the procedure/Function name you can code a Constant such as: [noparse]CONST ProcName = “Current Proc Name”[/noparse] that can then be used in your error handler to report the offending code.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Yigal,

Here’s some test code I worked up to show you what I was talking about:

Code:

Option Explicit

Sub SampleErrorTrap()

   Dim wkb As Workbook
   
   '*** Variables for Error Trapping ***
   Dim iFN     As Integer  'For File Number
   Dim lLineNo As Long
   Dim zMsg    As String
   Dim zLog    As String
   Const CurrProcName = "SampleErrorTrap"
   zLog = "G:BEKDocsErrLog.txt"
   On Error GoTo GeneralErrorTrap
   
   'You're VBA Code here! My test code follows:
   
   lLineNo = 1              'Start Counting here
   ActiveCell.Value = 10
   lLineNo = lLineNo + 2    'Increment by 2 to allow for counting lines.
   Workbooks.Open ("TestWorkBook.xlsm")
   lLineNo = lLineNo + 2
   
   'Protect Error Trap code
   Exit Sub
   
GeneralErrorTrap:

   Select Case Err
         Case 53
         Case Else
             zMsg = "Untrapped Error @ Line: " & _
                    Format(lLineNo, "###") & vbCrLf & _
                    "In Procedure: " & CurrProcName & vbCrLf & _
                    "Error: " & Format(Err, "###") & vbCrLf & _
                    "Description: " & Error
             MsgBox zMsg, vbOKOnly + vbCritical, "Untrapped Error:"
             iFN = FreeFile()               'Get next available file number
             Open zLog For Output As #iFN   'Open Log fle
             Print #iFN, zMsg               'Write to Log file
             Close #iFN
   End Select
   
End Sub

Message for User:

Data written to file for programmer:

Code:

Untrapped Error @ Line: 3
In Procedure: SampleErrorTrap
Error: 1004
Description: 'TestWorkBook.xlsm' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted.

Of course this only shows catching of the untrapped errors. The CASE 53 is a place holder as you would include a case statement for any normal errors that you want to automatically process.

Note: You may be able to capture the line number via using access to the VBE features but that includes allowing access to the VBE while running your code and errors while accessing that could cause serious problems with your code/file!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

It’s easier if you use line numbers in your code – then you can simply use the erl function.

Reply | Quote

Rory,

Thanks, I never thought of that as I’m not used to using line numbers. Great Idea! :thewave:
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Nor am I to be honest – this would be the only use I can really think of! Plus, MZ Tools can add line numbers to existing code with a single button click.

Reply | Quote

Yigal & Rory,

Here’s a revised sample using Rory’s suggestion…much simpler to code!

Code:

Option Explicit

Sub SampleErrorTrap()

   Dim wkb As Workbook
   
   '*** Variables for Error Trapping ***
   Dim iFN     As Integer  'For File Number
   Dim zMsg    As String
   Dim zLog    As String
   Const CurrProcName = "SampleErrorTrap"
   zLog = "G:BEKDocsErrLog.txt"
   On Error GoTo GeneralErrorTrap
   
   'You're VBA Code here! My test code follows:
   
10   ActiveCell.Value = 10
20   Workbooks.Open ("TestWorkBook.xlsm")   'File not found error
30   ActiveCell.Offset(0, 1).Value = 10 / 0 'Divide by Zero error
   'Protect Error Trap code
   Exit Sub
   
GeneralErrorTrap:

   Select Case Err
         Case 1004   'Sample of a TRAPPED Error
             'Well just ignore this for the File NOt Found test and continue
             Resume Next
         Case Else
             zMsg = "Untrapped Error @ Line: " & _
                    Format(Erl, "###") & vbCrLf & _
                    "In Procedure: " & CurrProcName & vbCrLf & _
                    "Error: " & Format(Err, "###") & vbCrLf & _
                    "Description: " & Error
             MsgBox zMsg, vbOKOnly + vbCritical, "Untrapped Error:"
             iFN = FreeFile()               'Get next available file number
             Open zLog For Output As #iFN   'Open Log fle
             Print #iFN, zMsg               'Write to Log file
             Close #iFN
   End Select
   
End Sub   'SampleErrorTrap()

Sample User Error Msg:
38354-instrumenterrormsg
Sample Log File output:

Code:

Untrapped Error @ Line: 30
In Procedure: SampleErrorTrap
Error: 11
Description: Division by zero

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks a lot!
I will try it and report back.

Reply | Quote

Hey Y’all,

This thread got me to thinking about global error handlers and found this:
White Paper by Luke Chung.

I’ve adapted it to Excel and made a few adjustments. Read the comments for instructions.

Generalized Error Handler Module Code:

Code:

Option Explicit

'+-------------------------------------------------------------------------+
'| Adapted from: "Error Handling and Debugging Tips and Techniques for     |
'|                Microsoft Access VBA                                     |
'|           by: Luke Chung, President of FMS, Inc.                        |
'+-------------------------------------------------------------------------+

'                    +------------------------+                 +----------+
'--------------------| Module Level Variables |-----------------| 11/07/14 |
'                    +------------------------+                 +----------+
' *** Current pointer to the array element of the call stack ***
Private iStackPointer       As Integer
Private Const iStackSizeInc As Integer = 10 ' Stack Size Increment
Private iFN                 As Integer   'For File Number
Private zCallStack()        As String    'Array of procedures in the call stack
Private zMsg                As String
Private zLog                As String

'                        +--------------------+                 +----------+
'------------------------|   PushCallStack()  |-----------------| 11/07/14 |
'                        +--------------------+                 +----------+
'Called by  : Every Procedure!
'Notes      : Adds the current procedure to the call stack array. Needs to
'             be 'called at the beginning of each procedure.
              
Sub PushCallStack(strProcName As String)

  On Error Resume Next

  ' Verify the stack array can handle the current array element
  If iStackPointer > UBound(zCallStack) Then
    ' If array has not been defined, initialize the error handler
    If Err.Number = 9 Then
      ErrorHandlerInit
    Else
      ' Increase the size of the array to not go out of bounds
      ReDim Preserve zCallStack(UBound(zCallStack) + iStackSizeInc)
    End If
  End If

  On Error GoTo 0

  zCallStack(iStackPointer) = strProcName

  iStackPointer = iStackPointer + 1   ' Increment pointer to next element

  
End Sub                  'PushCallStack()

'                        +--------------------+                 +----------+
'------------------------| ErrorHandlerInit() |-----------------| 11/07/14 |
'                        +--------------------+                 +----------+
'Called by  : PushCallStack
'Notes      : Initializes the masterCallStack Array

Private Sub ErrorHandlerInit()

  iStackPointer = 1
  ReDim zCallStack(1 To iStackSizeInc)
  
End Sub                  'ErrorHandlerInit()

'                        +--------------------+                 +----------+
'------------------------|   PopCallStack()   |-----------------| 11/07/14 |
'                        +--------------------+                 +----------+
'Called by  : Every Procedure!
'Notes      : Removes the current procedure from the call stack array.
'             Needs to be called at the end of each procedure.
              
Sub PopCallStack()

  If iStackPointer <= UBound(zCallStack) Then
    zCallStack(iStackPointer) = ""
  End If

  ' Reset pointer to previous element
  iStackPointer = iStackPointer - 1
  
End Sub                 'PopCallStack()

'                        +--------------------+                 +----------+
'------------------------| GlobalErrHandler() |-----------------| 11/07/14 |
'                        +--------------------+                 +----------+
'Called by  : Every Procedure?!
'Notes      : In most cases, when the global error handler is completed,
'             it should quit the program and exit.

Sub GlobalErrHandler()
  ' Comments: Main procedure to handle errors that occur.

  Dim zErrorDesc   As String
  Dim zMsg         As String
  Dim iCntr        As Integer
  Dim iErrorLineNo As Integer
  Dim lErrorNo     As Long

  ' Variables to preserve error information
  zErrorDesc = Err.Description
  lErrorNo = Err.Number
  iErrorLineNo = Erl

  'Setup Error Log File
  If iFN = 0 Then
    iFN = FreeFile()               'Get next available file number
    zLog = "G:BEKDocsTransferErrLog-" & Format(Now(), "YYYY-MM-DD") & ".txt"
    Open zLog For Output As #iFN   'Open Log fle
    
    '*** Write header info to Log file: ***
    Print #iFN, "Run Info: " & vbCrLf & _
                "Path:" & vbTab & vbTab & ActiveWorkbook.Path & vbCrLf & _
                "File:" & vbTab & vbTab & ActiveWorkbook.Name & vbCrLf & _
                "Date/Time:" & vbTab & Format(Now(), "MM/DD/YY HH:MM") & _
           vbCrLf & "-----------------------------------------------" & vbCrLf
  End If
             
  zMsg = "Error @ Line: " & _
         Format(iErrorLineNo, "###") & vbCrLf & _
         "In Procedure: " & zCallStack(iStackPointer - 1) & vbCrLf & _
         "Error Number: " & Format(lErrorNo, "###") & vbCrLf & _
         "Description : " & zErrorDesc
         
  MsgBox zMsg, vbOKOnly + vbCritical, "Universal Error Trap:"
  
  Print #iFN, zMsg                'Write to Log file
  
  Print #iFN, vbCrLf & "Call Stack Most Recent First:"
  
  iCntr = iStackPointer
  Do
    iCntr = iCntr - 1
    Print #iFN, zCallStack(iCntr)
  Loop Until iCntr = 1
  
'+-------------------------------------------------------------------------+
'| Note: If you change the logic to return to the procedure that caused    |
'|       the error you'll need to create and use a different you'll also   |
'|       have to create logic to determine when it is appropriate to close |
'|       the Log File as follows: !                                        |
'+-------------------------------------------------------------------------+
  
  Select Case lErrorNo
        '*** Specific Case needed for all Non-Fatal errors as determined
        '*** by the programmer!
        
        Case 11
            'Divide by 0 Return to caller
            Print #iFN, "*** Non-Fatal Retruning to Caller ***" & vbCrLf & _
                        "--------------------------------------" & vbCrLf

        Case Else     '*** Handles all Fatal Errors ***
            
            Print #iFN, "*** Fatal Error Exiting Excel ***"
            Close #iFN    '*** Close Error Log File ***

            '*** Reset workspace, close open objects, reset defaults! ***
            ResetWorkSpace

            With Application
               .DisplayAlerts = False  'True if User to be prompted for save!
               .Quit
            End With
           
  End Select   'Case lErrorNo
  
 
End Sub                  'GlobalErrHandler()

'                        +--------------------+                 +----------+
'------------------------|  ResetWorkSpace()  |-----------------| 11/07/14 |
'                        +--------------------+                 +----------+
'Called by  : Every Procedure?!
'Notes      : Code this routine to cleanup your environment for exiting!

Private Sub ResetWorkSpace()

   'Sample items to reset!
   
   Dim oSheet  As Worksheet
   
   With Application
   
       .ScreenUpdating = False
       .DisplayAlerts = False
       .IgnoreRemoteRequests = False
       
       On Error Resume Next
       
       With .ErrorCheckingOptions
           .UnlockedFormulaCells = True
           .InconsistentFormula = True
           .EmptyCellReferences = True
       End With
       
       ActiveWindow.DisplayZeros = True
        
       For Each oSheet In ActiveWorkbook.Sheets  '*** Turn off AutoFilter all Sheets
          oSheet.Select
          If ActiveSheet.AutoFilterMode Then _
            ActiveSheet.AutoFilterMode = False
       Next oSheet

   End With   'Application
   
End Sub                  'ResetWorkSpace()

Code to Test the error handler:

Code:

Option Explicit

Sub Main()

   On Error GoTo PROC_ERR
   PushCallStack "Main"

   SampleErrorTrap   '*** Call routine to generate errors ***
   
PROC_EXIT:
   PopCallStack
   Exit Sub

PROC_ERR:
   GlobalErrHandler
   Resume Next
  
End Sub           'Main()

Sub SampleErrorTrap()

   Dim wkb As Workbook
   
   On Error GoTo PROC_ERR
   PushCallStack "SampleErrorTrap"
   
   'You're VBA Code here! My test code follows:
   
10   ActiveCell.Value = 10
     'Non Fatal Error!
20   ActiveCell.Offset(0, 1).Value = 10 / 0 'Divide by Zero error (11)
     'Fatal Error!
30   Workbooks.Open ("TestWorkBook.xlsm") 'Method: File not found error (1004)
   
PROC_EXIT:
   PopCallStack
   Exit Sub

PROC_ERR:
   GlobalErrHandler
   Resume Next
   
End Sub           'SampleErrorTrap()

Sample output file w/one non-fatal and one fatal error:

Code:

Run Info: 
Path:		G:BEKDocsExcelVBA
File:		VBA - Excel - Instrumented Error Trapping Sample.xlsm
Date/Time:	11/08/14 17:55
-----------------------------------------------

Error @ Line: 20
In Procedure: SampleErrorTrap
Error Number: 11
Description : Division by zero

Call Stack Most Recent First:
SampleErrorTrap
Main
*** Non-Fatal Retruning to Caller ***
--------------------------------------

Error @ Line: 30
In Procedure: SampleErrorTrap
Error Number: 1004
Description : 'TestWorkBook.xlsm' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted.

Call Stack Most Recent First:
SampleErrorTrap
Main
*** Fatal Error Exiting Excel ***

Note: The user gets the same message w/the exception of the Call Stack.
I hope some of you find this useful.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote