Page 194 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Chapter 8

Figure 8-1. This sample worksheet lets you calculate the components of a mortgage loan.

Sub Calculate_Table()
Dim counter As Integer, Payments As Integer, Frequency As Integer
Dim Temp As String, intRow As Integer, intColumn As Integer
Dim intOffset As Integer, strR1C1Address As String, strA1Address As String

ActiveWorkbook.S heets.Add After:=Worksheets(Worksheets.Count)
Sheets( Sheets.Count).Select
ActiveSheet.N ame = Range("Name")
Payments = Range("Payments")
Frequency = 12
' Set headers
Range("A5") = "Payment"
Range("B5" ) = "Principal"
Range("C5") = "Interest"
Range("D5") = "Present Value"
Range("E5" ) = "Interest Paid"
' Loop through number of payments and insert formulas
' Use various meth ods of referencing a range
For counte r = 1 To Payments
int Row = 5 + counter
intColumn = 1

168