About Charts

An Excel chart is simply packed with objects, each of which has its own properties and methods . Because of this, manipulating charts with Visual Basic for Applications (VBA) can be a bit of a challenge - and, unfortunately , the macro recorder isn't much help in Excel 2007. In this chapter, I discuss the key concepts that you need to understand in order to write VBA code that generates or manipulates charts. The secret, as you'll see, is a good understanding of the object hierarchy for charts. But first, a bit of background about Excel charts.

Chart locations

In Excel, a chart can be located in either of two places within a workbook:

As an embedded object on a worksheet: A worksheet can contain any number of embedded charts.
In a separate chart sheet: A chart sheet normally holds a single chart.

Most charts are created manually by using the commands in the Insert Charts group . But you can also create charts by using VBA. And, of course, you can use VBA to modify existing charts.

Tip	The fastest way to create a chart is to select your data and then press Alt+F1. Excel creates an embedded chart and uses the default chart type. To create a new default chart on a chart sheet, select the data and press F11.

A key concept when working with charts is the active chart - that is, the chart that's currently selected. When the user clicks an embedded chart or activates a chart sheet, a Chart object is activated. In VBA, the ActiveChart property returns the activated Chart object (if any). You can write code to work with this Chart object, much like you can write code to work with the Workbook object returned by the ActiveWorkbook property.

Here's an example: If a chart is activated, the following statement will display the Name property for the Chart object:

 MsgBox ActiveChart.Name

If a chart is not activated, the preceding statement generates an error.

Note	As you see later in this chapter, it's not necessary to activate a chart in order to manipulate it with VBA.

The macro recorder and charts

If you've read other chapters in the book, you know that I often recommend using the macro recorder to learn about objects, properties, and methods. Unfortunately, Microsoft took a major step backward in Excel 2007 when it comes to recording chart actions. Although it's possible to record a macro while you create and customize a chart, there's a pretty good chance that the recorded macro will not produce the same result when you execute it.

Following is a macro that I recorded while I created a column chart, deleted the legend, and then added a shadow effect to the column series.

 Sub RecordedMacro()     ActiveSheet.Shapes.AddChart.Select     ActiveChart.SetSourceData Source:=Range("Sheet1!$A:$B")     ActiveChart.ChartType = xlColumnClustered     ActiveChart.Legend.Select     Selection.Delete ActiveSheet.ChartObjects("Chart 1").Activate     ActiveChart.SeriesCollection(1).Select End Sub

The chart is shown in Figure 18-1.

Figure 18-1: The macro recorder was turned on while this chart was created and customized.

Notice the following points:

Even though I selected the range before I turned on the macro recorder, the source data range is hard-coded in the macro. In other words, the recorded macro does not use the Selection object, which is typical of other recorded macros. In other words, it's not possible to record a macro that creates a chart from the selected range.
The macro also hard-codes the chart's name. When this macro is played back, it's very unlikely that the newly-created chart will be named Chart 1, so the macro will either select the wrong chart or end with an error if a chart with that name does not exist.
Although selecting the data series was recorded, the formatting command was not. So, if you would like to find out which objects and properties are used to format a data series, the macro recorder is of no help at all.

Compatibility Note

The VBA code in this chapter uses many new chart- related properties and methods that were introduced in Excel 2007. As a result, most of the code presented here will not work with previous versions of Excel.

Bottom line? In Excel 2007, Microsoft downgraded macro recording for charts to the point where it's virtually useless. I don't know why this happened , but it's likely that they just didn't have the time, and meeting the product ship date was deemed a higher priority.

Despite the flaws, recording a chart-related macro can still be of some assistance. For example, you can use a recorded macro to figure out how to insert a chart, change the chart type, add a series, and so on. But when it comes to learning about formatting, you're on your own. More than ever, you'll need to rely on the object browser and the Auto List Members feature.

The Chart object model

When you first start exploring the object model for a Chart object, you'll probably be very confused - which is not surprising; the object model is very confusing. It's also very deep.

For example, assume that you want to change the title displayed in an embedded chart. The top-level object, of course, is the Application object (Excel). The Application object contains a Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which contains a Chart object. The Chart object has a ChartTitle object, and the ChartTitle object has a Text property that stores the text that's displayed as the chart's title.

Here's another way to look at this hierarchy for an embedded chart:

 Application     Workbook         Worksheet             ChartObject                 Chart                     ChartTitle

Your VBA code must, of course, follow this object model precisely. For example, to set a chart's title to YTD Sales , you can write a VBA instruction like this:

 WorkSheets("Sheet1").ChartObjects(1).Chart.ChartTitle. _   .Text = "YTD Sales"

This statement assumes the active workbook as the Workbook object. The statement works with the first item in the ChartObjects collection on the worksheet named Sheet1 . The Chart property returns the actual Chart object, and the ChartTitle property returns the ChartTitle object. Finally, you get to the Text property.

For a chart sheet, the object hierarchy is a bit different because it doesn't involve the Worksheet object or the ChartObject object. For example, here's the hierarchy for the ChartTitle object for a chart in a chart sheet:

 Application     Workbook         Chart              ChartTitle

In terms of VBA, you could use this statement to set the chart title in a chart sheet to YTD Sales :

 Sheets("Chart1").ChartTitle.Text = "YTD Sales"

A chart sheet is essentially a Chart object, and it has no containing ChartObject object. Put another way, the parent object for an embedded chart is a ChartObject object, and the parent object for a chart on a separate chart sheet is a Workbook object.

Both of the following statements will display a message box with the word Chart in it:

 MsgBox TypeName(Sheets("Sheet1").ChartObjects(1).Chart) Msgbox TypeName(Sheets("Chart1"))

Note

When you create a new embedded chart, you're adding to the ChartObjects collection and the Shapes collection contained in a particular worksheet. (There is no Charts collection for a worksheet.) When you create a new chart sheet, you're adding to the Charts collection and the Sheets collection for a particular workbook.