asked on

Why don't errors get trapped by my "on error go to..." logic?

I have a MS Access database and have "on error" routines for every VBA function, subroutine and event. Still, I get errors that pop up like "Invalid use of null" or "overflow error" that do not fire the "on error" logic. Why not?

I have a routine that runs monthly, importing Excel spreadsheets provided by banks and credit companies. Each record entails quite a bit of logic, so the routine is all VBA. Without the "on error" logic firing, ACCESS doesn't tell you where the error occurred, so debugging is difficult. There are too many lines of VBA and too many iterations of those lines of VBA to step-thru.

Suggestions?

Steve

Martin Liss

Can you post one of the subs where this happens?

ASKER CERTIFIED SOLUTION

Zack Soderquist

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zack Soderquist

I noticed you said .... "on error" routines for every VBA function, subroutine and event.

Unless every function and subroutine needs to be treated differently.. this is not necessary.

A function or subroutine will use the same error handling as the subroutine or function that calls it if a different one is not specified.

In my previous example, My error handler calls a subroutine that writes the error to a log file and then continues on. If I put this in the main subroutine/function that you call to start the macro, then all subroutines and functions called after that will use the one error handler.

SowleMan

ASKER

Thanks, Mr. Soderquist. I learned a few things in your reply. Don't know if this is within protocol, but would you please send me the logic that produces your error log. That seems like a pretty cool process I can use often.
Thanks
Steve

Zack Soderquist

Here is a sample of the logic i use in my error handling

Option Explicit

Public Const WORKBOOKFILENAME As String = "XYZReport.xlsm"

Public strErrorLogDirectory As String
Public strErrorLogFileName  As String
Public strErrorLogFullPath As String

Public strErrStack As String

Public Sub UnitTest_WriteToErrorLog()
'
' Purpose:  Test the WriteToErrorLog Subroutine
' Details:  Initialize error handling, cause an error, throw a custom error
'

'NOTE: about the strErrStack variable. I use this to track where the macro is at when the error is thrown
'      and it is written as part of the error log entry. This makes it easier to track down errors and bugs
'      this Sub starts the strErrStack string and the other subs append to it.
'      To keep the strErrStack variable reasonable in size, I reset it at different and logical points in
'      my macros.

    'Error Handling
    On Error GoTo ErrorHandler:
    strErrStack = "UnitTest_WriteToErrorLog"

    SetErrorFileInfo
    CreateDivError
    ThrowError
    
Exit Sub

'Error Handler
ErrorHandler:
    Call WriteToErrorLog(Err.Number, Err.Description, strErrStack)
    Err.Clear
    Resume Next

End Sub

Private Sub SetErrorFileInfo()
'
' Purpose:  Set File Name and Location
' Details:  Set the location and name of the error log
'
    
    'Error Handling
    strErrStack = strErrStack & "." & "SetErrorFileInfo"

    strErrorLogDirectory = "C:\logs\"
    strErrorLogFileName = "XYZReport-ErrorLog.csv"
    strErrorLogFullPath = strErrorLogDirectory & strErrorLogFileName
End Sub


Private Sub CreateDivError()
'
' Purpose:  Force a Division Error
' Details:  Cause excel to generate a divide by zero error
'
    
    'Error Handling
    strErrStack = strErrStack & "." & "CreateDivError"
    
    Dim TestVar As Variant
    TestVar = 100 / 0
End Sub

Private Sub ThrowError()
'
' Purpose:  Throw custom error
' Details:  Use the following to call your own error, or write an event to the error log.
'           This is great for tracking an event that doesn't create an error
'
    
    'Error Handling
    strErrStack = strErrStack & "." & "ThrowError"
    
    Call Err.Raise(50000, "MyError", "Write Custome Error to Log")
End Sub

Private Sub WriteToErrorLog(intErrNum As Long, strErrDesc As String, Optional ByVal strErrStack As String = "Unknown")
'
' Purpose:  Capture errors
' Details:  Capture errors and write to an error log
'
    
    Dim datTimeStamp As Date
    datTimeStamp = Now()
    
    'Create error string
    Dim strErrorLine As String
    strErrorLine = """" & datTimeStamp & """,""" & intErrNum & """,""" & strErrDesc & """,""" & WORKBOOKFILENAME & """,""" & strErrStack & """"
    
    'Write to error log
    Open strErrorLogFullPath For Append As #1
    Print #1, strErrorLine
    Close #1

End Sub

Open in new window

Why don't errors get trapped by my &quot;on error go to...&quot; logic?

Why don't errors get trapped by my "on error go to..." logic?