VBA error: Unable to set the Values property of the Series class

[Marco Shaw]

Excel 2003 SP1

Trying to create a chart using VBA. The following two subs are edited a bit
after being created by the macro recorder.

The send sub errs on this line:
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R12C3:R22C3"

With the error:
Run-time error '1004':
Unable to set the Values property of the Series class

Column 3 is just a series of numbers. Strangely, the first
ActiveChart.SeriesCollection(1).Values line works OK, but then the 2nd
fails.

I can't figure it out...

Sub area_chart()
'
' area_chart Macro
'

'
Range("A1:C72").Select
Charts.Add
ActiveChart.ChartType = xl3DAreaStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C72"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R72C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.Parent.Name = "Area Chart"
End Sub

Sub area_chart2()
'
' area_chart2 Macro
'

'
ActiveSheet.ChartObjects("Area Chart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C3:R11C3"
ActiveChart.SeriesCollection(1).Name = "=""CRITICAL"""
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R12C3:R22C3"
ActiveChart.SeriesCollection(2).Name = "=""MAJOR"""
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R23C3:R33C3"
ActiveChart.SeriesCollection(3).Name = "=""MINOR"""
ActiveChart.SeriesCollection(4).Values = "=Sheet1!R34C3:R44C3"
ActiveChart.SeriesCollection(4).Name = "=""NORMAL"""
ActiveChart.SeriesCollection(5).Values = "=Sheet1!R45C3:R55C3"
ActiveChart.SeriesCollection(5).Name = "=""UNKNOWN"""
ActiveChart.SeriesCollection(6).Values = "=Sheet1!R56C3:R66C3"
ActiveChart.SeriesCollection(6).Name = "=""WARNING"""
Windows("vpo_report.xls").LargeScroll Down:=-1
End Sub

 

[Guest]

An ongoing issue in Excel: you can't programmatically access a series that
has all "#N/A" values, and that is what you get when you add a new series
without specifying the data values at the time of creation (see, for example,
http://support.microsoft.com/default.aspx?scid=kb;en-us;213379).

Solution: instead of NewSeries, use the Add method to create the series and
specify the values at the same time, e.g.:

ActiveChart.SeriesCollection.Add Source:=Range("C12:C22")
ActiveChart.SeriesCollection.Add Source:=Range("C23:C33")
ActiveChart.SeriesCollection.Add Source:=Range("C34:C44")
ActiveChart.SeriesCollection.Add Source:=Range("C45:C55")
ActiveChart.SeriesCollection.Add Source:=Range("C56:C66")

However, the number of series you need to add depends on the data range you
specified when you first created the chart. If column B also contains
numeric values, the chart will have 2 initially filled data series when you
create it (cols B and C); but if only column C then you will have only one
filled series. Since your error is coming when you try to use
SeriesCollection(2), I would guess you only have the one filled series (which
is why there was no error when you accessed SeriesCollection(1)).

Hope this clarifies things - it is a pretty murky subject, and frankly the
AddSeries method doesn't make much sense given the 'bug' ('design feature'?)
trying to access the series when it is blank.

 

[Marco Shaw]

Solution: instead of NewSeries, use the Add method to create the series
and

specify the values at the same time, e.g.:

That did the trick!

Thanks.

Marco