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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.