Determining The Number Of Printed Pages

Category: Printing / General VBA | [Item URL]

If you need to determine the number of printed pages for a worksheet printout, you can use Excel's print preview feature, and view the page count displayed at the bottom of the screen.

This tip provides two ways to determine the number of printed pages -- one using the the Excel 4 (XLM) Get.Document macro function, the other using VBA.

Using an XLM macro in VBA

You can execute an XLM macro function from VBA, as follows:

  PgCnt = ExecuteExcel4Macro("Get.Document(50)")

In the statement above, the number of printed pages in the active sheet is assigned to the PgCnt variable.

The VBA subroutine below loops through all worksheets in the active workbook and displays the total number of printed pages. Note that this may return incorrect results when using a user-specified print range.

Sub ShowPageCount()
    PageCount = 0
    For Each sht In Worksheets
        sht.Activate
        Pages = ExecuteExcel4Macro("Get.Document(50)")
        PageCount = PageCount + Pages
    Next sht
    MsgBox "Total Pages = " & PageCount
End Sub

Using VBA

Stew Scott provided the VBA procedure below, which does not use XLM.

Sub NumberOfPrintedPages()
    Worksheets(1).DisplayAutomaticPageBreaks = True
    HorizBreaks = Worksheets(1).HPageBreaks.Count
    HPages = HorizBreaks + 1
    VertBreaks = Worksheets(1).VPageBreaks.Count
    VPages = VertBreaks + 1
    NumPages = HPages * VPages
    Worksheets(1).DisplayAutomaticPageBreaks = False
    MsgBox NumPages
End Sub