Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 when setting a Series Collection.Values as Array via VB
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.1 5,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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 when setting a Series Collection.Values as Array via VB
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. "David Messenger" wrote: 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.1 5,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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 when setting a Series Collection.Values as Array via VB
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 On 7 Jul, 10:08, David Messenger wrote: 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.1 5,1,0.15,1,0.15,1,0.15,1,*0.15,1,0.15,1,0.15,1,0.1 5,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.1*5,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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 when setting a Series Collection.Values as Array vi
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 -- Regards, Tom Ogilvy "David Messenger" wrote: 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. "David Messenger" wrote: 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.1 5,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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 when setting a Series Collection.Values as Array vi
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. "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "David Messenger" wrote: 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. "David Messenger" wrote: 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.1 5,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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 when setting a Series Collection.Values as Array vi
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" wrote: 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 On 7 Jul, 10:08, David Messenger wrote: 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.1 5,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.1 5,*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.1*5,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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 when setting a Series Collection.Values as Array vi
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error 1004 when setting pivottable.pagefields | Excel Discussion (Misc queries) | |||
Setting name of series - run-time error 1004 | Excel Programming | |||
Series Collection Results in Runtime Error 1004 | Excel Programming | |||
Error 1004: Unable to set the XValues of the Series Class | Excel Programming | |||
EXCEL VB Macro Run Time Error 1004 - Setting ColorIndex | Excel Programming |