Can't trap Run-time error 5

Posted on 2009-04-18
Last Modified: 2013-11-27
My first attempt to use an error trapping routine, almost verbatim out of the "Access 2007 Bible".  Instead of the error being being trapped when the IRR calculation fails, the line of code is highlighted and the standard Access error message box appears with "Run-time error '5':  Invalid procedure call or argument."  The error handler routine is never entered.

Why is the error not directed to the "ErrorHandler" routine?
Dim statements


On Error GoTo ErrorHandler




Code here         Error 5 occurs here with an IRR calculation: code halts with Access msgbox







    Select Case Err.Number

        Case 5

                IRRValue = 0

                Resume ProceedWithRoutine

    End Select

Exit Sub

Open in new window

Question by:DaveRoehrman

    Author Comment

    Developments since that issue....

    I realized that I had the On Error statement before a loop, so it trapped only one error.

    Now the On Error is inside the loop, so it should trap one error per loop iteration, right?

    It doesn't even trap one error now.

    What am I doing wrong?
    On Error GoTo HereAfterError
            IRRValue = IRR(ValueOn(), Guess) * DaysInvestedLastQtr
            Debug.Print "IRRValue = " & IRRValue;
    GoTo SkipErrorHandler
                    IRRValue = 0

    Open in new window

    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello DaveRoehrman,

    Two things:

    1) It would be helpful if you could post a smallish "test" database with "fake" data (or real data if it is not too
    sensitive to post), that includes the full code, and in which you can describe the results you want to get and
    how they differ from what you do get.

    2) I urge you to be very careful in using internal rates of return.  IRR--the finance construct itself, and not just
    the Excel functions IRR(), XIRR(), and MIRR(), or IRR functions in other apps--is, simply put, not reliable, and
    prioritizing investment decisions on an IRR basis can lead to bad decision-making.  You should use net present
    value (NPV) instead.

    a) Depending on the nature of the cash flows, there can actually be more than one IRR.  (The number of IRR
    results will match the number of times the stream of cash flows changes signs.)

    b) Some scenarios can have very high IRR but actually have negative NPV using any rational discount rate.

    Illustrating both...  Consider the following cash flows:

    Today, -1,600
    1 year from now, 10,000
    2 yrs from now, -10,000

    This stream actually has two IRRs: 25%, and 400%, both of which sure look attractive.  Yet,
    any reasonable discount yields a negative NPV.

    Bottom line: trust NPV, and eschew IRR.


    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Seeing the *Actual* code would help.

    Your error handler only handles one Case?
    Even then, it sends it to the mysterious "ProceedWithRoutine" code?

    The bottom line here is that an error handler exists so that when your code encounters an error, it can exit gracefully.
    For the record, here is a generic standard error handler that Access generates in Access 2003:

    On Error GoTo Err_cmdOK_Click

    ********** Your Code Goes Here **********

        Exit Sub

        MsgBox "There was an error executing the command." _
        & vbCrLf & "Error " & Err.Number & ": " _
        & vbCrLf & Error, vbExclamation
        Resume Exit_cmdOK_Click

    You claim that this error handler comes directly from the Access 2007 Bible?
    I have never seen an error handler quite like that.

    The other issue here is WHY you are getting the error in the first place.
    You need to explain this in detail.

    In a nutshell, your goal should be to write your code in such a way as to avoid getting errors in the first place.
    (Ex.: using the Nz() function to avoid the "Invalid use or Null" error)

    A wise Expert here ( once said:
     "The best Error handler is writing code that avoids most errors to begin with"

    LVL 92

    Assisted Solution

    by:Patrick Matthews

    Agreed generally about heading off possible errors being better than just handling them, but there are times
    when it is expedient to simply let an error happen, and then trap it when it does.  The classic case in Excel
    is to test whether a sheet exists or a workbook is open.  Yes, you could just enumerate the collection, but
    it is more direct to just try and assign an object variable, and it is fails, you know it doesn't exist/is closed.

    BTW, did you get my personal note from Thursday night?  :)


    LVL 74

    Assisted Solution

    by:Jeffrey Coachman

    Sorry, I meant to stress that my post was a General rule.

    It's just that I have seen people (me included) write code to trap errors instead of writing code to prevent them.

    Also I had not seen that style of error handler used that often.

    Finally, without any actual Code to provide context to the issue, I was at a loss for any specific advice.

    But yes you have a good point.
    As in when we trap error 2501 in the "Calling" sub, when the NoData Event triggers in a report, and cancels the Open event (generating the error).

    I have worked with the asker on a previous question.
    I have reviewed some of his previous questions.
    He generally:
    - Asks well thought out questions
    - Will apply suggestion without needing much hand-holding
    - Will do independent research.
    - And most importantly, provide quality feedback to the Expert.

    So my hope is that he took it as a general rule, not as a criticism of his code.


    <Off topic>
    Yes, I got your note, ...and after the initial shock, ...and thoughts that I might be being "Punked", or some that this was some late April Fools joke, ...I started writing you a reply.
    Look for it tomorrow.
    Thanks again!

    </Off Topic>

    Accepted Solution

    Thanks for the votes of confidence, Patrick & Jeff.  Justification AND encouragement from the experts; who could ask for anything more?  I'll attempt to maintain the ethic.  I greatly value your(pl) discussion and guidance.

    My objective is to reconstruct in Access 2007 what my employer had painstakingly built in Excel in conjunction with Advisor's Assistant some 6 or more years ago, thereby compressing weeks of manual entry into a few seconds of run time.  The Excel file takes actual (quarterly or annual) Beginning Value, Ending Value, and intervening Transactions, and kneads them into the IRR argument format, including Guess, to obtain the ROR for a periodic report to investors.  He had AA pass parameters to a complex series of tables to come up with an (understandably wild shot at) IRR, then return this result to AA.  Once I get to the point of duplicating the results from the old method ,thereby establishing credence with the owner, I can proceed with "improvements" to offer as alternatives, seriously considering NPV.

    I'm working with aboutr 600 records, and 98% of them return valid IRR values from this procedure as written.  However, there are the few that IRR() is unable to digest for (whatever various) reasons.  I had been hard coding their exclusion in an If-Then, just to get through it, but of course I have to rewrite it every time a random account is added or deleted.  I would like to streamline this function by ignoring (and later listing for evaluation) the occasions of IRR() failure (after 20 passes,  I understand).

    My first attempt at error trapping was as close to literal Access 2007 Bible text as I thought workable, but I'll admit experimenting with it after initial failures.  The issue has boiled down to one 2-part question, for which I will offer a fair split of points and move on:

    Is it correct application of error trapping to catch REPEATED errors within a loop (in general), and from IRR() ( in particular), resetting the trap within the loop?  If so, why doesn't my (simple) code work?
    I included the entire Procedure which works as written before adding the error handler.  You'll find all the trap setting and handling code about 80% down in the code window:
        'Exclude known offenders
        If Loopcount <> 382 And Loopcount <> 417 And Loopcount <> 429 And Loopcount <> 435 Then
                'Attempt to skip over Accounts that fail IRR()
                On Error GoTo HereAfterError

            IRRValue = IRR(ValueOn(), Guess) * DaysInvestedLastQtr
            Debug.Print "IRRValue = " & IRRValue;
    GoTo SkipErrorHandler
    HereAfterError:                        'This instruction is never executed
                    IRRValue = 0

            rs2![YTD1Q09IROR] = IRRValue

    Private Sub IRRCalc_Click()
    Dim rs2 As ADODB.Recordset
    Dim rs3 As ADODB.Recordset
    Dim TransactionsStatement As String
    Dim AccountsStatement As String
    Dim AdditionalValue As Double
    Dim TradeDay As Integer
    Dim Loopcount As Integer
    Dim TLoopcount As Integer
    Dim AccountNumber As String
    Dim IRRValue As Double
    Dim Guess As Double
    Dim X As Integer
    Dim DaysLastQtr As Integer
    DaysLastQtr = 90
    Dim DaysLastQtrYTD As Integer
    DaysLastQtrYTD = 90
    Dim DayCounter As Integer
    Dim FirstDayOfQtr As Integer
    FirstDayOfQtr = 1
    Dim LastDayOfQtr As Integer
    LastDayOfQtr = 90
    Dim CumulativeQtrValue As Double
    Dim DaysInvestedLastQtr As Integer
    Dim DaysThisYear As Integer
    If Year(Date) Mod 4 = 0 Then
        DaysThisYear = 366
        DaysThisYear = 365
    End If
    'Dim ValueOn() As Double
    ReDim ValueOn(DaysThisYear) As Double
        'Obtain all Accounts
        AccountsStatement = "SELECT [Account#], [12/31/08 Value], [3/31/09 Value], [YTD1Q09IROR] " _
        & "FROM tbAccounts " _
        & "WHERE [12/31/08 Value] Is Not Null AND [3/31/09 Value] AND InvestorTypeAbbrev <> 'NON' AND InvestorTypeAbbrev <> 'N/A' ;"
        Set rs2 = New ADODB.Recordset
        rs2.ActiveConnection = CurrentProject.Connection
        rs2.Source = AccountsStatement
        rs2.CursorType = adOpenStatic
        rs2.LockType = adLockOptimistic
            'Step through Accounts
            Debug.Print "Accounts = " & rs2.RecordCount
            Loopcount = 0
            Do Until rs2.EOF
                Loopcount = Loopcount + 1
                'Get the next account number
                AccountNumber = rs2![Account#]
                TransactionsStatement = "SELECT [Account#], [TradeDate], [TradeAmount] " _
                & "FROM tbTransactions " _
                & "INNER JOIN tbAccounts ON tbTransactions.[SelectAnAccount] = tbAccounts.[Account#] " _
                & "WHERE [SelectAnAccount] = '" & AccountNumber & "' ;"
                'WHERE ((#12/31/2008#<=[TradeDate]<=#3/31/2009#));     'Add to speed up process if necessary
                Set rs3 = New ADODB.Recordset
                rs3.ActiveConnection = CurrentProject.Connection
                rs3.Source = TransactionsStatement
                rs3.CursorType = adOpenStatic
                rs3.LockType = adLockOptimistic
                'Step through Transactions for this Account, accumulating values for each day in the array
                Debug.Print "----------------------------------------"
                Debug.Print "Xactions for Account # " & rs2![Account#] & " : " & rs3.RecordCount
                'Debug.Print "Loopcount & Account# = " & Loopcount & " - " & rs3![Account#]
                TLoopcount = 0
                Do Until rs3.EOF
                    TLoopcount = TLoopcount + 1
                    Debug.Print "Loopcount/Account # " & Loopcount & "   " & rs2![Account#]
                    Debug.Print "Transaction # " & TLoopcount
                    Debug.Print "Trade Date = " & rs3!TradeDate
                    TradeDay = DatePart("y", rs3("TradeDate"))
                    Debug.Print "TradeDay = " & TradeDay
                    Debug.Print "TradeAmount = " & rs3!TradeAmount
                    AdditionalValue = rs3!TradeAmount
                    Debug.Print "AdditionalValue = " & AdditionalValue
                    ValueOn(TradeDay) = ValueOn(TradeDay) + AdditionalValue
                    If ValueOn(TradeDay) <> 0 Then
                        Debug.Print "Value On Day = " & TradeDay & " was " & ValueOn(TradeDay)
                    End If
                    For X = FirstDayOfQtr - 1 To LastDayOfQtr
                        Debug.Print "Day " & X & "   " & ValueOn(X)
                    Next X
                    If TLoopcount >= 17 Then
                        Debug.Print "TLoopcount = " & TLoopcount
                    End If
            Set rs3 = Nothing
            'Set the beginning value (must be negative to make IRR() work)
            'Was there money in the account at the beginning of the quarter?
            DaysInvestedLastQtr = 0
            If rs2![12/31/08 Value] <> 0 Then
                DaysInvestedLastQtr = DaysLastQtr
                ValueOn(FirstDayOfQtr - 1) = rs2![12/31/08 Value] * (-1)      'If so, make it negative
                Debug.Print "ValueOn(0) =         " & ValueOn(0)
            'Otherwise determine the first day money was deposited and
            'Find number of days invested this quarter
                For DayCounter = FirstDayOfQtr - 1 To LastDayOfQtr
                    If ValueOn(DayCounter) <> 0 Then
                        ValueOn(DayCounter) = ValueOn(DayCounter) * (-1)    '...and make this value negative
                        Debug.Print "ValueOn Day " & DayCounter & " = " & ValueOn(DayCounter) & " (should be neg)"
                        DaysInvestedLastQtr = DaysLastQtr - DayCounter
                        GoTo Job2
                    End If
            End If
            Debug.Print "DaysInvestedLastQtr = " & DaysInvestedLastQtr
            'Set the end value
            Debug.Print "ValueOn(LastDayOfQtr) pre   = " & ValueOn(LastDayOfQtr)
            ValueOn(LastDayOfQtr) = ValueOn(LastDayOfQtr) + rs2![3/31/09 Value]
            Debug.Print "ValueOn(LastDayOfQtr) post = " & ValueOn(LastDayOfQtr)
            Debug.Print "Values immediately before accumulation............"
                    For X = FirstDayOfQtr - 1 To LastDayOfQtr
                        Debug.Print "Day " & X & "   " & ValueOn(X)
                    Next X
            'Prepare for last quarter calculation:
                'Formulate the Guess
                    'and determine the sign of the Guess
            CumulativeQtrValue = 0
            For DayCounter = FirstDayOfQtr - 1 To LastDayOfQtr
                CumulativeQtrValue = CumulativeQtrValue + ValueOn(DayCounter)
                Debug.Print "CumulativeQtrValue after Day " & DayCounter; " is " & CumulativeQtrValue
            Debug.Print "CumulativeQtrValue = " & CumulativeQtrValue
            If CumulativeQtrValue < 0 Then
                Guess = (-0.1 / DaysLastQtrYTD)
            Else: Guess = (0.1 / DaysLastQtrYTD)
            End If
            Debug.Print "Guess = " & Guess
            'Make the calculation, place the figure into the account record, and move to the next account
            Debug.Print "Loopcount = " & Loopcount
            Debug.Print "Account# " & rs2![Account#]
            rs2![YTD1Q09IROR] = 0
        If Loopcount <> 382 And Loopcount <> 417 And Loopcount <> 429 And Loopcount <> 435 Then
                On Error GoTo HereAfterError
            IRRValue = IRR(ValueOn(), Guess) * DaysInvestedLastQtr
            Debug.Print "IRRValue = " & IRRValue;
    GoTo SkipErrorHandler
                    IRRValue = 0
            rs2![YTD1Q09IROR] = IRRValue
            Debug.Print "rs2![YTD1Q09IROR] = " & rs2![YTD1Q09IROR]
        Else: Debug.Print "_______________ Problem account _________________"
        End If
            For X = 0 To 90   '(Print the array values during development)
                If ValueOn(X) <> 0 Then Debug.Print ValueOn(X), X
            Next X
            'Prepare for next account by clearing the array
            For X = 0 To 90
                ValueOn(X) = 0
            Next X
            'Update the current Account
            Debug.Print "rs2![YTD1Q09IROR] = " & rs2![YTD1Q09IROR]
            'Move to the next account
            Me!ProgressIRR = Str(Loopcount)
            'Close the Account recordset
            Set rs2 = Nothing
    End Sub

    Open in new window

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now