Can't set Chart's SeriesCollection XValues

Hello,

Been trying to tackle this very strange problem i have been having.

I have a pie-chart's x, y coordinates reference a contigous range of cells
for each x,y coordinate.

The following code works:

chartObj.Chart.SeriesCollection(1).Values = yRng
chartObj.Chart.SeriesCollection(1).XValues = xRng

where xRng, yRng are Range objects.

However, i get the following error message : "Unable to set the XValues
property of the Series class" when i try programmatically set the chart x to
the ACTUAL VALUE contained with xRng using the following way:

Dim xArray, yArray as variant
Dim i as long
For i = 1 To xRng.count
yArray(i - 1) = yRng.Cells(i, 1)
xArray(i - 1) = xRng.Cells(i, 1)
Next i
chartObj.Chart.SeriesCollection(1).Values = yArray
chartObj.Chart.SeriesCollection(1).XValues = xArray '->This line fails.

I don't understand as both methods should be equivalent. In the first
example, i'm setting the chart x,y coordinates to reference cells. In the
2nd example, i'm setting the chart x,y, coordianes to the actual data
contained within those same cells.

Can anyone see what i'm doing wrong or how i can better set the chart's x,y
references to the actual values. Unfortunatle the Workbook.BreakLinks method
doesn't exist in excel2000 for me to use to convert the chart's x,y refrences
to actual values.

Thanks,

I first assumed it was because Pie charts don't support xValues. However, I
didn't receive an error running the following code. But all that adding
xValues did was rename the category labels to these xValues. The xValues
themselves apparently weren't used in the plot.

I never use Pie charts (or charts a whole lot in general) and havn't spent
much time investigating this. So I may be missing something. I suggest you
take it from here. I repeat, the following two macros (written in a hurry)
worked for me.

Regards,
Greg

Sub MakePieChart()
Dim cht As Chart
Dim chtobj As ChartObject
Dim s As Series
Set chtobj = ActiveSheet. _
ChartObjects.Add(100, 100, 200, 200)
Set cht = chtobj.Chart
With cht
.ChartType = xlPie
Set s = .SeriesCollection.NewSeries
s.XValues = Range("A1:A10")
s.Values = Range("B1:B10")
End With
End Sub

Sub ChangeChartData()
Dim cht As Chart
Dim xRng As Range, yRng As Range
Dim xArray() As Single, yArray() As Single
Dim i As Long

Set xRng = Range("C1:C10")
Set yRng = Range("D1:D10")
ReDim xArray(xRng.Count - 1)
ReDim yArray(yRng.Count - 1)
For i = 1 To xRng.Count
yArray(i - 1) = yRng.Cells(i, 1)
xArray(i - 1) = xRng.Cells(i, 1)
Next
Set cht = ActiveSheet.ChartObjects(1).Chart
With cht.SeriesCollection(1)
.Values = yArray
.XValues = xArray
End With
End Sub

"BW" wrote:

> Hello,
>
> Been trying to tackle this very strange problem i have been having.
>
> I have a pie-chart's x, y coordinates reference a contigous range of cells
> for each x,y coordinate.
>
> The following code works:
>
> chartObj.Chart.SeriesCollection(1).Values = yRng
> chartObj.Chart.SeriesCollection(1).XValues = xRng
>
> where xRng, yRng are Range objects.
>
> However, i get the following error message : "Unable to set the XValues
> property of the Series class" when i try programmatically set the chart x to
> the ACTUAL VALUE contained with xRng using the following way:
>
> Dim xArray, yArray as variant
> Dim i as long
> For i = 1 To xRng.count
> yArray(i - 1) = yRng.Cells(i, 1)
> xArray(i - 1) = xRng.Cells(i, 1)
> Next i
> chartObj.Chart.SeriesCollection(1).Values = yArray
> chartObj.Chart.SeriesCollection(1).XValues = xArray '->This line fails.
>
> I don't understand as both methods should be equivalent. In the first
> example, i'm setting the chart x,y coordinates to reference cells. In the
> 2nd example, i'm setting the chart x,y, coordianes to the actual data
> contained within those same cells.
>
> Can anyone see what i'm doing wrong or how i can better set the chart's x,y
> references to the actual values. Unfortunatle the Workbook.BreakLinks method
> doesn't exist in excel2000 for me to use to convert the chart's x,y refrences
> to actual values.
>
> Thanks,
>
>
>

