Problem with plotting a chart when using arrays as Values and Xvalues
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 |
All times are GMT +1. The time now is 08:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com