This site will look better if you upgrade to a browser that supports web standards.
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.
Choose Num_Pages, asand define a new named function, say
Then you can call it from the worksheet: using =Num_Pages:
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
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.