Hi All,
I create a chart thru macro.
The chart is well created when the source column is less than 32.
After exceeding the column, the runtime error arise.
The error as follow:
"Unable to set the XValues property of the Serial class"

what it means?? I really out of ideas?
Do appreciate any guideline / solution to it

Thank you
Best regards,
Ocurnos

Are you assigning the XValues as an array or a range?

If you are assigning the XValues as an array then Excel attempts to list
each element in the array (including decimals if any) in the chart's Series
Formula and easily exceeds the formula capacity. The appended code
demonstrates this. The two commented out lines work while the array approach
fails if and only if there are too many elements referenced.

To see this, select the chart series and examine the series formula in the
formula bar.

If the above doesn't solve the problem then please post your code.

Regards,
Greg


Sub SetChartVAlues()
Dim cht As Chart
Dim i As Long
Dim ArrX() As Variant, ArrY() As Variant
Dim Xrng As Range, Yrng As Range

Set Xrng = Range("A1:A50")
Set Yrng = Range("B1:B50")
Set cht = ActiveSheet.ChartObjects(1).Chart
With cht.SeriesCollection(1)
'.Values = Yrng
'.XValues = Xrng
ReDim ArrX(1 To Xrng.Count)
ReDim ArrY(1 To Yrng.Count)
For i = 1 To UBound(ArrX)
ArrX(i) = Xrng(i)
ArrY(i) = Yrng(i)
Next
.Values = ArrY
.XValues = ArrX
End With
End Sub

"Daniel" wrote:

Hi All,
I create a chart thru macro.
The chart is well created when the source column is less than 32.
After exceeding the column, the runtime error arise.
The error as follow:
"Unable to set the XValues property of the Serial class"

what it means?? I really out of ideas?
Do appreciate any guideline / solution to it

Thank you
Best regards,
Ocurnos

Hi Greg,
i use looping to create the following output:
strSerial = "=(Summary!R5C"
Do While i <= intMaxCol
strSerial = strSerial & i
If Not i = intMaxCol Then
strSerial = strSerial & ",Summary!R5C"
Else
strSerial = strSerial & ")"
End If
i = i + 2
Loop

output:
=(Summary!$C$5,Summary!$E$5,Summary!$G$5,Summary!$ I$5,Summary!$K$5,Summary!$M$5,Summary!$O$5,Summary !$Q$5,Summary!$S$5,Summary!$U$5,Summary!$W$5,Summa ry!$Y$5,Summary!$AA$5,Summary!$AC$5,Summary!$AE$5, Summary!$AG$5,Summary!$AI$5,Summary!$AK$5,Summary! $AM$5,Summary!$AO$5,Summary!$AQ$5,Summary!$AS$5,Su mmary!$AU$5,Summary!$AW$5,Summary!$AY$5)

Next step:
I directly assign the output to XValues as follow:
chtChart.SeriesCollection(1).XValues = strSerial

Is that too many elements referenced as u said?
If yes, any better idea to solve it?

thank you in advance.
Best regards,
Ocurnos

"Greg Wilson" wrote:

Are you assigning the XValues as an array or a range?

If you are assigning the XValues as an array then Excel attempts to list
each element in the array (including decimals if any) in the chart's Series
Formula and easily exceeds the formula capacity. The appended code
demonstrates this. The two commented out lines work while the array approach
fails if and only if there are too many elements referenced.

To see this, select the chart series and examine the series formula in the
formula bar.

If the above doesn't solve the problem then please post your code.

Regards,
Greg


Sub SetChartVAlues()
Dim cht As Chart
Dim i As Long
Dim ArrX() As Variant, ArrY() As Variant
Dim Xrng As Range, Yrng As Range

