Microsoft KB Archive/139401

= "Subscript Out of Range" Error When XValues Referenced =

Article ID: 139401

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q139401



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: XL

Keywords: kberrmsg kbprb KB139401

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.