A Class Module To Manipulate A Chart Series
A companion file is available: Click here to download
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.
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:
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)
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
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks