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

 

[Rorylfc]

At which point does the code stop?

 

[hiker95]

wycyobm,

Welcome to the MrExcel forum.

In order to assist you it would help us if you posted all of your macro code using code tags.

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[wycyobm]

Hi, i have posted like a code.
It stops at annualPmnt = Pmt(intRate, loanLife, -initLoanAmnt, , 0)

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

 

[hiker95]

wycyobm,

In order to assist you it would help us if you posted all of your macro code using code tags.


When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]

 

[steve the fish]

Whats in cell B3 on the activesheet at the time this line runs:

loanLife = Cells(3, 2).Value

 

[BarryL]

what are your values for interest, loan life and loan amount? these should all be Doubles..... might you have ne of those values as a string?

 

[Rick Rothstein]

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 = WorksheetFunction.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

Pmt is not a native VBA function... it is an Excel function, so you must use the WorksheetFunction object to get to it... try making the change I show in red above.

 

[steve the fish]

It is on my 2010 version Rick.

 

[BarryL]

It is on my 2010 version Rick.

Yea worked for me also. I got the error OP mentioned when I inputted a string instead of a Double.