LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 21st 05, 07:00 PM
Posts: n/a
Default Problem with plotting a chart when using arrays as Values and Xvalues


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)


'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!

  #3   Report Post  
Old June 22nd 05, 08:53 PM
Jon Peltier
Posts: n/a

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

More information:

- Jon
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions

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

  #4   Report Post  
Old August 19th 05, 09:05 PM
Posts: n/a

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's Profile:
View this thread:

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 08:31 PM.

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

About Us

"It's about Microsoft Excel"


Copyright © 2017