Folks...

I am seeing some odd results with the behavior of the HPageBreaks
collection.

Have a look to the following code

---------------------------------------------------------------
Sub testHB()

Dim mySheet As Worksheet
Dim numItems As Integer
Dim i As Integer
Dim tmpBuf As String


Set mySheet = ActiveSheet

numItems = mySheet.HPageBreaks.Count
For i = 1 To numItems
tmpBuf = mySheet.HPageBreaks.Item(i).Location.Address
MsgBox tmpBuf
Next i

End Sub
---------------------------------------------------------------

For some reason it does fail with a "subscript out of range" error in
a not fully reproducible way (sometimes it works, sometimes it does
not) when trying to dereference item(i) with I being 1 and numItems
= 2 (i.e. when accessing HPageBreaks(2) or greater in a collection
having two or more members...

Any pointer / suggestion welcome !

Regards

--alexT

Alex, see if it is related to print previewing. I recall that one can only
get the HPageBreaks after the sheet has been previewed or printed.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Alex T" wrote in message
om...
Folks...

I am seeing some odd results with the behavior of the HPageBreaks
collection.

Have a look to the following code

---------------------------------------------------------------
Sub testHB()

Dim mySheet As Worksheet
Dim numItems As Integer
Dim i As Integer
Dim tmpBuf As String


Set mySheet = ActiveSheet

numItems = mySheet.HPageBreaks.Count
For i = 1 To numItems
tmpBuf = mySheet.HPageBreaks.Item(i).Location.Address
MsgBox tmpBuf
Next i

End Sub
---------------------------------------------------------------

For some reason it does fail with a "subscript out of range" error in
a not fully reproducible way (sometimes it works, sometimes it does
not) when trying to dereference item(i) with I being 1 and numItems
= 2 (i.e. when accessing HPageBreaks(2) or greater in a collection
having two or more members...

Any pointer / suggestion welcome !

Regards

--alexT

Alex,

From fooling around, this is what I observe (xl 2000). If I don't have a
print area set and the last page break falls at the same row as the end of
the used range, I get a "subscript out of range" error on the last break.
In other words it counts the last break, but then it doesn't recognize the
last break's location. As Bob said, if I then set a print range, the error
does not occur.

hth,

Doug

"Bob Flanagan" wrote in message
...
Alex, see if it is related to print previewing. I recall that one can
only
get the HPageBreaks after the sheet has been previewed or printed.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Alex T" wrote in message
om...
Folks...

I am seeing some odd results with the behavior of the HPageBreaks
collection.

Have a look to the following code

---------------------------------------------------------------
Sub testHB()

Dim mySheet As Worksheet
Dim numItems As Integer
Dim i As Integer
Dim tmpBuf As String


Set mySheet = ActiveSheet

numItems = mySheet.HPageBreaks.Count
For i = 1 To numItems
tmpBuf = mySheet.HPageBreaks.Item(i).Location.Address
MsgBox tmpBuf
Next i

End Sub
---------------------------------------------------------------

For some reason it does fail with a "subscript out of range" error in
a not fully reproducible way (sometimes it works, sometimes it does
not) when trying to dereference item(i) with I being 1 and numItems
= 2 (i.e. when accessing HPageBreaks(2) or greater in a collection
having two or more members...

Any pointer / suggestion welcome !

Regards

--alexT

Hello

Thanks to everyone !

Actually this seems to be a known bug of XL, described in KB 210663

Amazingly enough the bug is persisting from XL 2000 onwards...

--alexT