ExcelBanter - Displaying XValues.

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

Sonny Kocak
Microsoft Developer Community Support
Email :

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| Date: Fri, 09 Apr 2004 15:24:33 +0200
| From: y
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4)
Gecko/20030624
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Displaying XValues.
| Content-Type: text/plain; charset=us-ascii; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID:
| Newsgroups: microsoft.public.excel.programming
| NNTP-Posting-Host: host177-63.pool80181.interbusiness.it 80.181.63.177
| Lines: 1
| Path:
cpmsftngxa06.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTF EED02.phx.gbl!TK2MSFTNGXA0
1.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.g bl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:485706
| X-Tomcat-NG: microsoft.public.excel.programming
|
| I tried
|
| MsgBox myChart.SeriesCollection(1).XValues
|
| MsgBox myChart.SeriesCollection(1).XValues.Address
|
| MsgBox myChart.SeriesCollection(1).XValues.Value
|
| They don't work. Here the of code:
|
| Sub Macro4_2()
|
| Dim rArea As Range, rXValues As Range, rYValues As Range
| Dim c As Variant
| Dim i As Integer
| Dim iNseries As Integer
| Dim chtmyChart As Chart
| Dim sSTR As String
|
| Set rArea = Application.InputBox(prompt:="Select range:", Type:=8)
| Set rXValues = Application.InputBox(prompt:="Select XValues:",
Type:=8)
| Set rYValues = Application.InputBox(prompt:="Select YValues:",
Type:=8)
|
| iNseries = rArea.Rows.Count - 1
| Set chtmyChart = Charts.Add
|
| With chtmyChart
| For i = 1 To iNseries
| .SeriesCollection.NewSeries
| Next
| .Name = "Pippo"
| i = 1
| For Each c In .SeriesCollection
| sSTR = "=Foglio1!" & rArea.Offset(i - 1, 0).Resize(1,
| rArea.Columns.Count).Address(ReferenceStyle:=xlR1C 1)
| Rem c.Values = "=Foglio1!$C$4:$L$4"
|
| Rem c.Values = sSTR
| c.Name = "=Foglio1!" & rYValues(i -
1).Address(ReferenceStyle:=xlR1C1)
| c.XValues = rXValues.Address
| i = i + 1
| Rem MsgBox "LABEL: " & sYValues(i).Address & "SERIE: " &
sSTR & ""
| Next
| .ChartType = xlSurface
| End With
|
| all things go fine <<
|
| MsgBox chtmyChart.SeriesCollection(1).XValues
|
| macro stops <<
|
| End Sub
|
|