Multiple large .NewSeries lead to Run-time error 1004

[Dave Booker]

I have the following code block, operating on a chart object named currChart,
which creates a set of series that reference cells from a worksheet object
named simulationSheet:

Dim newSeries As Series
For i = 1 To numSeries
'Create a new series
Set newSeries = currChart.SeriesCollection.NewSeries
newSeries.name = simulationSheet.Cells(1, i)
newSeries.Values =
simulationSheet.Range(simulationSheet.Cells(2, i),
simulationSheet.Cells(numDays, i))
Next i

This code works great when numDays is small, less than 60. However, if I
try to execute this code when numDays is large, say around 1600, then the
first series I create works. But the second time (when i = 2) the series is
created, and then on the next line (where it modifies the name) I get the
following error:

Run-time error ‘1004’: Unable to set the Name property of the Series class

Also, if I inspect the newSeries object after it has been created, many of
its member objects (i.e. AxisGroup, ErrorBars, Formula…) have the value:

<Unable to get the (object type AxisGroup,ErrorBars,Formula, …) property of
the Series class>

 

[Kevin Yu [MSFT]]

Hi,

We have reviewed this issue and are currently researching on it. We will
update you ASAP. Thanks for your patience!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jon Peltier]

While Kevin's working on it, try i=256, then i=257. In your code, i is the column
index, and each worksheet is limited to 256 columns.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Dave Booker]

i is always limited to something much smaller than 256. It's the row index,
"numDays", whose magnitude is associated with this problem.

 

[Jon Peltier]

Sorry, I missed that.

Does the range specified in the code contain any plottable data? If not, several
chart types (line and XY for example) will not be able to access the series formula,
Xvalues, values, or name in VBA. Can you manually create the series with the
proscribed data range?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Dave Booker]

Yes, it's valid data, and it can be manually charted. Besides, it crashes
before you even try to reference the data range.

 

[Jon Peltier]

This procedure ran for a number of values of numDays:

Sub ChartMe()
Dim newSeries As Series
Dim i As Long
Dim currChart As Chart
Dim simulationSheet As Worksheet
Dim numDays As Long
Dim numSeries As Integer

numSeries = 5
numDays = 3200 ' 6, 60, 600
Set currChart = ActiveChart
Set simulationSheet = ActiveSheet

For i = 1 To numSeries
'Create a new series
Set newSeries = currChart.SeriesCollection.newSeries
newSeries.Name = simulationSheet.Cells(1, i)
newSeries.Values = _
simulationSheet.Range(simulationSheet.Cells(2, i), _
simulationSheet.Cells(numDays, i))
Next i

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______