Page 207 - Excel Progamming Weekend Crash Course

k540629 ch14.qxd 9/2/03 9:34 AM Page 182

182 Saturday Afternoon

At this point you have learned about the tools required to further improve the Loan
Calculator application that was started in Session 13. Specifically, the goal is to apply font
and background formatting to make the worksheet more attractive and clearly delineate
which cells the user should enter data into. Here’s what needs to happen:
The worksheet title in cell A1 should be a larger font.
The labels in cells B4:B7 should be boldface.
Cells C4:C7 should have appropriate number formats.
The entire area of the worksheet should have a gray background color with the
three data input cells white.
The resulting program is presented in Listing 14-2. New code that has been added to the
original program from Session 13 is bracketed in "***" comments.

Listing 14-2 The improved program for creating 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”)

With ws
‘ Add a title to the worksheet.
.Range(“A1”) = “Loan Calculator”

‘***
‘ Change the font of cell A1
.Range(“A1”).Font.Size = 24
.Range(“A1”).Font.Bold = True
‘***

‘ Put identifying labels in column B.
.Range(“B4”) = “Loan amount”
.Range(“B5”) = “Annual interest rate”
.Range(“B6”) = “Loan term in years”
.Range(“B7”) = “Monthly payment”

‘***
‘Change the font in B4:B7 to bold.
.Range(“B4:B7”).Font.Bold = True
‘***

‘ Increase the width of column B to