CODEWITHSUNDEEP
excelvba
JLCH
JLCH

Reputation: 803

Count pages in print area of another tab

Aim Create an excel function that counts the number of pages on a different tab, but also respects the print area.

What I've tried The following function works, but does not respect print areas, and strangely when recalculating changes it's output

Public Function countpa(Tabx As String) As Integer
    Application.Volatile
    countpa = Sheets(Tabx).PageSetup.Pages.Count
End Function

The following integrated function GET.DOCUMENT(50) seems to respect the page layout / print area, but I don't suceed to use it in a function that allows me to refer to a different tab

works when executed on tab

Sub ShowMe()
    MsgBox ExecuteExcel4Macro("GET.DOCUMENT(50)") & _
        " pages will be printed."
End Sub

does not work

Public Function countp(Tabx As String) As Long
   countp = Application.ExecuteExcel4Macro("Get.Document(50," & Tabx & ")")
End Function

Thanks in advance for your help

Upvotes: 1

Views: 338

Answers (1)

T.M.
T.M.

Reputation: 9948

You have to include a double quotation mark "" within the string parts together with starting or ending quotation marks.

Public Function countp(Tabx As String) As Long
countp = ExecuteExcel4Macro("Get.Document(50,""" & Tabx & """)")
End Function

Example call

Debug.Print countp("Sheet1") & " page(s) will be printed."

Upvotes: 1

Related Questions