HI Greg

Thanks for your help.

I did a google search on this error message and it appears that it's a
limitation in excel. See
http://www.excelforum.com/archive/in.../t-276813.html, or do a google
search on the error message.

Just FYI...
BW

"Greg Wilson" wrote:

> I first assumed it was because Pie charts don't support xValues. However, I
> didn't receive an error running the following code. But all that adding
> xValues did was rename the category labels to these xValues. The xValues
> themselves apparently weren't used in the plot.
>
> I never use Pie charts (or charts a whole lot in general) and havn't spent
> much time investigating this. So I may be missing something. I suggest you
> take it from here. I repeat, the following two macros (written in a hurry)
> worked for me.
>
> Regards,
> Greg
>
> Sub MakePieChart()
> Dim cht As Chart
> Dim chtobj As ChartObject
> Dim s As Series
> Set chtobj = ActiveSheet. _
> ChartObjects.Add(100, 100, 200, 200)
> Set cht = chtobj.Chart
> With cht
> .ChartType = xlPie
> Set s = .SeriesCollection.NewSeries
> s.XValues = Range("A1:A10")
> s.Values = Range("B1:B10")
> End With
> End Sub
>
> Sub ChangeChartData()
> Dim cht As Chart
> Dim xRng As Range, yRng As Range
> Dim xArray() As Single, yArray() As Single
> Dim i As Long
>
> Set xRng = Range("C1:C10")
> Set yRng = Range("D1:D10")
> ReDim xArray(xRng.Count - 1)
> ReDim yArray(yRng.Count - 1)
> For i = 1 To xRng.Count
> yArray(i - 1) = yRng.Cells(i, 1)
> xArray(i - 1) = xRng.Cells(i, 1)
> Next
> Set cht = ActiveSheet.ChartObjects(1).Chart
> With cht.SeriesCollection(1)
> .Values = yArray
> .XValues = xArray
> End With
> End Sub
>
> "BW" wrote:
>
> > Hello,
> >
> > Been trying to tackle this very strange problem i have been having.
> >
> > I have a pie-chart's x, y coordinates reference a contigous range of cells
> > for each x,y coordinate.
> >
> > The following code works:
> >
> > chartObj.Chart.SeriesCollection(1).Values = yRng
> > chartObj.Chart.SeriesCollection(1).XValues = xRng
> >
> > where xRng, yRng are Range objects.
> >
> > However, i get the following error message : "Unable to set the XValues
> > property of the Series class" when i try programmatically set the chart x to
> > the ACTUAL VALUE contained with xRng using the following way:
> >
> > Dim xArray, yArray as variant
> > Dim i as long
> > For i = 1 To xRng.count
> > yArray(i - 1) = yRng.Cells(i, 1)
> > xArray(i - 1) = xRng.Cells(i, 1)
> > Next i
> > chartObj.Chart.SeriesCollection(1).Values = yArray
> > chartObj.Chart.SeriesCollection(1).XValues = xArray '->This line fails.
> >
> > I don't understand as both methods should be equivalent. In the first
> > example, i'm setting the chart x,y coordinates to reference cells. In the
> > 2nd example, i'm setting the chart x,y, coordianes to the actual data
> > contained within those same cells.
> >
> > Can anyone see what i'm doing wrong or how i can better set the chart's x,y
> > references to the actual values. Unfortunatle the Workbook.BreakLinks method
> > doesn't exist in excel2000 for me to use to convert the chart's x,y refrences
> > to actual values.
> >
> > Thanks,
> >
> >
> >