Compartir a través de

Working with PageSetup Object Features in Excel 2010

  • Artículo

Pancarta de nota rápida de Office

Handy Programming Tips for Microsoft Excel 2010: Learn how to add various headers and footers to a Microsoft Excel 2010 workbook by using the PageSetup object.

Última modificación: viernes, 06 de mayo de 2011

Hace referencia a: Excel 2010 | Office 2010 | VBA

En este artículo
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Applies to:    Microsoft Excel 2010

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

The PageSetup object contains all page-setup attributes (left margin, bottom margin, paper size, and so on) as properties. In this topic, you programmatically use the PageSetup object properties to add various headers and footers to a workbook. To complete this task, you must do the following:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

In this task, you add programming code that adds various headers and footers to the workbook.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Nota

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub PageSetupDemo()

  Range("A1:H100").Formula = "=RANDBETWEEN(1, 100)"
  With PageSetup
    .LeftMargin = 100
    .RightMargin = 100

    ' Set the page header and footer for all the pages. To see these
    ' click File and then click the Print button.
    .CenterHeader = "This is the page header."
    .CenterFooter = "This is the page footer."

    ' Use distinct even and odd page headers:
    .OddAndEvenPagesHeaderFooter = True
    .EvenPage.CenterHeader.Text = "This is an even page header"
    .EvenPage.CenterFooter.Text = "This is an even page footer"

    ' When you preview the workbook, you see different headers on the
    ' even and odd pages.

    ' Now make the first page have a specific header/footer
    ' as well:
    .DifferentFirstPageHeaderFooter = True
    .FirstPage.LeftFooter.Text = "This is the bottom of the first page"
    .FirstPage.LeftHeader.Text = "This is the top of the first page."

    ' Watch carefully as you modify this property. It affects
    ' whether the headers and footers respect the margins that
    ' you set in the PageSetup object, earlier in the
    ' procedure:
    .AlignMarginsHeaderFooter = True
    ' Now set the value to False:
    .AlignMarginsHeaderFooter = False

    ' Ensure that the header and footer size scales with the printed
    ' document scaling:
    .ScaleWithDocHeaderFooter = True
  End With
  ActiveWorkbook.PrintPreview
End Sub

Test the Solution

In this task, you step through the VBA code line-by-line.

To run the code

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. Press F8 to step through the code line-by-line and watch the results.

Next Steps