using Evaluate on a Name object?

[Rich]

Hello,

Last week I had a problem cycling through the PageBreak
Collection in VBA. Tom Ogilvy was kind enough to share an
example how to do this. Worked Great! but a little over
my head since I have never used the Evaluate and Index
functions of Excel in VBA. I have not been able to find
documentation in the help files (or elsewhere) on the code
that was used to set up the Name objects:

ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"

I experimented with the name objects using

Names("hzPB").RefersTo:="=Sheet1$A$1:$B$20

instead of RefersToR1C1, but that did not return something
that could be evaluated. Then I tried
RefersToR1C1...'Sheet1'... which gave me a runtime error.
It was this line -- "=GET.DOCUMENT(64,""Sheet1"")" --
which really stumped me. I could not find any
documentation on Get.Document(64,""...""). I would be
grateful if someone could explain to me about Get.Document
(in Excel in this example) or point me to an article or
some source. It seems like this line is the only way to
capture just rows with Pagebreaks.

Thanks in advance,
Rich

 

[Rich]

Well, I did stumble onto this piece of code which will
list pagebreaks but not using Evaluate

Dim rng As Range, i As Integer, pgbk As Integer
Set rng = Range("A1:L97")
For i = 1 To rng.Rows.Count
pgbk = rng(i, 1).EntireRow.PageBreak
If pgbk = xlPageBreakAutomatic Then
Debug.Print "automatic pgbreak at A" & i
ElseIf pgbk = xlPageBreakManual Then
Debug.Print "manual pgbreak at A" & i
End If
Next

I guess my problem was that I was not referencing the row-
EntireRow property (or the EntireColumn property either).
This routine is a little easier to understand, but it does
not have the performance of Tom's routine, probably
because I am looking at the entire row or column where in
the Named Range routine you are evaluating only the named
range. I would still like to know more about

RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"

What does the 64 represent? The number of rows?

 

[Dana DeLouis]

If you are interested, the old Excel 4 macro help file is still available
for download from Microsoft.

Macrofun.exe File Available on Online Services
http://support.microsoft.com/default.aspx?scid=kb;en-us;128185&Product=xlw

 

[Rich]

Thanks. I will give that a try.

-----Original Message-----
If you are interested, the old Excel 4 macro help file is still available
for download from Microsoft.

Macrofun.exe File Available on Online Services
http://support.microsoft.com/default.aspx?scid=kb;en- us;128185&Product=xlw

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =





.

 

[Rich]

OK! That help file did the trick. Now this makes sense.
I am just curious why Microsoft isn't including these help
files with the later versions of Excel if that older
functionality is still available. There must a way to do
the same thing with pagebreaks for named ranges in the
later version - without having to look at EntireRow or
EntireColumn. Oh well, at least I have some solutions
now.

Thank you all for your help.
Rich

 

[Dana DeLouis]

Your code works fine, but would any ideas here help?

Sub Demo()
Dim aRow As Range

With Range("A1:L97")
For Each aRow In .EntireRow.Rows
Select Case aRow.PageBreak
Case xlAutomatic
Debug.Print "Automatic pgbreak at Row: " & aRow.Row
Case xlPageBreakManual
Debug.Print "Manual pgbreak at Row: " & aRow.Row
End Select
Next aRow
End With
End Sub