|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
NPer Function Errorto pay off a loan. Keep getting the "Run Time 5, Invalid procedure call or argument". The language reference says this error will occur under two conditions: 1. If Rate <= -1 2. If Rate = 0 AND Pmt = 0 The language reference also states through example that the pmt argument should be negative. Cool so far. My code and assumptions are below yet I still get the run time 5 error!!!! Assuming Balance, APR, MFA are dimensioned as Double Assuming inputs are: Balance = 500 APR = 12 MFA = 5 ************************* Rem Capture essential variables Balance = CDbl(txtBal.Text) APR = udAPR.Value / 100# MFA = CDbl(txtMFA.Text) Rem Calculate number of months for fixed amount scenario NumMonths2 = NPer(APR / 12, -MFA, Balance, 0) ************************* This should work, what am I missing here? I'm using VB6 SP6 on Win 2K SP4. The Help on NPer doesn't show that any arguments can be less than zero. Try
making them all positive values. Show quoteHide quote "Corey" wrote: > I'm using the NPer function to display the number of payments it would take > to pay off a loan. Keep getting the "Run Time 5, Invalid procedure call or > argument". The language reference says this error will occur under two > conditions: > 1. If Rate <= -1 > 2. If Rate = 0 AND Pmt = 0 > The language reference also states through example that the pmt argument > should be negative. Cool so far. > > My code and assumptions are below yet I still get the run time 5 error!!!! > Assuming Balance, APR, MFA are dimensioned as Double > Assuming inputs are: > Balance = 500 > APR = 12 > MFA = 5 > ************************* > Rem Capture essential variables > Balance = CDbl(txtBal.Text) > APR = udAPR.Value / 100# > MFA = CDbl(txtMFA.Text) > > Rem Calculate number of months for fixed amount scenario > NumMonths2 = NPer(APR / 12, -MFA, Balance, 0) > > ************************* > This should work, what am I missing here? I'm using VB6 SP6 on Win 2K SP4. > Every help and example that Microsoft uses to demonstrate the NPer function
however places a negative symbol in front of the payment argument in the function. Ex. NPer(APR / 12, -pmt, PV, FV, 0) I have tried what you suggested and it eliminates the error but makes the NPer result negative. Sure I could just use ABS() to make the result positive no matter what however I'm not sure that the financial result would be correct. Any other suggestions? Thanks Show quoteHide quote "Charlie" wrote: > The Help on NPer doesn't show that any arguments can be less than zero. Try > making them all positive values. > > "Corey" wrote: > > > I'm using the NPer function to display the number of payments it would take > > to pay off a loan. Keep getting the "Run Time 5, Invalid procedure call or > > argument". The language reference says this error will occur under two > > conditions: > > 1. If Rate <= -1 > > 2. If Rate = 0 AND Pmt = 0 > > The language reference also states through example that the pmt argument > > should be negative. Cool so far. > > > > My code and assumptions are below yet I still get the run time 5 error!!!! > > Assuming Balance, APR, MFA are dimensioned as Double > > Assuming inputs are: > > Balance = 500 > > APR = 12 > > MFA = 5 > > ************************* > > Rem Capture essential variables > > Balance = CDbl(txtBal.Text) > > APR = udAPR.Value / 100# > > MFA = CDbl(txtMFA.Text) > > > > Rem Calculate number of months for fixed amount scenario > > NumMonths2 = NPer(APR / 12, -MFA, Balance, 0) > > > > ************************* > > This should work, what am I missing here? I'm using VB6 SP6 on Win 2K SP4. > > Hopefully, someone can explain this... There's no error here after setting
"MFA" to a non-whole number. Note that APR needs to be divided by 100 (as shown in help) '======== Dim Balance As Double Dim APR As Double Dim MFA As Double Dim NumMonths2 As Double Balance = 500 APR = 0.12 MFA = 5.00001 NumMonths2 = NPer(APR / 12, -MFA, Balance) '======== Another function I've never used <g> -- Show quoteHide quoteKen Halter - MS-MVP-VB - http://www.vbsight.com DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm Please keep all discussions in the groups.. "Corey" <Co***@discussions.microsoft.com> wrote in message news:B22D2401-1F64-4A3F-894B-256D5920E419@microsoft.com... > I'm using the NPer function to display the number of payments it would > take > to pay off a loan. Keep getting the "Run Time 5, Invalid procedure call > or > argument". The language reference says this error will occur under two > conditions: > 1. If Rate <= -1 > 2. If Rate = 0 AND Pmt = 0 > The language reference also states through example that the pmt argument > should be negative. Cool so far. > > My code and assumptions are below yet I still get the run time 5 error!!!! > Assuming Balance, APR, MFA are dimensioned as Double > Assuming inputs are: > Balance = 500 > APR = 12 > MFA = 5 > ************************* > Rem Capture essential variables > Balance = CDbl(txtBal.Text) > APR = udAPR.Value / 100# > MFA = CDbl(txtMFA.Text) > > Rem Calculate number of months for fixed amount scenario > NumMonths2 = NPer(APR / 12, -MFA, Balance, 0) > > ************************* > This should work, what am I missing here? I'm using VB6 SP6 on Win 2K > SP4. > Ken,
Thanks also for the input. I am dividing the APR by 100 before using it in the function so I can verify that it isn't causing the problem. Relating to my last reply, I verified that setting the payment argument to a positive value eliminates the error but doesn't produce the correct financial result. So this idea is out. Something interesting that I've also identified is that, if the payment argument is greater that 1% (0.01) of the PV then the error doesn't occur but if the payment argument is 1% (0.10) of the PV the error occurs. Maybe this is a limitation of the function itself??? Show quoteHide quote "Ken Halter" wrote: > Hopefully, someone can explain this... There's no error here after setting > "MFA" to a non-whole number. Note that APR needs to be divided by 100 (as > shown in help) > '======== > Dim Balance As Double > Dim APR As Double > Dim MFA As Double > Dim NumMonths2 As Double > > Balance = 500 > APR = 0.12 > MFA = 5.00001 > > NumMonths2 = NPer(APR / 12, -MFA, Balance) > '======== > > Another function I've never used <g> > > -- > Ken Halter - MS-MVP-VB - http://www.vbsight.com > DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm > Please keep all discussions in the groups.. > "Corey" <Co***@discussions.microsoft.com> wrote in message > news:B22D2401-1F64-4A3F-894B-256D5920E419@microsoft.com... > > I'm using the NPer function to display the number of payments it would > > take > > to pay off a loan. Keep getting the "Run Time 5, Invalid procedure call > > or > > argument". The language reference says this error will occur under two > > conditions: > > 1. If Rate <= -1 > > 2. If Rate = 0 AND Pmt = 0 > > The language reference also states through example that the pmt argument > > should be negative. Cool so far. > > > > My code and assumptions are below yet I still get the run time 5 error!!!! > > Assuming Balance, APR, MFA are dimensioned as Double > > Assuming inputs are: > > Balance = 500 > > APR = 12 > > MFA = 5 > > ************************* > > Rem Capture essential variables > > Balance = CDbl(txtBal.Text) > > APR = udAPR.Value / 100# > > MFA = CDbl(txtMFA.Text) > > > > Rem Calculate number of months for fixed amount scenario > > NumMonths2 = NPer(APR / 12, -MFA, Balance, 0) > > > > ************************* > > This should work, what am I missing here? I'm using VB6 SP6 on Win 2K > > SP4. > > > > > > Thanks also for the input. I am dividing the APR by 100 before using argument to ait in > the function so I can verify that it isn't causing the problem. > > Relating to my last reply, I verified that setting the payment > positive value eliminates the error but doesn't produce the correct (0.10) offinancial > result. So this idea is out. Something interesting that I've also > identified is that, if the payment argument is greater that 1% (0.01) of the > PV then the error doesn't occur but if the payment argument is 1% > the PV the error occurs. I don't use this function myself, so this is only a guess on my part.> > Maybe this is a limitation of the function itself??? Floating point values can't represent numbers not based on powers of 2 exactly, so VB returns an approximation. The value 0.01 is one of these values that can't be represented exactly. It is hard to show that with the number itself, but consider these two demonstrations... Debug.Print 123.01 - 123 ==> 1.00000000000051E-02 Debug.Print 234.01 - 234 ==> 9.99999999999091E-03 I suspect that 0.01 is being approximated as a value slightly less than 0.01 and that VB is treating this "smaller than 0.01" value as 0. Remember, this is only a guess. For more information on the floating point representation problem, take a look at these... INFO: Visual Basic and Arithmetic Precision http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1 (Complete) Tutorial to Understand IEEE Floating-Point Errors http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q42/9/80.ASP&NoWebContent=1 What Every Computer Scientist Should Know About Floating Point http://docs.sun.com/source/806-3568/ncg_goldberg.html Rick Problem Solved.
The solution is in the mathematics of the formula itself and I just missed it. In my code I was using a Period Interest Rate of 1% or (.12 / 12) = 0.01. My PV was $500 and my Payment argument was $5. Therefore the interest for each month would have equalled my payment amount causing the balance to never reduce. Therefore FV can never = 0. Doh! Although it would have been nice of Microsoft to include a better error message than a run time. Maybe something along the lines of "You cannot acheive the specified FV with the current arguments. Please adjust and try again." The run time 5 error is obviously very generic but even the tech references to the NPer function show only the two conditions I mentioned in an earlier post to cause the error. Well, this situation can also cause the error and it does not fall into the two specified by MS as seen below. 1. If Rate is less than or = 0, then run time 5 2. If Rate = 0 AND Pmt = 0, then run time 5 (3.) If Rate * PV <= Pmt, then run time 5 A simple series of checks keeps the APR, PV and Payment variables from producing this result again. Thanks for everyone's help and suggestions. Show quoteHide quote "Rick Rothstein" wrote: > > Thanks also for the input. I am dividing the APR by 100 before using > it in > > the function so I can verify that it isn't causing the problem. > > > > Relating to my last reply, I verified that setting the payment > argument to a > > positive value eliminates the error but doesn't produce the correct > financial > > result. So this idea is out. Something interesting that I've also > > identified is that, if the payment argument is greater that 1% (0.01) > of the > > PV then the error doesn't occur but if the payment argument is 1% > (0.10) of > > the PV the error occurs. > > > > Maybe this is a limitation of the function itself??? > > I don't use this function myself, so this is only a guess on my part. > Floating point values can't represent numbers not based on powers of 2 > exactly, so VB returns an approximation. The value 0.01 is one of these > values that can't be represented exactly. It is hard to show that with > the number itself, but consider these two demonstrations... > > Debug.Print 123.01 - 123 ==> 1.00000000000051E-02 > Debug.Print 234.01 - 234 ==> 9.99999999999091E-03 > > I suspect that 0.01 is being approximated as a value slightly less than > 0.01 and that VB is treating this "smaller than 0.01" value as 0. > Remember, this is only a guess. > > For more information on the floating point representation problem, take > a look at these... > > INFO: Visual Basic and Arithmetic Precision > http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1 > > (Complete) Tutorial to Understand IEEE Floating-Point Errors > http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q42/9/80.ASP&NoWebContent=1 > > What Every Computer Scientist Should Know About Floating Point > http://docs.sun.com/source/806-3568/ncg_goldberg.html > > Rick > > >
Show quote
Hide quote
"Corey" <Co***@discussions.microsoft.com> wrote in message Would it be a better world if error messages actually told people what the news:8595B797-5C34-48D5-8ADB-B6E7672D7A25@microsoft.com... > Problem Solved. > > The solution is in the mathematics of the formula itself and I just missed > it. In my code I was using a Period Interest Rate of 1% or (.12 / 12) = > 0.01. My PV was $500 and my Payment argument was $5. Therefore the > interest > for each month would have equalled my payment amount causing the balance > to > never reduce. Therefore FV can never = 0. Doh! Although it would have > been > nice of Microsoft to include a better error message than a run time. > Maybe > something along the lines of "You cannot acheive the specified FV with the > current arguments. Please adjust and try again." The run time 5 error is > obviously very generic but even the tech references to the NPer function > show > only the two conditions I mentioned in an earlier post to cause the error. > Well, this situation can also cause the error and it does not fall into > the > two specified by MS as seen below. > 1. If Rate is less than or = 0, then run time 5 > 2. If Rate = 0 AND Pmt = 0, then run time 5 > (3.) If Rate * PV <= Pmt, then run time 5 > > A simple series of checks keeps the APR, PV and Payment variables from > producing this result again. > > Thanks for everyone's help and suggestions. real problem is? <g> My favorites are Error 429 "activex can't create component" (or something like that)... thing is, VB knows exactly which component is causing trouble but leaves it up to you to find out which one it is... another killer is (and, even worse) Error 48 "error loading DLL". That can be a *bear* to track down... the OS/VB knows exactly which dll is causing the problem but keeps it a secret. Error 5's cause enough confusion on their own. Things like setting focus to a control in Form_Load will raise an error 5. If the message would say "you can't set focus to Text1 because it's not visible and/or enabled", there'd be no confusion. -- Ken Halter - MS-MVP-VB - http://www.vbsight.com DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm Please keep all discussions in the groups.. I agree whole heartedly.
Thanks again Show quoteHide quote "Ken Halter" wrote: > "Corey" <Co***@discussions.microsoft.com> wrote in message > news:8595B797-5C34-48D5-8ADB-B6E7672D7A25@microsoft.com... > > Problem Solved. > > > > The solution is in the mathematics of the formula itself and I just missed > > it. In my code I was using a Period Interest Rate of 1% or (.12 / 12) = > > 0.01. My PV was $500 and my Payment argument was $5. Therefore the > > interest > > for each month would have equalled my payment amount causing the balance > > to > > never reduce. Therefore FV can never = 0. Doh! Although it would have > > been > > nice of Microsoft to include a better error message than a run time. > > Maybe > > something along the lines of "You cannot acheive the specified FV with the > > current arguments. Please adjust and try again." The run time 5 error is > > obviously very generic but even the tech references to the NPer function > > show > > only the two conditions I mentioned in an earlier post to cause the error. > > Well, this situation can also cause the error and it does not fall into > > the > > two specified by MS as seen below. > > 1. If Rate is less than or = 0, then run time 5 > > 2. If Rate = 0 AND Pmt = 0, then run time 5 > > (3.) If Rate * PV <= Pmt, then run time 5 > > > > A simple series of checks keeps the APR, PV and Payment variables from > > producing this result again. > > > > Thanks for everyone's help and suggestions. > > Would it be a better world if error messages actually told people what the > real problem is? <g> My favorites are Error 429 "activex can't create > component" (or something like that)... thing is, VB knows exactly which > component is causing trouble but leaves it up to you to find out which one > it is... another killer is (and, even worse) Error 48 "error loading DLL". > That can be a *bear* to track down... the OS/VB knows exactly which dll is > causing the problem but keeps it a secret. Error 5's cause enough confusion > on their own. Things like setting focus to a control in Form_Load will raise > an error 5. If the message would say "you can't set focus to Text1 because > it's not visible and/or enabled", there'd be no confusion. > > -- > Ken Halter - MS-MVP-VB - http://www.vbsight.com > DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm > Please keep all discussions in the groups.. > > >
Show quote
Hide quote
"Ken Halter" <Ken_Halter@Use_Sparingly_Hotmail.com> wrote in message I agree with you that it would be nice to always receive completenews:%23GgM3o%23gFHA.1248@TK2MSFTNGP12.phx.gbl... > "Corey" <Co***@discussions.microsoft.com> wrote in message > news:8595B797-5C34-48D5-8ADB-B6E7672D7A25@microsoft.com... > > Problem Solved. > > > > The solution is in the mathematics of the formula itself and I just missed > > it. In my code I was using a Period Interest Rate of 1% or (.12 / 12) = > > 0.01. My PV was $500 and my Payment argument was $5. Therefore the > > interest > > for each month would have equalled my payment amount causing the balance > > to > > never reduce. Therefore FV can never = 0. Doh! Although it would have > > been > > nice of Microsoft to include a better error message than a run time. > > Maybe > > something along the lines of "You cannot acheive the specified FV with the > > current arguments. Please adjust and try again." The run time 5 error is > > obviously very generic but even the tech references to the NPer function > > show > > only the two conditions I mentioned in an earlier post to cause the error. > > Well, this situation can also cause the error and it does not fall into > > the > > two specified by MS as seen below. > > 1. If Rate is less than or = 0, then run time 5 > > 2. If Rate = 0 AND Pmt = 0, then run time 5 > > (3.) If Rate * PV <= Pmt, then run time 5 > > > > A simple series of checks keeps the APR, PV and Payment variables from > > producing this result again. > > > > Thanks for everyone's help and suggestions. > > Would it be a better world if error messages actually told people what the > real problem is? <g> My favorites are Error 429 "activex can't create > component" (or something like that)... thing is, VB knows exactly which > component is causing trouble but leaves it up to you to find out which one > it is... another killer is (and, even worse) Error 48 "error loading DLL". > That can be a *bear* to track down... the OS/VB knows exactly which dll is > causing the problem but keeps it a secret. Error 5's cause enough confusion > on their own. Things like setting focus to a control in Form_Load will raise > an error 5. If the message would say "you can't set focus to Text1 because > it's not visible and/or enabled", there'd be no confusion. > > -- > Ken Halter - MS-MVP-VB - http://www.vbsight.com > DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm > Please keep all discussions in the groups.. > > information on all errors, but ... Actually the OS/VB *DOES NOT* know exactly which dll is causing the problem. It goes back to the basic Windows Loader since time began (Window's time anyway). Each component often depends on another component which you can examine using DumpBin. As the Loader loads each component, fumbling its way down through the hierarchy, it returns a 'Yea' or 'Nay' for each step. That's it. Period. The second it catches a 'No Vote' it abandons ship in total panic. It has no clue where its been and only partially aware of why he was on that ship in the first place. The Unix Loader keeps a nice little journal of his travels. So does just about every other OS ever developed, but not the Windows Loader. You would think that an OS that was so utterly dependent on a myriad of diverse dynamically linked componentswould keep better records. I think the original reason the Loader was designed so lite was because in the early years with 386s and enhanced/extended memory there was so much thunking going on it was consider an optimization to skip that extra checking. Or perhaps it was just because Windows has DOS roots and its loader didn't need to count beyond '1'. <g> -ralph That's weird. I guess it shouldn't surprise me.
Show quoteHide quote "Ken Halter" wrote: > Hopefully, someone can explain this... There's no error here after setting > "MFA" to a non-whole number. Note that APR needs to be divided by 100 (as > shown in help) > '======== > Dim Balance As Double > Dim APR As Double > Dim MFA As Double > Dim NumMonths2 As Double > > Balance = 500 > APR = 0.12 > MFA = 5.00001 > > NumMonths2 = NPer(APR / 12, -MFA, Balance) > '======== > > Another function I've never used <g> > > -- > Ken Halter - MS-MVP-VB - http://www.vbsight.com > DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm > Please keep all discussions in the groups.. > "Corey" <Co***@discussions.microsoft.com> wrote in message > news:B22D2401-1F64-4A3F-894B-256D5920E419@microsoft.com... > > I'm using the NPer function to display the number of payments it would > > take > > to pay off a loan. Keep getting the "Run Time 5, Invalid procedure call > > or > > argument". The language reference says this error will occur under two > > conditions: > > 1. If Rate <= -1 > > 2. If Rate = 0 AND Pmt = 0 > > The language reference also states through example that the pmt argument > > should be negative. Cool so far. > > > > My code and assumptions are below yet I still get the run time 5 error!!!! > > Assuming Balance, APR, MFA are dimensioned as Double > > Assuming inputs are: > > Balance = 500 > > APR = 12 > > MFA = 5 > > ************************* > > Rem Capture essential variables > > Balance = CDbl(txtBal.Text) > > APR = udAPR.Value / 100# > > MFA = CDbl(txtMFA.Text) > > > > Rem Calculate number of months for fixed amount scenario > > NumMonths2 = NPer(APR / 12, -MFA, Balance, 0) > > > > ************************* > > This should work, what am I missing here? I'm using VB6 SP6 on Win 2K > > SP4. > > > > > |
|||||||||||||||||||||||
Other interesting topics