VBA Function

[Guest]

I am trying to create my own Excel function in my VBA editor and I need to
know how to refer to the page numbers in any given sheet.

Example: I want the variable PgNum to equal the page number of the cell
that the function is in. In other words, if the function is located on page
7, I want PgNum to equal the value 7.

Thanks

Chris

 

[Guest]

A couple of questions spring immediately to mind. What should the function
return if more than one sheet is selected (your print area just expanded)?
What should the function return if the print area is changed (your print area
just shrunk)? What should the function return if the cell is not within the
print area? Even once this is answered I think you have a tricky function
here.

 

[Dave D-C]

I hope this is helpful.

I think you have to scan thru the PageBreaks to find your page.
If you put this into cell A1
=zPageNum(CELL("row", A1))
this will give you the pagenum.
It is slow because Excel has to paginate the sheet for each use.
It is limited to the leftmost pages (doesn't consider VPageBreaks)
(could be extended, of course).
To its credit, it doesn't use Excel 4 macros (GET.DOCMENT(..))
which has a memory leak (at least with Excel97).

Function zPageNum&(pRow&)
Dim iPage%
For iPage = 1 To ActiveSheet.HPageBreaks.Count
If ActiveSheet.HPageBreaks(iPage).Location.Row - 1 >= pRow Then
Exit For
End If
Next iPage
zPageNum = iPage
End Function