There are a handful of functions on the Excel core, but none of them can retrieve the number of pages a worksheet has. It could be done with old versions via the Excel 4.0 macros (XLM) GET functionality (but for me, no more).
You can also solve this via VBA macro, which will not be my preferred option since UDT functions have a poor performance in Excel (in this case, specially terrible performance, everything that has to deal with PrintArea is extremelly slow).
Here are some links to do the task recalling to Excel Macro 4.0:
- To make them work I think that you have to follow these steps.
- More on how to use the Excel Macro 4.0 functions, explained here, with a nice PDF help.
- From ExcelFox forum, a supperb resume for commands
- From JKP, this other one
- From spanish blog ExcelForo, this one
But I couldn’t make them to work and finally gave up, so I needed a macro, tunned convenientely to show number of pages, total in vertical or total in horizontal:
Public Function fPages(ByVal Target As Excel.Range, _ Optional ByVal bVertical As Boolean = True, _ Optional ByVal bHorizontal As Boolean = True) As Long Dim oWsh As Excel.Worksheet Dim lgHpBreaks As Long Dim lgVBreaks As Long Set oWsh = Target.Parent With oWsh '.DytisplayAutomaticPageBreaks = False '.UsedRange.EntireRow.Hidden = True '.UsedRange.EntireColumn.Hidden = True '.Range(.PageSetup.PrintArea).EntireRow.Hidden = False '.Range(.PageSetup.PrintArea).EntireColumn.Hidden = False lgHpBreaks = .HPageBreaks.Count + 1 lgVBreaks = .VPageBreaks.Count + 1 '.UsedRange.EntireRow.Hidden = False '.UsedRange.EntireColumn.Hidden = False End With fPages = IIf(bVertical, lgVBreaks, 1) * IIf(bVertical, lgHpBreaks, 1) End Function
But this function do not autorecalculates… so you better use combined with a function that it does (the Now() function will perform good).
If you use it in the worksheet, go for: