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:
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
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.
Was this article helpful?