Page 189 - Excel Progamming Weekend Crash Course

k540629 ch13.qxd 9/2/03 9:34 AM Page 164

164 Saturday Afternoon

The PMT function returns a negative number for the loan payment because it
is money going out rather than money flowing in. Simply place a minus sign
Tip in front of the function to get a positive result.
The program shown in Listing 13-1 shows how to use this function in a loan calculator
worksheet. It also shows one approach to creating custom applications using VBA program-
ming. This approach is to write a program that creates a worksheet, complete with the
required labels, data entry cells, and formulas. After this has been done, the program is
not used again; the finished worksheet is itself the custom application.

Listing 13-1 A VBA program to create a loan calculator worksheet

Public Sub CreateLoanWorksheet()
Dim wb As Workbook
Dim ws As Worksheet
‘Create a new, blank workbook.
Set wb = Workbooks.Add
‘Rename the first worksheet.
wb.Worksheets(1).Name = “Loan Calculator”
Set ws = wb.Worksheets(“Loan Calculator”)
‘Add a title to the worksheet.
ws.Range(“A1”) = “Loan Calculator”
‘Put identifying labels in column B.
ws.Range(“B4”) = “Loan amount”
ws.Range(“B5”) = “Annual interest rate”
ws.Range(“B6”) = “Loan term in years”
ws.Range(“B7”) = “Monthly payment”
‘ Increase the width of column B to
‘ accomodate the labels.
ws.Range(“B1”).EntireColumn.AutoFit
‘Put the loan payment formula in C7
ws.Range(“C7”).Value = “=-PMT(C5/12, C6*12, C4)”
‘Save the workbook.
wb.SaveAs Filename:=”LoanCalculator”
End Sub

The design of the finished worksheet is fairly simple. It contains a label to identify the
application, three more labels to identify the cells where the user enters the loan informa-
tion (amount, interest rate, and term), and a final label to identify the result. The result
cell will contain the PMT formula to calculate the loan payment. In sum, the steps that the
VBA program has to perform are: