This site will look better if you upgrade to a browser that supports web standards.

McGimpsey & Associates 

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Using an XL4M command to determine the number of pages in a sheet.

VBA does not allow a convenient way to determine the number of pages that a worksheet will print to. It's possible to use the VPageBreaks and HPagebreaks to calculate the number of pages, but the process is slow and requires a rather large amount of code to calculate the number of partial pages.

Fortunately, an XL4M command allows us to get the number of pages in one go:

Warning: XL4M commands can be hazardous to your spreadsheets. While they are supported through at least XL11 (WinXL2003/MacXL2004), they may not be in future versions. Also, when used with MacXL or WinXL97/00 as named functions in a spreadsheet, they can cause crashes when cells containing references to the named functions are copied to another sheet. You've been warned.

On the Worksheet

Choose Insert/Name/Define and define a new named function, say Num_Pages, as

    =Get.Document(50)

Then you can call it from the worksheet: using =Num_Pages:

In a VBA routine

To use the XL4M command in a macro, call it via the ExecuteExcel4Macro() command:

    Dim nNumPages As Long
	   nNumPages = Application.ExecuteExcel4Macro("Get.Document(50)")
	

This page last updated Thursday, 4 March 2004

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.