Number of pages of Excel worksheet

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: 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:
=fPages(A1,True,True)+(NOW()*0)

Leave a Reply

Your email address will not be published. Required fields are marked *