Can't trap Run-time error 5

Posted on 2009-04-18
Medium Priority
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
  • 3
  • 2
  • 2

Author Comment

ID: 24177219
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 93

Expert Comment

by:Patrick Matthews
ID: 24177343
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
ID: 24177345

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 (http://www.experts-exchange.com/M_62754.html) once said:
 "The best Error handler is writing code that avoids most errors to begin with"


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 24177672

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
Jeffrey Coachman earned 1000 total points
ID: 24177935

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

DaveRoehrman earned 0 total points
ID: 24178808
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
ID: 24249810

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

850 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