VBA bug

[wycyobm]

Hi, I am trying to run my code but it stops at one point and it says invalid procedure call or argument.
Does anyone knows where is the problem? Thanks!


'Calculation of the annual payment
annualPmnt = Pmt(intRate, loanLife, -initLoanAmnt, , 0)
'Initialize beginning balance of the 1st year
yrBegBal = initLoanAmnt
'Loop to calculate and output year-by-year amortization table
For rowNum = 1 To loanLife
intComp = yeBegBal * intRate
princRepay = annualPmnt = intComp
yrEndBal = yrBegBal = princRepay
Cells(outRow + rowNum + 3, 3).Value = rowNum 'this is the year number
Cells(outRow + rowNum + 3, 4).Value = yeBegBal
Cells(outRow + rowNum + 3, 5).Value = annualPmnt
Cells(outRow + rowNum + 3, 6).Value = intComp
Cells(outRow + rowNum + 3, 7).Value = princRepay
Cells(outRow + rowNum + 3, 8).Value = yrEndBal
yrBegBal = yrEndBal

 

[Rick Rothstein]

It is on my 2010 version Rick.

Well look at that... it is on my version as well.

Goes to show you how much I use VB's built-in statistics' functions.

Hey wycyobm, sorry for any confusion I may have caused to you... looks like BarryL might be onto something in Message #10.

 

[steve the fish]

It seems to be fine at coercing strings into numbers too. The only way ive managed to get that error is when loanLife is zero or empty.

 

[wycyobm]

Hi, this is my full code.

'-----Declaration of Variables-----
Dim initLoanAmnt, intRate, loanLife, yrBegBal, yrEndBal
Dim annualPmnt, intComp, princRepay
Dim outRow, rowNum, outSheet
'***********************************************
'Inputs of the programmer
'***********************************************
outRow = 5 'this is used to decide where the output table will start
outSheet = "Loan Amort"
Worksheets(outSheet).Activate
Rows(outRow + 3 & ":" & outRow + 100).Select
Selection.Clear
'*************************************************
'Inputs of the user
'************************************************
'The program will read here the inputs that the user
'provides in the worksheet.
intRate = Cells(2, 2).Value
loanLife = Cells(3, 2).Value
initLoanAmnt = Cells(4, 2).Value
'Here where make sure that the interest
'does not exceed 15%
If intRate > 0.15 Then
MsgBox "Interest rate cannot be greater than 15%"
End
End If
'********************************************
'Compute and output results
'********************************************
'Calculation of the annual payment
annualPmnt = Pmt(intRate, loanLife, -initLoanAmnt, , 0)
'Initialize beginning balance of the 1st year
yrBegBal = initLoanAmnt
'Loop to calculate and output year-by-year amortization table
For rowNum = 1 To loanLife
intComp = yeBegBal * intRate
princRepay = annualPmnt = intComp
yrEndBal = yrBegBal = princRepay
Cells(outRow + rowNum + 3, 3).Value = rowNum 'this is the year number
Cells(outRow + rowNum + 3, 4).Value = yeBegBal
Cells(outRow + rowNum + 3, 5).Value = annualPmnt
Cells(outRow + rowNum + 3, 6).Value = intComp
Cells(outRow + rowNum + 3, 7).Value = princRepay
Cells(outRow + rowNum + 3, 8).Value = yrEndBal
yrBegBal = yrEndBal
Next rowNum
'*****************************************
'Format the output data in the table
'******************************************
Range(Cells(outRow + 4, 4), Cells(outRow + loanLife + 3, 8) _
).Select
Selection.NumberFormat = "$#,##0"

And this is what is on the worksheet

[TABLE="width: 308"]
<colgroup><col style="width: 162pt; mso-width-source: userset; mso-width-alt: 6912;" width="216"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3040;" width="95"> <col style="width: 75pt; mso-width-source: userset; mso-width-alt: 3200;" width="100"> <tbody>[TR]
[TD="class: xl63, width: 411, bgcolor: transparent, colspan: 3"]Loan Amortization Table using the PMT function[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Input Data[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Interest rate in % per year[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loan life[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Initial loan amount[/TD]
[TD="class: xl65, bgcolor: transparent"] £ 14,000.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Annual payment[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Works fine for me excepting for the fact you have got some of the names of your variables wrong.

 

[wycyobm]

Works fine for me excepting for the fact you have got some of the names of your variables wrong.

Hi, which parts are wrong?

 

[steve the fish]

Oh and you have = signs where you need negative signs. The errors where in this part:

'Calculation of the annual payment
annualPmnt = Pmt(intRate, loanLife, -initLoanAmnt, , 0)
'Initialize beginning balance of the 1st year
yrBegBal = initLoanAmnt
'Loop to calculate and output year-by-year amortization table
For rowNum = 1 To loanLife
    intComp = yrBegBal * intRate
    princRepay = annualPmnt - intComp
    yrEndBal = yrBegBal - princRepay
    Cells(outRow + rowNum + 3, 3).Value = rowNum 'this is the year number
    Cells(outRow + rowNum + 3, 4).Value = yrBegBal
    Cells(outRow + rowNum + 3, 5).Value = annualPmnt
    Cells(outRow + rowNum + 3, 6).Value = intComp
    Cells(outRow + rowNum + 3, 7).Value = princRepay
    Cells(outRow + rowNum + 3, 8).Value = yrEndBal
    yrBegBal = yrEndBal
Next rowNum

Use option explicit at the top of the module...

 

[BarryL]

Use option explicit at the top of the module...

the key to fixing 99% of my typing mistakes

 

[wycyobm]

When I try to run it says that there is a invalid procedure call or argument