Programming Excel with VBA and .NET

Plot a Series

The data plotted on a chart belongs to the chart’s SeriesCollection . Each column or row of data is represented by a Series object and each item in the series is represented by a Point object.

You can add series to existing charts by calling the Add, Extend, or NewSeries methods of the SeriesCollection. For example, the first of the following procedures creates a new line chart, and the second plots a new series using the column chart type:

Sub CreateChart(  )
    Dim chrt As Chart
    Set chrt = Charts.Add(, ActiveSheet)
    chrt.Name = "Plot a Series"
    chrt.SetSourceData [HomeSales], xlColumns
    chrt.ChartType = xlLine
End Sub
 
Sub AddNewSeries(  )
    Dim chrt As Chart, sc As SeriesCollection, _
      sr As Series
    ' Get the chart 
.
    Set chrt = Charts("Plot a Series")
    ' Get the series collection.
    Set sc = chrt.SeriesCollection
    ' Add a new series.
    sc.Add [FLGrowth], xlColumns, True, False, False
    ' Get the last series
    Set sr = sc(sc.Count)
    ' Change the chart type for the series.
    sr.ChartType = xlColumnClustered
End Sub

The PlotBy arguments (xlRows or xlColumns) should match when adding series to a chart, unless of course the new data is arranged differently from the existing data.