Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts

    How to create log file when runtime error occurs?

    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?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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: CONST ProcName = "Current Proc Name" that can then be used in your error handler to report the offending code.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:\BEKDocs\ErrLog.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:
    Attachment 40237
    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
    Last edited by RetiredGeek; 2014-11-04 at 18:01.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It's easier if you use line numbers in your code - then you can simply use the erl function.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2014-11-05)

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks, I never thought of that as I'm not used to using line numbers. Great Idea!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:\BEKDocs\ErrLog.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:
    instrumenterrormsg.JPG
    Sample Log File output:
    Code:
    Untrapped Error @ Line: 30
    In Procedure: SampleErrorTrap
    Error: 11
    Description: Division by zero
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    YigalB (2014-11-05)

  10. #8
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks a lot!
    I will try it and report back.

  11. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:\BEKDocs\Transfer\ErrLog-" & 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:\BEKDocs\Excel\VBA
    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.

    Last edited by RetiredGeek; 2014-11-08 at 17:58.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •