7.6.5 Styles

Styles

Like Word, Excel has the ability to work with styles. Styles are quite useful to standardize the look of your spreadsheet. By assigning all the formatting properties including fonts, alignment, numeric formatting, color, borders, and shading to a single Style, you don t have to spend time cutting and pasting code over and over again (and hoping that you cut and pasted the right formatting code).

The Workbook object has a Styles property, which returns a Styles collection object. The Styles collection starts out with a few default styles, called Normal, Comma, Comma[0], Currency, Currency[0], and Percent. The styles with the "[0]" suffix omit the two decimal places after the number. You can also define your own styles. The Normal style is the default style. Use this to return to the default. Use the others to quickly set the most common numeric formatting.

While the default styles are helpful, they re rather plain. Our clients demand pizzazz, so we want something a little more sophisticated than just commas in our numbers. Since Office is polymorphic, and the Style object belongs to the Styles collection, we know to look for an Add method. Once the Style is added, we can change its attributes. All of the formatting discussed in this chapter has been illustrated with the Range object, but it also works on the Style object.

The following code creates a Style suitable for a title. The style uses 14-point Times New Roman, makes it bold, centers it, and shades it in light gray.

#DEFINE xlHAlignCenter -4108

#DEFINE xlVAlignCenter -4108

#DEFINE xlEdgeBottom 9

#DEFINE xlEdgeTop 10

#DEFINE xlEdgeRight 8

#DEFINE xlEdgeLeft 7

#DEFINE xlDouble -4119

#DEFINE xlMedium -4138

* Add the new style

oSheet.Parent.Styles.Add("Title")

* Change the attribute of the style

WITH oSheet.Parent.Styles("Title")

* Font

.Font.Name = "Times New Roman"

.Font.Size = 14

.Font.Bold = .T.

* Alignment

.HorizontalAlignment = xlHAlignCenter

.VerticalAlignment = xlVAlignCenter

* Shading

.Interior.Color = RGB(220, 220, 220) && Light Gray

ENDWITH

* Set cell A1 to have a value, then apply the style.

oSheet.Range("A1").Value = "Testing"

oSheet.Range("A1").Style = "Title"

The only aspect that seems radically different between Styles and Ranges is the Border object. The whole series of constants changes, and it s not documented in the Help file. The only way to find out about it is to run the macro recorder and verify that the constants it s recording for Styles are very different from those used for Ranges. The constants needed for the Border object are: xlLeft (-4131), xlTop (-4160), xlBottom (-4107), xlRight (-4152), xlDiagonalUp (6), and xlDiagonalDown (5). Note that the inside borders are missing; we re not sure where they ve gone.

If it s any consolation, there is a bit of consistency with borders. The same bug that affects the Weight property also happens with Styles, too.

 

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved