Microsoft Office Tutorials and References
In Depth Information
Chapter 18: Working with Charts
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 won’t work with versions
prior to Excel 2007.
The Chart object model
When you first start exploring the object model for a Chart object, you’ll probably be very
confused — which isn’t 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
toplevel 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:
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:
Text = “YTD Sales”
This statement assumes the active workbook is 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.
Note that the preceding statement will fail if the chart doesn’t have a title. To add a default title
to the chart (which displays the text Chart Title ), use this statement:
Worksheets(“Sheet1”).ChartObjects(1).Chart.HasTitle = True