Error 1004 when setting a Series Collection.Values as Array via VB

[David Messenger]

I am working in Excel 2000 with a Doughnut Chart.

I am trying to set-up the Chart with Series data “hard coded†into it,
rather than relying on a specified Range (because I want to part of the Graph
to be “fixed†and the other part variable via the Worksheet).

Extract from VB Code

With ActiveChart

.SeriesCollection(1).Values = Dial_Values

More CODE

End With

Where Dial_Values is the String Variable

“={1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,0.15,1,13.75}â€

This has 71 numbers in it…….
Code works for any combination of 71 numbers or below (ie 60, 50 numbers
etc) and with any combination of Values (ie 1,0.15 or 1, 0.2 etc).

If try ABOVE 71 numbers get

Runtime Error 1004
“Unable to set the Values property of the Series Classâ€

Tried both using the String Variable and with the value of the String
directly written as Code.

However if I manually go into the Graph and add more than 71 numbers as an
Array into the Source Data it works ?

Same issue occurs if taking same approach with Xvalues.

Am I doing something wrong or is 72 some undefined internal limit ?

Obscure question I know but ... Any suggestions ?

Thanks

 

[David Messenger]

Update.

Changed Code to pass in Values as an variable Array rather than a String.

ie .SeriesCollection(1).Values = Dial_Values

Where Dial_Values is now an Array.

This reduced the problem ... could now get to 90 numbers .... an increase of
20.

Tried changing the values passed in and it seems to somehow relate to the
number of Characters passed in (proximity to 255 ?) because if I use smaller
length numbers in the sequence (ie 10,1 rather than 1,0.10) I can pass in
more Numbers in the Array and get the Graph to update without Error 1004.

Even when I am at "Code limit" of 90 .... can still MANUALLY type in many
more using the Source Data Dialog and it works fine.

This makes me more puzzled than before.

 

[RichardSchollar]

Hi David

When applying array constants to the SeriesCollection there is an
upper limit to the number of characters that you can use (and it's
about 251 or so) - your array constant is exceeding this I'm afraid.
Another option might be to write these values to a range (eg in a
hidden sheet) and then refer the SeriesCollection to this range.

Hope this helps!

Richard

 

[Tom Ogilvy]

Chart Series Array

Sub Series()
Sheets("Sheet1").Select ActiveSheet.ChartObjects(1).Select
Dim v(1 To 1000)
For i = 1 To 1000
v(i) = i
Next
ThisWorkbook.Names.Add Name:="List", _
RefersTo:=Application.Transpose(v)
Set ns = ActiveChart.SeriesCollection.NewSeries
With ns
.Values = "=" & ThisWorkbook.Name & "!List"
End With
End Sub


You need to transpose an array as shown greater than 256 elements

 

[David Messenger]

Thanks Tom

Since I already had the "Array" part, and the "SeriesCollection.Values" part
I just added the "ThisWorkbook.Names.Add" Name part in the middle.
Clever idea using Names to store an Array of constants! Read about using
Names to store Constants last week in a book and didn't think of it here.
Graph now works fine with Error 1004 not to be seen.
So it ended up being a 1 line fix !

I was trying to "hard code" the values to allow the Graph to be copied over
to another Workbook without dealing with all the separate Series links.
Now if that becomes and issue I will add some Code to Copy over the "Name"
to the Destination Workbook as well and reset the Series to come off the
"Name" in the Destination Workbook.

 

[David Messenger]

Thanks Richard,

Went with Toms Idea above.
Still don't get why can change the SeriesCollection via the Dialog to a
large array > 251 characters but can't do it in VB ... but problem now
bypassed with Toms solution.

 

[RichardSchollar]

That was a great solution Tom. I didn't know you could do that -
something I shall file away for use in the future!

Richard