PDA

View Full Version : [SOLVED] Self define function shows error



Emily
03-29-2005, 08:22 PM
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
03-29-2005, 08:48 PM
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
03-30-2005, 01:09 AM
DRJ

Thanks for your reply.

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

Jacob Hilderbrand
03-30-2005, 10:01 AM
I can't say with 100% certainty, but it would not work for me.

Emily
04-01-2005, 07:57 PM
For those interest in this topic, please read the probably answer by Insomniac posted in JMT
http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?board=2;action=display;num=1112095856;start=0#4

Zack Barresse
04-01-2005, 08:12 PM
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 (http://www.mrexcel.com/board2/viewtopic.php?t=21312).

Emily
04-01-2005, 09:15 PM
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