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
SowleManRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you post one of the subs where this happens?
Zack SoderquistCommented:
Check your code for "On Error Goto 0" in a subroutine  .. this will reset your error handling back to default.  Also make sure your "on error" logic has "Resume Next"

Example:

On Error GoTo ErrorHandler:

Dim TestVar As Variant

TestVar = 10 / 0

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zack SoderquistCommented:
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.
SowleManRetiredAuthor Commented:
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 SoderquistCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.