Article ID: 139401 - Last Review: August 17, 2005 - Revision: 1.3

"Subscript Out of Range" Error When XValues Referenced

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q139401

On This Page

Expand all | Collapse all

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
Back to the top

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.
Back to the top

RESOLUTION

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

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
Back to the top

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
Back to the top

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.
Back to the top
APPLIES TO
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
Back to the top
Keywords: 
kberrmsg kbprb KB139401
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations