DaveRoehrman
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?
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
;-)
ASKER
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?
Open in new window