Set Xrng = Range("A1:A50")
Set Yrng = Range("B1:B50")
Set cht = ActiveSheet.ChartObjects(1).Chart
With cht.SeriesCollection(1)
'.Values = Yrng
'.XValues = Xrng
ReDim ArrX(1 To Xrng.Count)
ReDim ArrY(1 To Yrng.Count)
For i = 1 To UBound(ArrX)
ArrX(i) = Xrng(i)
ArrY(i) = Yrng(i)
Next
.Values = ArrY
.XValues = ArrX
End With
End Sub

"Daniel" wrote:

Hi All,
I create a chart thru macro.
The chart is well created when the source column is less than 32.
After exceeding the column, the runtime error arise.
The error as follow:
"Unable to set the XValues property of the Serial class"

what it means?? I really out of ideas?
Do appreciate any guideline / solution to it

Thank you
Best regards,
Ocurnos

As you are probably aware, there is a problem using noncontiguous ranges as
chart data sources. There is no good workaround to my knowledge other than
avoiding noncontiguous ranges or having a helper column/row that is
contiguous that the chart actually references. This can be in a hidden
worksheet if you like.

When there are (or potentially are) several data points to plot then the
only good way IMHO is to reference a range address instead of listing each
element as you are in effect doing. If you display the formula bar and select
the chart series and examine the Series Formula you can see that it is very
large when you do it your way. And thus it easily runs out of capacity.
Imagine having 2000 data points to reference doing it this way!!! Yet one
can easily plot 2000 points and much more referencing a range address instead.

I suggest using a hidden worksheet and referencing a contiguous range in
this worksheet. I use hidden worksheets for most of my projects to store all
sorts of things. IMO they are very practical.

Regards,
Greg

"Daniel" wrote:

Hi Greg,
i use looping to create the following output:
strSerial = "=(Summary!R5C"
Do While i <= intMaxCol
strSerial = strSerial & i
If Not i = intMaxCol Then
strSerial = strSerial & ",Summary!R5C"
Else
strSerial = strSerial & ")"
End If
i = i + 2
Loop

output:
=(Summary!$C$5,Summary!$E$5,Summary!$G$5,Summary!$ I$5,Summary!$K$5,Summary!$M$5,Summary!$O$5,Summary !$Q$5,Summary!$S$5,Summary!$U$5,Summary!$W$5,Summa ry!$Y$5,Summary!$AA$5,Summary!$AC$5,Summary!$AE$5, Summary!$AG$5,Summary!$AI$5,Summary!$AK$5,Summary! $AM$5,Summary!$AO$5,Summary!$AQ$5,Summary!$AS$5,Su mmary!$AU$5,Summary!$AW$5,Summary!$AY$5)

Next step:
I directly assign the output to XValues as follow:
chtChart.SeriesCollection(1).XValues = strSerial

Is that too many elements referenced as u said?
If yes, any better idea to solve it?

thank you in advance.
Best regards,
Ocurnos

"Greg Wilson" wrote:

Are you assigning the XValues as an array or a range?

If you are assigning the XValues as an array then Excel attempts to list
each element in the array (including decimals if any) in the chart's Series
Formula and easily exceeds the formula capacity. The appended code
demonstrates this. The two commented out lines work while the array approach
fails if and only if there are too many elements referenced.

To see this, select the chart series and examine the series formula in the
formula bar.

If the above doesn't solve the problem then please post your code.

Regards,
Greg


Sub SetChartVAlues()
Dim cht As Chart
Dim i As Long
Dim ArrX() As Variant, ArrY() As Variant
Dim Xrng As Range, Yrng As Range

Set Xrng = Range("A1:A50")
Set Yrng = Range("B1:B50")
Set cht = ActiveSheet.ChartObjects(1).Chart
With cht.SeriesCollection(1)
'.Values = Yrng
'.XValues = Xrng
ReDim ArrX(1 To Xrng.Count)
ReDim ArrY(1 To Yrng.Count)
For i = 1 To UBound(ArrX)
ArrX(i) = Xrng(i)
ArrY(i) = Yrng(i)
Next
.Values = ArrY
.XValues = ArrX
End With
End Sub

"Daniel" wrote:

