Listing Calculating Loan Payments without Using the Loan Object

Public Function Payment(vInterestRate As Variant, vTerm As Variant, _ vPrincipalAmount) As Variant

Payment = Application.WorksheetFunction.Pmt _

(vInterestRate / 12, vTerm, vPrincipalAmount) End Function

Sub Test1NoObject()

Dim rg As Range

Dim vTerm As Variant

Dim vInterestRate As Variant

Dim vPrincipalAmount As Variant

Set rg = ThisWorkbook.Worksheets("Loans"). _ Range("LoanListStart").Offset(1, 0)

Do Until IsEmpty(rg)

vTerm = rg.Offset(0, 1).Value vInterestRate = rg.Offset(0, 2).Value vPrincipalAmount = rg.Offset(0, 3).Value rg.Offset(0, 4).Value = _

Payment(vInterestRate, vTerm, vPrincipalAmount) Set rg = rg.Offset(1, 0)

Loop

Set rg = Nothing

End Sub

This listing works much the same as the previous listing, except it doesn't use the Loan object. Therefore a subtle, yet mentally significant difference exists between these two. When the Loan object is used, the variables that are attributable to a loan (such as variables representing the term and interest rate) are directly attached to the Loan object. Without a Loan object, you need to use variables that, though appropriately named, have no inherent relationship to one another. Likewise, the Payment function exists as just another function in a collection of procedures within a standard module. Further, you need to be aware of the parameters required by the Payment function. Take a minute to ponder these mental differences between the two listings.

0 0

Post a comment