Class SeriesCollection (Excel VBA)

A collection of all the Series objects in the specified chart or chart group. To use a SeriesCollection class variable it first needs to be instantiated, for example

Dim scn as SeriesCollection
Set scn = ActiveChart.SeriesCollection()

Add

Adds one or more new series to the SeriesCollection collection.

This method does not actually return a Series object as stated in the Object Browser. This method is not available for PivotChart reports.

Add (Source, Rowcol, SeriesLabels, CategoryLabels, Replace)

Charts("Chart1").SeriesCollection.Add Source:=ActiveWorkbook.Worksheets("Sheet1").Range("B1:B10")

Arguments

The following argument is required

Source (Range) - The new data as a Range object.

Optional arguments

The following arguments are optional

Rowcol (XlRowCol) - Specifies whether the new values are in the rows or columns of the specified range.

Possible return values are xlColumns - Data series is in a row, xlRows - Data series is in a column.

SeriesLabels (Boolean) - True if the first row or column contains the name of the data series. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the series name from the contents of the first row or column.

CategoryLabels (Boolean) - True if the first row or column contains the name of the category labels. False if the first row or column contains the first data point of the series. If this argument is omitted, Excel attempts to determine the location of the category label from the contents of the first row or column.

Replace (Boolean) - If CategoryLabels is True and Replace is True, the specified categories replace the categories that currently exist for the series. If Replace is False, the existing categories will not be replaced. The default value is False.

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveChart.SeriesCollection.Count

Extend

Adds new data points to an existing series collection.

This method is not available for PivotChart reports.

Extend (Source, Rowcol, CategoryLabels)

Charts("Chart1").SeriesCollection.Extend Source:=Worksheets("Sheet1").Range("B1:B6")

Arguments

The following argument is required

Source (Range) - The new data to be added to the SeriesCollection object as a Range object.

Optional arguments

The following arguments are optional

Rowcol (XlRowCol) - Specifies whether the new values are in the rows or columns of the given range source. Can be one of the following XlRowCol constants: xlRows or xlColumns. If this argument is omitted, Microsoft Excel attempts to determine where the values are by the size and orientation of the selected range or by the dimensions of the array.

Possible return values are xlColumns - Data series is in a row, xlRows - Data series is in a column.

CategoryLabels (Boolean) - True to have the first row or column contain the name of the category labels. False to have the first row or column contain the first data point of the series. If this argument is omitted, Excel attempts to determine the location of the category label from the contents of the first row or column.

Item

Returns a single object from a collection.

Item (Index)

Index: The name or index number for the object.

Dim srsSeriesCollection As Series
Set srsSeriesCollection = ActiveChart.SeriesCollection(Index:=1)

NewSeries

Creates a new series. Returns a Series object that represents the new series.

This method isn't available for PivotChart reports.

Dim srsNewSeries As Series
Set srsNewSeries = ActiveChart.SeriesCollection.NewSeries()

Paste

Pastes data from the Clipboard into the specified series collection.

Paste (Rowcol, SeriesLabels, CategoryLabels, Replace, NewSeries)

Worksheets("Sheet1").Range("C1:C5").Copy 
Charts("Chart1").SeriesCollection.Paste

Arguments

Optional arguments

The following arguments are optional

Rowcol (XlRowCol) - Specifies whether the values corresponding to a particular data series are in rows or columns.

Possible return values are xlColumns - Data series is in a row, xlRows - Data series is in a column.

SeriesLabels (Boolean) - True to use the contents of the cell in the first column of each row (or the first row of each column) as the name of the data series in that row (or column). False to use the contents of the cell in the first column of each row (or the first row of each column) as the first data point in the data series. The default value is False.

CategoryLabels (Boolean) - True to use the contents of the first row (or column) of the selection as the categories for the chart. False to use the contents of the first row (or column) as the first data series in the chart. The default value is False.

Replace (Boolean) - True to apply categories while replacing existing categories with information from the copied range. False to insert new categories without replacing any old ones. The default value is True.

NewSeries (Boolean) - True to paste the data as a new series. False to paste the data as new points in an existing series. The default value is True.