Page 198 - Excel Progamming Weekend Crash Course

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

Session 14 — Formatting a Worksheet 173

the alignment of the selected cells from the default to “centered.” The recorded macro is
shown as follows:
Sub Macro1()
‘
‘ Macro1 Macro
‘ Macro recorded 1/30/2003 by Peter G. Aitken
‘
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub
The one line of code that reflects the formatting that I changed is:

.HorizontalAlignment = xlCenter
The other lines of code are related to additional aspects of formatting that are located on
the same tab in the Format Cells dialog box as the Alignment option. Even though I did not
change these, Excel included them in the recorded macro code. This means that when you
are using recorded macros to learn what the code is for making formatting changes, you
need to pay attention to locate the specific code that is relevant.

Number Formatting

Number formatting controls how numbers are displayed; it has no effect on cells that contain
text. When you apply number formatting using the Format Cells dialog box, you have a vari-
ety of named categories and options from which to choose (see Figure 14-1). In code, how-
ever, things are not this simple. Number formats are set using formatting codes that use a
special syntax to describe how a number is to be displayed. These format codes are assigned
to the NumberFormat property of the Range object. For example, to display numbers with no
commas or special characters, and two decimal places, the code would be as follows:
SomeRange.NumberFormat = “0.00”

Excel’s number formats are flexible and permit you to display numeric data in almost any
imaginable way. Each number formatting code is made up of certain characters, as described
in Table 14-1.