VBA自定义函数：获取指定单元格所在的页码

Public Function PageNumber( _
       Optional ByRef target As Excel.Range, _
       Optional ByVal nStart As Long = 1&) As Variant
 Dim pbHorizontal As HPageBreak
 Dim pbVertical As VPageBreak
 Dim nHorizontalPageBreaks As Long
 Dim nPageNumber As Long
 Dim nVerticalPageBreaks As Long
 Dim nRow As Long
 Dim nCol As Long
On Error GoTo ErrHandler

Application.Volatile

If target Is Nothing Then _

Set target = Application.Caller

With target

nRow = .Row

nCol = .Column

With .Parent

If .PageSetup.Order = xlDownThenOver Then

nHorizontalPageBreaks = .HPageBreaks.Count + 1&

nVerticalPageBreaks = 1&

Else

nHorizontalPageBreaks = 1&

nVerticalPageBreaks = .VPageBreaks.Count + 1&

End If

nPageNumber = nStart

For Each pbHorizontal In .HPageBreaks

If pbHorizontal.Location.Row > nRow Then Exit For

nPageNumber = nPageNumber + nVerticalPageBreaks

Next pbHorizontal

For Each pbVertical In .VPageBreaks

If pbVertical.Location.Column > nCol Then Exit For

nPageNumber = nPageNumber + nHorizontalPageBreaks

Next pbVertical

End With

End With

PageNumber = nPageNumber

ResumeHere:

Exit Function

ErrHandler:

PageNumber = CVErr(xlErrRef)

Resume ResumeHere

End Function

Sub test()

MsgBox PageNumber(ActiveCell, 1)

End Sub