+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Marco Shaw
    Guest

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

    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



  2. #2
    K Dales
    Guest

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

    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...b;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" wrote:

    > 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
    >
    >
    >


  3. #3
    Marco Shaw
    Guest

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

    > 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1