Hi

I am trying to generate a chart from VBA arrays, but I am finding that
once the arrays become too large I get the following runtime error:

Run-time error '1004':
Unable to set the XValues property of the Series class

A simple piece of code which reproduces the error:

Option Base 1
Sub test()
Const nPts As Long = 81 'if increase this to = 82, doesn't work!
Dim x(nPts) As Double, y(nPts) As Double
Dim i As Long

For i = 1 To nPts
x(i) = i
y(i) = i
Next i

'Create graph
Set Graph = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)

Graph.Activate

'Plot Data
With ActiveChart.SeriesCollection.NewSeries
.Name = "Data"
.XValues = x
.Values = y
.ChartType = xlXYScatter
End With
End Sub

This is with Excel 2003.
Any help with be fantastic!
Jonathan

XL has a limit on how many characters it will accept in the string for
either the x- or the y-values. I don't remember exactly what it is but
it is either around 250 characters or 450 characters. You could always
put the data in a worksheet range and specify the range as the chart
source.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Hi

I am trying to generate a chart from VBA arrays, but I am finding that
once the arrays become too large I get the following runtime error:

Run-time error '1004':
Unable to set the XValues property of the Series class

A simple piece of code which reproduces the error:

Option Base 1
Sub test()
Const nPts As Long = 81 'if increase this to = 82, doesn't work!
Dim x(nPts) As Double, y(nPts) As Double
Dim i As Long

For i = 1 To nPts
x(i) = i
y(i) = i
Next i

'Create graph
Set Graph = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)

Graph.Activate

'Plot Data
With ActiveChart.SeriesCollection.NewSeries
.Name = "Data"
.XValues = x
.Values = y
.ChartType = xlXYScatter
End With
End Sub

This is with Excel 2003.
Any help with be fantastic!
Jonathan

The limit's about 250. I w ould have thought 256, but apparently it
reserves a few for = and {} around the array.

More information:

http://peltiertech.com/Excel/ChartsH...ChartData.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Tushar Mehta wrote:

XL has a limit on how many characters it will accept in the string for
either the x- or the y-values. I don't remember exactly what it is but
it is either around 250 characters or 450 characters. You could always
put the data in a worksheet range and specify the range as the chart
source.

Read this thread when I was trying to solve the same problem and got
discouraged. But, for future reference, here's the way around it
without cluttering your spreadsheet (instead of populating a range, you
use a name)

ActiveWorkbook.Names.Add Name:="Cht1Srs1X", RefersTo:=myArrayX
ActiveWorkbook.Names.Add Name:="Cht1Srs1Y", RefersTo:=myArrayY
Xstring = "='" & WorkSheetName & "'!Cht1Srs1X"
Ystring = "='" & WorkSheetName & "'!Cht1Srs1Y"
mySeries.XValues = Xstring
mySeries.Values = Ystring


--
Stacy35216
------------------------------------------------------------------------
Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952
View this thread: http://www.excelforum.com/showthread...hreadid=380965