A Class Module to Manipulate a Chart SERIES
Excel's object model has a serious flaw: There is no direct way to to use VBA to determine the ranges used in a chart. This tip contains a useful class module that can simplify the task of manipulating chart's using VBA.
Background Information
The Series object is contained in a Chart object. The SeriesCollection is a collection of Series objects for a particular Chart object. If a chart plots two data series it will have two Series objects. You can refer to a particular Series object by its index number. The expression below, for example, creates an object variable that represents the first Series object in the active chart:
Set MySeries = ActiveChart.SeriesCollection(1)
A Series object has many properties, but I'll list three that seem relevant to this discussion:
- Formula property: Returns or sets the SERIES formula for the Series. When you select a series in a chart, its SERIES formula is displayed in the formula bar. The Formula property returns this formula as a string.
- Values property: Returns or sets a collection of all the values in the series. This can be a range on a worksheet or an array of constant values, but not a combination of both.
- XValues property: Returns or sets an array of x values for a chart series. The XValues property can be set to a range on a worksheet or to an array of values -- but it can't be a combination of both.
If your VBA code needs to determine the data range used by a particular chart series, it's obvious that the Values property of the Series object is just the ticket. And, you can use the XValues property to get the range that contains the x values (or category labels). In theory, that certainly seems correct -- but in practice, it doesn't work. You'll find that he XValues and Values properties return a variant array. Unfortunately, there is no direct way to get a Range object for a Series object.
Note: When you set the Values property for a Series object, you can specify a Range object or an array. But when you read this property, it is always an array. Go figure.
About a Chart's SERIES formula
Every data series in a chart has a SERIES formula that determines the data used in the series. Here's an example of a SERIES formula for a chart series:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1)
A SERIES formula is comprised of four arguments:
- Series Name. Can be a cell reference or a literal string. Optional.
- XValues. Can be a range reference (including a non-contiguous range reference) or an array. Optional.
- Values. Can be a range reference (including a non-contiguous range reference) or an array.
- Plot Order. Must be an integer.
The ChartSeries calls module, described below, essentially parses and analyzes a chart's SERIES formula.
The ChartSeries Class Module
I created a class module named ChartSeries. When this class module is included in a workbook, your VBA code can create a new "ChartSeries" object and manipulate the following properties of this object:
- Chart (read/write)
- ChartSeries (read/write)
- SeriesName (read/write)
- XValues (read/write)
- Values (read/write)
- PlotOrder (read/write)
- SeriesNameType (read-only)
- XValuesType (read-only)
- ValuesType (read-only)
- PlotOrderType (read-only)
Example Usage
The simple procedure below demonstrate how to use the ChartSeries class. It starts by creating a new object called ChartSeries. It sets the Chart property to an embedded chart, and sets the ChartSeries property to 1. It then uses the XValuesType property to determine the "type" of the XValues in the chart. If the chart uses a range, it displays the address of the range. If the chart uses something other than a range (that is, a literal array), it displays the array.
Sub ExampleUsage()
Dim MySeries As New ChartSeries
With MySeries
Chart = Sheets(1).ChartObjects(1).Chart
ChartSeries = 1
If .XValuesType = "Range" Then
MsgBox .XValues.Address
Else
MsgBox .XValues
End If
End With
End Sub
View or Download
Click the links below to view the class module code or download an example workbook.