Unable to set the XValues property of the Series class

[Ben]

Hi,

I'm using a xlXYScatter chart with 3 series. When I try to assign
the Xarray with 200 values to the XValues like this:

objChart.SeriesCollection(1).XValues = Xarray

I got the following error:
Unable to set the XValues property of the Series class

I have the chart embeded in an OLE control on a VB form.

Please Help.
Thanks

 

[Jon Peltier]

You've run into the limit on the number of characters which can be used to
describe the .values and .xvalues, which is around 253 (no, not 255 or 256).
200 values plus 199 commas is 399 characters, and that's if the values are
all single digit.

Dump the values into a worksheet range and use this range as the chart's
source data range.

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

 

[Jon Peltier]

It's not just the chart that's embedded, right? It should be a workbook with
a chart sheet visible. If there's no worksheet, you'll have to add one.

How are you addressing the chart currently?

- Jon

 

[Jon Peltier]

Ben -

What & where is the data on the worksheet?

I'm a little rusty, but it should go something like the following. Adjust
the ranges if there's a risk of overwriting anything important on Sheet1.
Also I am assuming the arrays are one dimensional.

Dim objChart As Object
Dim objWksht As Object ' or as Excel.Worksheet if early binding
Dim rngX1 As Object ' or as Excel.Range
Dim rngY1 As Object ' or as Excel.Range
Dim rngX2 As Object ' or as Excel.Range
Dim rngY2 As Object ' or as Excel.Range

Set objChart = OLE1.object.ActiveChart
Set objWksht = OLE1.Object.Worksheets("Sheet1")

' populate ranges with array data
With objWksht
Set rngX1 =
objWksht.Range("A1").Resize(UBound(XArray1)+1-LBound(XArray1))
rngX1.Value = .Application.WorksheetFunction.Transpose(XArray1)

Set rngY1 =
objWksht.Range("B1").Resize(UBound(YArray1)+1-LBound(YArray1))
rngY1.Value = .Application.WorksheetFunction.Transpose(YArray1)

Set rngX2 =
objWksht.Range("D1").Resize(UBound(XArray2)+1-LBound(XArray2))
rngX21.Value = .Application.WorksheetFunction.Transpose(XArray2)

Set rngY2 =
objWksht.Range("E1").Resize(UBound(YArray2)+1-LBound(YArray2))
rngY2.Value = .Application.WorksheetFunction.Transpose(YArray2)
End With

' populate chart series from ranges
With objChart
.Type = xlXYScatter
.HasTitle = True
.SeriesCollection(1).Name = "=""Serie1"""
.SeriesCollection(1).XValues = rngX1 ' XArray1
.SeriesCollection(1).Values = rngY1 ' YArray1
.SeriesCollection(2).Name = "=""Serie2"""
.SeriesCollection(2).XValues = rngX2 ' XArray2
.SeriesCollection(2).Values = rngY2 ' YArray2
'....
End with


- Jon

 

[Jon Peltier]

Glad to help.

- Jon