XL: "Subscript Out of Range" Error When XValues Referenced

ID: Q139401

The information in this article applies to:

Microsoft Excel for Windows, version 5.0
Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

When you reference values returned by the XValues property in a Microsoft Excel Visual Basic for Applications macro, you may receive the following error message:

Run time error "9":
Subscript out of range

CAUSE

This problem occurs because the XValues property returns a vertical array of x coordinates, which requires that you specify a second dimension for the array or transpose the array into a horizontal array.

RESOLUTION

To read the array of values returned by the Xvalues property, use either of the following methods.

Method 1

To reference the vertical array of values returned by the XValues property, use two-dimensional referencing. For example, reference the array (x) with 1 as the second dimension reference, as in the following macro:


   Sub DisplayXValues()
       Dim TheArray As Variant
       TheArray = ActiveChart.SeriesCollection(1).XValues
       For I = 1 To UBound(TheArray)
           MsgBox TheArray(I, 1)
       Next I
   End Sub

Method 2

Use the Transpose function to convert the two-dimensional array (vertical array) into a one-dimensional array (horizontal array). For example, transpose the array as in the following macro:


   Sub DisplayXValues()
      Dim TheArray As Variant
      TheArray = ActiveChart.SeriesCollection(1).XValues
      TheArray = Application.Transpose(TheArray)
      For I = 1 To UBound(TheArray)
         MsgBox TheArray(I)
      Next I
   End Sub

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words:

Keywords : kberrmsg
Version : WINDOWS: 5.0, 7.0; MACINTOSH: 5.0
Platform : MACINTOSH WINDOWS
Issue type : kbprb