Children of the Worksheet Object

Let us discuss a few of the children of the Worksheet object. Others will be discussed in later chapters.

Comments

The Comments property returns the Comments collection, which consists of all Comment objects (comments) in the worksheet. We will discuss the Comment object in Chapter 19.

The Names collection

We discussed the Names collection and Name objects in Chapter 16, and so we refer the reader to that earlier discussion.

The Outline object

To illustrate Excel outlining using code, consider the worksheet shown in Figure 18-3. Our goal is to produce the outline in Figure 18-4.

Figure 18-3. Illustrating Excel outlines

A

B

C

D

1

Con

Col2

Col3

2

Rcwl

73

33

23

53

R&V2

123

22

222

4

Row3

23 ;

34

345

3d

SubTotaM

432

89

590

6

7

Rcw4

223

23

454

8

RChVÖ

345

10

53

9

Row6

11

13

4

10

SübTotaS2

579

46

511

11

12

Total

1011

135

1101

Figure 18-4. The end result

1 | 2 3 |

A

B

C

D

1

CoM

Col?

Col 3

2

Row1

78

33

23

3

Rqw2

123

22

277

4

Row3

231

34

345

-

5

SubTctan

432

99

590

6

»

7

Row4

223

454

3

Row5

345

10

53

9

RowG

11

13

4

10

SubTotal2

573

46

511

-

11

12

Total

1011

135

1101

The first step in obtaining the outline in Figure 18-4 is to set the properties of the Outline object for this worksheet. The Outline property of the Worksheet object returns an Outline object, so we begin with:

With ActiveSheet.Outline .SummaryRow = xlBelow .AutomaticStyles = False End With

Setting the SummaryRow property to xlBelow tells Excel that our summary rows (the subtotal and total rows) lie below the detailed data. Thus, Excel will place the expansion/contraction buttons (the small buttons displaying minus signs in Figure 18-4) at the appropriate rows.

Setting AutomaticStyles to False prevents Excel from tampering with our formatting. Otherwise, Excel would remove the boldfacing on the summary rows.

As you can see in Figure 18-4, we want to make the following groupings:

Rows 2-4 Rows 7-9 Rows 2-11

For this, we use the Group method of the Range object. In particular, the following code accomplishes the desired grouping, resulting in Figure 18-4:

With ActiveSheet

.Rows("2:4").Group .Rows("7:9").Group .Rows("2:11").Group End With

Note that the SummaryColumn property of the Outline object governs the location of the expansion/contraction buttons when columns grouped.

To expand or collapse levels, the user can click the small numbered buttons at the top of the leftmost column in Figure 18-4. Clicking on button number X results in all levels above X being completely expanded and all levels below and including X being completely contracted. Thus, all rows at level X and above are made visible, but no levels below X are visible.

The same thing can be accomplished using the ShowLevels method of the Outline object, whose syntax is:

OutlineObject.ShowLevels(.owLevels, ColumnLevels) For instance, the code:

ActiveSheet.Outline.ShowLevels 2

is equivalent to clicking on the button labeled 2 and has the effect of showing all levels above and including level 2, as pictured in Figure 18-5.

Figure 18-5. Outline collapsed to level 2

1 2 3

A

B

C

D

1

CoM

CoE2

Col 3

+ 5

Sublet all

432

89

590

6

+ 10

SubTctal2

579

46

511

11

12

Total

1011

136

1101

The PageSetup object

The PageSetup object represents the page formatting (such as margins and paper size) of an Excel worksheet. Each of the page-formatting options is set by setting a corresponding property of the PageSetup object.

The PageSetup property of the Worksheet object returns the worksheet's PageSetup object.

The properties and methods of the PageSetup object are shown in Table 18-3. (All of the items in Table 18-3 are properties except the PrintQuality method.) Most of the members in Table 18-3 are self-explanatory (and hold no real surprises), so we will not discuss them.

Table 18-3. Members of the PageSetup Object

Application

FitToPagesWide

PrintErrors<v 10>

BlackAndWhite

FooterMargin

PrintGridlines

BottomMargin

HeaderMargin

PrintHeadings

CenterFooter

LeftFooter

PrintNotes

CenterFooterPicture<v 10>

LeftFooterPicture<v 10>

PrintQuality

CenterHeader

LeftHeader

PrintTitleColumns

CenterHeaderPicture<v10>

LeftHeaderPicture<v 10>

PrintTitleRows

CenterHorizontally

LeftMargin

RightFooter

CenterVertically

Order

RightFooterPicture<v 10>

ChartSize

Orientation

RightHeader

Creator

PaperSize

RightHeaderPicture<v 10>

Draft

Parent

RightMargin

FirstPageNumber

PrintArea

TopMargin

FitToPagesTall

PrintComments

Zoom

To illustrate, the following code sets some of the properties of the active worksheet:

To illustrate, the following code sets some of the properties of the active worksheet:

With ActiveSheet.PageSetup

.LeftMargin = Application.InchesToPoints(1) .RightMargin = Application.InchesToPoints(1) .PrintTitleRows = "A1" .PaperSize = xlPaperLetter End With

Note the use of the InchesToPoints function, which is required if we want to express units in inches, since most of the formatting properties require measurement in points. Referring to Figure 18-6, the PrintTitleRows property will cause the word Report, which lies in cell A1, to appear on each page of the printout.

Figure 18-6. A worksheet and the PrintTitleRows property

Figure 18-6. A worksheet and the PrintTitleRows property

0 0

Post a comment