Page 181 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

It should be noted that the values for the margin settings are all expressed in terms of points
(there being 72 points per inch). You probably don’t want to keep that number in your head
and perform conversions all the time, so you will want to use the Application.InchesToPoints Chapter 7
method to make the conversion for you. All you need to do is put the number of inches in the
parentheses of the method and assign that value to the appropriate property. For example,
you could set a top margin of three-quarters of an inch using the following code:

ActiveSheet.PageSetup.TopMargin = Application.InchesToPoints(0.75)

Another important aspect of changing how a worksheet is printed is in the proper placement
of page breaks. A page break represents the last row or column that will be printed on a page;
in most cases, you can let Excel set the page breaks automatically, moving a row or column to
the next page only when it would encroach on a margin. If you’d rather specify where page
breaks should occur, you can do so by specifying the column to the right of where you want
the page break, or the row below where you want the page break.

Note Yes, referencing rows and columns is more like the topics you’ll find in Chapter 8,
“Ranges and Cells,” but it also makes sense to cover page breaks here with the rest of the
printing topics.

The syntax for setting a manual page break requires you to specify the row or column below
or to the right of where you want the break to be placed. For example, if you wanted to set a
manual page break above row 30 on Sheet2, you would use the following line of code:

Worksheets("Sheet2").Rows(30).PageBreak = xlPageBreakManual

Setting a manual page break to the left of column D on Sheet1, however, would use this code:

Worksheets("Sheet1").Columns("D").PageBreak = xlPageBreakManual
To delete a page break, you set the PageBreak property to either of the Excel constants xlPage-
BreakNone or xlNone, as in the following examples:

Worksheets("Sheet2").Rows(30).PageBreak = xlPageBreakNone
Worksheets("Sheet1").Columns("D").PageBreak = xlNone
You can remove all of the page breaks on a worksheet using the worksheet’s ResetAllPage-
Breaks method:

Worksheets("Sheet1").ResetAllPageBreaks

Tip Print to Your Specification
Remember that you can force a worksheet to print on a specified number of pages by set;
ting the FitToPagesTall and FitToPagesWide properties of a worksheet’s PageSetup object.

155