Self define function shows error

[Emily]

The code below works fine but shows #VALUE! when applied in worksheet
Example, A1

= pagerow(2) = #VALUE!

Any suggestion?
Emily

Function pagerow(pagenumber As Integer) As Integer 
    pagerow = (Application.ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64)," & _
pagenumber & ")") - 1) 
End Function 
 
Sub test() 
    RowofPage = pagerow(2) 
End Sub

[Jacob Hilderbrand]

Try one of these.

Option Explicit
 
Function PageRow(PageNumber As Long) As Long
PageRow = ActiveSheet.HPageBreaks(PageNumber ).Location.Offset(-1, 0).Row
End Function

Function PageRow2(PageNumber As Long, Ancor As Range) As Long
PageRow2 = Ancor.Parent.HPageBreaks(PageNumber ).Location.Offset(-1, 0).Row
End Function

Also, make sure the page exists (i.e., make sure that there is text in a cell on that page).

[Emily]

DRJ

Thanks for your reply.

Does Application.ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64)," & pagenumber & ")") - 1) not appliable as a function?

[Jacob Hilderbrand]

I can't say with 100% certainty, but it would not work for me.

[Emily]

For those interest in this topic, please read the probably answer by Insomniac posted in JMT
http://www.puremis.net/excel/cgi-bin...5856;start=0#4

[Zack Barresse]

Yes, that's correct. You cannot use Excel4Macros in UDF's. I'm wondering why you can't just utilize the GET.DOCUMENT properties as the named range? This is the standard workaround. No need for a UDF.

IrishDave mentions this at Colo's board (from your link). Paddy shows some working solutions here.

[Emily]

firefytr,

Thanks for your reply

Actually, I seldom use Excel4Macros in my own worksheet.

The question is quoted from one Chinese Excel discussion board and just for my interest.

Thanks for the sharing.
Emily