Compartir a través de

Working with Page Headers in Excel 2010

  • Artículo

Pancarta de nota rápida de Office

Handy Programming Tips for Microsoft Excel 2010: Learn how to programmatically work with page headers and footers in Microsoft Excel 2010.

Última modificación: viernes, 20 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:   June 2011

Provided by:    Frank Rice, Microsoft Corporation

In Microsoft Excel 2010, headers and footers are lines of text that print at the top (header) and bottom (footer) of each page in the spreadsheet. In this topic, you add headers or footers at the top or bottom of a printed worksheet and then see the use of some of their properties in action. 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 works with various properties of the workbook page headers and footers.

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 WorkWithPages()
    ' Fill random data:
    Range("A1", "R100").Formula = "=RANDBETWEEN(1, 100)"

    Dim pgs As Pages
    Set pgs = PageSetup.Pages

    PageSetup.DifferentFirstPageHeaderFooter = True

    ' Look in the Immediate window for this output:
    Debug.Print "The current sheet can be printed on " & _
     pgs.Count & " page(s)."

    Dim pg As Page
    Set pg = pgs(1)
    pg.CenterHeader.Text = "This is the first page's header"

    Set pg = pgs(2)
    pg.CenterFooter.Text = "This is the second page's footer"

    Set pg = pgs(pgs.Count)
    pg.CenterFooter.Text = "This is the last page's center footer."
    pg.LeftHeader.Text = "This is the last page's header"

    ' Note that Excel supports only distinct headers/footers
    ' for the first page, so headers and footers on the second
    ' and other pages are combined--the last value set overwrites
    ' the header/footer.

    ' See the values in the Immediate window.
    ' Note that the code disregards errors that occur--attempting
    ' to retrieve a header/footer setting that doesn't exist raises an error:
    On Error Resume Next
    Debug.Print "First page (CenterHeader) : " & pgs(1).CenterHeader.Text
    Debug.Print "Second page (CenterHeader): " & pgs(2).CenterHeader.Text
    Debug.Print "Second page (CenterFooter): " & pgs(2).CenterFooter.Text
    Debug.Print "Third page (CenterFooter) : " & pgs(3).CenterFooter.Text
    Debug.Print "Last page (LeftHeader)    : " & pgs(pgs.Count).LeftHeader.Text
    Debug.Print "Last page (CenterFooter)  : " & pgs(pgs.Count).CenterFooter.Text

    ' In conclusion, use the Page class to retrieve information about headers
    ' and footers for specific pages. Use the PageSetup object to set the headers
    ' and footers, as it's clearer to set them there.
End Sub

Test the Solution

In this task, you step through the VBA code that manipulates the headers and footers in the workbook.

To step through 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. Now, press F8 to step through the code line-by-line and watch the code behavior.

Next Steps