Link to home
Start Free TrialLog in
Avatar of DaveRoehrman
DaveRoehrmanFlag for United States of America

asked on

Can't trap Run-time error 5

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
     .
ProceedWithRoutine:
     .
     .
     .
ErrorHandler:
    Select Case Err.Number
        Case 5
                IRRValue = 0
                Resume ProceedWithRoutine
    End Select
Exit Sub

Open in new window

Avatar of DaveRoehrman
DaveRoehrman
Flag of United States of America image

ASKER

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
HereAfterError:
                IRRValue = 0
SkipErrorHandler:

Open in new window

Avatar of 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.

Regards,

Patrick
DaveRoehrman,

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_cmdOK_Click:
    Exit Sub

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

JeffCoachman
SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial