Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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

  #3   Report Post  
Jon Peltier
 
Posts: n/a
Default

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.

  #4   Report Post  
Stacy35216
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent Chart Questions Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
Urgent Chart Assistance Requested Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
Prevent non-numerical data (e.g. text) from plotting on a chart jaweiss Charts and Charting in Excel 1 March 19th 05 06:54 PM
Problem adding Series XValues to Chart using VBA Sean Curry Charts and Charting in Excel 0 March 14th 05 01:18 AM
Strange Problem with Chart and Plot Visible Cells Option Marcus Leon Charts and Charting in Excel 1 January 7th 05 04:52 AM


All times are GMT +1. The time now is 04:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"