Problem with plotting a chart when using arrays as Values and Xvalues

[jonathan.underwood]

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

 

[Tushar Mehta]

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

 

[Jon Peltier]

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/ChartsHowTo/DelinkChartData.html

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

 

[Stacy35216]

Read this thread when I was trying to solve the same problem and go
discouraged. But, for future reference, here's the way around i
without cluttering your spreadsheet (instead of populating a range, yo
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 = Ystrin