Strange behavior Chart.SeriesCollection(n).Values property

[Emile van Mierlo]

Hello group,

I encountered strange behavior with the Chart.SeriesCollection(n).Values and
XValues property when using floating point arrays.

When this procedure is run, the integer array works fine, but the floating
point array exits with the error message: "Unable to set the XValues
property of the Series class". The strange part is: if you decrease the
array size, it works, if you decrease the decimals, it works. The error
remains if the array is typed.

Anybody knows what's going on?

Thanks.

Emile

Sub drawAxis()

Dim xAxis(1 To 40), yAxis(1 To 40), n As Integer

For n = 1 To 40
xAxis(n) = 0.005
yAxis(n) = 5
Next n

Chart1.SeriesCollection(1).Values = yAxis
Chart1.SeriesCollection(1).XValues = xAxis

End Sub

 

[Andy Pope]

Hi Emile,

The problem is that there is a limit to the length of the data series
formula. Integer values are ok but the floating point exceeds the
length. Therefore as you have discovered reducing the decimals or number
of points will allow it to work again.

Check out Jon Peltier's page for more detail.
http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

Cheers
Andy

 

[Emile van Mierlo]

Hallo Andy,

Thanks for replying so quickly.

The problem is that there is a limit to the length of the data series
formula. Integer values are ok but the floating point exceeds the
length. Therefore as you have discovered reducing the decimals or number
of points will allow it to work again.

I changed the code: now it doesn't set the XValues and Values directly, but
it sets a worksheet name instead, and the chart points to the worksheet
names (see below). This works without limitation. Do you perhaps know why
this makes a difference? The number of points is the same. Is it solely the
fact that the Formula cannot be too long?

Thanks again.

Emile

Sub drawAxis()

Dim xAxis(1 To 40), yAxis(1 To 40), n As Integer

For n = 1 To 40
xAxis(n) = 0.005
yAxis(n) = 5
Next n

ThisWorkbook.Names.Add "xAxis", xAxis
ThisWorkbook.Names.Add "yAxis", yAxis

End Sub

 

[Andy Pope]

Hi,

Yes it a limit on the series formula text length.

If you use the array approach, the integer version so I works, now go to
the chart and select the data series. In the formula bar you will see
the values explicitly entered. Quite a lengthy formula.
Do the same for a range and you will see only the range reference.

There are other limitations within a chart and even with a range these
can be reached.

Cheers
Andy

 

[Emile van Mierlo]

Yes it a limit on the series formula text length.

If you use the array approach, the integer version so I works, now go to
the chart and select the data series. In the formula bar you will see
the values explicitly entered. Quite a lengthy formula.
Do the same for a range and you will see only the range reference.

There are other limitations within a chart and even with a range these
can be reached.

Ok, now I understand this behavior; thanks a lot for explaining, Andy.


Best regards,

Emile