Hi All,
I create a chart thru macro.
The chart is well created when the source column is less than 32.
After exceeding the column, the runtime error arise.
The error as follow:
"Unable to set the XValues property of the Serial class"

what it means?? I really out of ideas?
Do appreciate any guideline / solution to it

Thank you
Best regards,
Ocurnos

Thanks Greg.

"Greg Wilson" wrote:

As you are probably aware, there is a problem using noncontiguous ranges as
chart data sources. There is no good workaround to my knowledge other than
avoiding noncontiguous ranges or having a helper column/row that is
contiguous that the chart actually references. This can be in a hidden
worksheet if you like.

When there are (or potentially are) several data points to plot then the
only good way IMHO is to reference a range address instead of listing each
element as you are in effect doing. If you display the formula bar and select
the chart series and examine the Series Formula you can see that it is very
large when you do it your way. And thus it easily runs out of capacity.
Imagine having 2000 data points to reference doing it this way!!! Yet one
can easily plot 2000 points and much more referencing a range address instead.

I suggest using a hidden worksheet and referencing a contiguous range in
this worksheet. I use hidden worksheets for most of my projects to store all
sorts of things. IMO they are very practical.

Regards,
Greg

"Daniel" wrote:

Hi Greg,
i use looping to create the following output:
strSerial = "=(Summary!R5C"
Do While i <= intMaxCol
strSerial = strSerial & i
If Not i = intMaxCol Then
strSerial = strSerial & ",Summary!R5C"
Else
strSerial = strSerial & ")"
End If
i = i + 2
Loop

output:
=(Summary!$C$5,Summary!$E$5,Summary!$G$5,Summary!$ I$5,Summary!$K$5,Summary!$M$5,Summary!$O$5,Summary !$Q$5,Summary!$S$5,Summary!$U$5,Summary!$W$5,Summa ry!$Y$5,Summary!$AA$5,Summary!$AC$5,Summary!$AE$5, Summary!$AG$5,Summary!$AI$5,Summary!$AK$5,Summary! $AM$5,Summary!$AO$5,Summary!$AQ$5,Summary!$AS$5,Su mmary!$AU$5,Summary!$AW$5,Summary!$AY$5)

Next step:
I directly assign the output to XValues as follow:
chtChart.SeriesCollection(1).XValues = strSerial

Is that too many elements referenced as u said?
If yes, any better idea to solve it?

thank you in advance.
Best regards,
Ocurnos

"Greg Wilson" wrote:

Are you assigning the XValues as an array or a range?

If you are assigning the XValues as an array then Excel attempts to list
each element in the array (including decimals if any) in the chart's Series
Formula and easily exceeds the formula capacity. The appended code
demonstrates this. The two commented out lines work while the array approach
fails if and only if there are too many elements referenced.

To see this, select the chart series and examine the series formula in the
formula bar.

If the above doesn't solve the problem then please post your code.

Regards,
Greg


Sub SetChartVAlues()
Dim cht As Chart
Dim i As Long
Dim ArrX() As Variant, ArrY() As Variant
Dim Xrng As Range, Yrng As Range

Set Xrng = Range("A1:A50")
Set Yrng = Range("B1:B50")
Set cht = ActiveSheet.ChartObjects(1).Chart
With cht.SeriesCollection(1)
'.Values = Yrng
'.XValues = Xrng
ReDim ArrX(1 To Xrng.Count)
ReDim ArrY(1 To Yrng.Count)
For i = 1 To UBound(ArrX)
ArrX(i) = Xrng(i)
ArrY(i) = Yrng(i)
Next
.Values = ArrY
.XValues = ArrX
End With
End Sub

"Daniel" wrote:

Hi All,
I create a chart thru macro.
The chart is well created when the source column is less than 32.
After exceeding the column, the runtime error arise.
The error as follow:
"Unable to set the XValues property of the Serial class"

what it means?? I really out of ideas?
Do appreciate any guideline / solution to it

Thank you
Best regards,
Ocurnos