XL2000: "Subscript Out of Range" Using HPageBreaks or VPageBreaks.Location

ID: Q210663

The information in this article applies to:

Microsoft Excel 2000

SYMPTOMS

When you use the Location property or Location.Address of vertical or horizontal page breaks in a Microsoft Visual Basic for Applications macro, you may receive the following error message:

Run-time error '9':
Subscript out of range

CAUSE

This problem may occur when the following conditions are true:

The active cell is above the horizontal page break or to the left of the vertical page break that is referred to by the HPageBreaks or VPageBreaks index.

-and-
The vertical or horizontal page break location is off the screen to the right of the visible window or below the visible window of the workbook.

-and-

You use a Visual Basic for Applications macro in Microsoft Excel similar to the following code:


Sub TestHorizontal()
    ActiveSheet.Range("CZ1000").Value = 1
    MsgBox ActiveSheet.HPageBreaks.Count
    MsgBox ActiveSheet.HPageBreaks(1).Location.Address
    MsgBox ActiveSheet.HPageBreaks(2).Location.Address
End Sub
 
Sub TestVertical()
    ActiveSheet.Range("CZ1000").Value = 1
    MsgBox ActiveSheet.VPageBreaks.Count
    MsgBox ActiveSheet.VPageBreaks(1).Location.Address
    MsgBox ActiveSheet.VPageBreaks(2).Location.Address
    MsgBox ActiveSheet.VPageBreaks(3).Location.Address
End Sub

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

To prevent this problem from occurring, add code to select the last cell used in the worksheet before the code uses the Location property of horizontal or vertical page breaks. For example, use the following code to select the end cell, use the Location property, and then reselect the original active cell:


Sub CheckPageBreaks()

    'Set object "currcell" equal to active cell.
    Set currcell = ActiveCell
    
    'Select the last cell on the worksheet that has data.
    Range("a1").SpecialCells(xlCellTypeLastCell).Select
    
    'Include code with Location property here.
    x = ActiveSheet.HPageBreaks(2).Location.Address
    MsgBox x
    'Example sets x equal to address of second horizontal page break.
    'Then message box displays the address of the page break.
    
    'Select original active cell.
    currcell.Select

End Sub

NOTE: There must be enough data in the worksheet to go below the second page break for this example to work. If there is no data, or the data stops before the second page break, the code fails with the error as stated.

After the Location property is calculated, you may again select the original active cell. If you use code to scroll between the first and last cell, or select the last cell and immediately reselect the starting cell, the error may still occur. The screen must redraw and the Location property be calculated for the workaround to be effective. If you use the above code with


Application.ScreenUpdating = False

to prevent screen redraw, the problem still occurs.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

If you use the Count method with the VPageBreaks or HPageBreaks property, you may get a result of zero. This occurs under the conditions listed in the "Cause" section. If a page break is visible, the Count method may give the correct answer. The Count method for vertical or horizontal page breaks may give the expected result if a page break is near the visible portion of the workbook window. The workaround given earlier can be used to get the expected count.

Page breaks that are to the right of the workbook window or below the workbook window may allow the Count method to work and that page break to be located, if the distance from the window to the page break is less than one-half the distance between page breaks.

REFERENCES

For more information about the Location property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type Location Property (Excel) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about how to trap errors in a macro, click the article number below to view the article in the Microsoft Knowledge Base:

Q213637 How to Use "On Error" to Handle Errors in a Macro

Additional query words: XL2000

Keywords : kbdta kbdtacode OffVBA KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug