Microsoft Office Tutorials and References
In Depth Information
Figure 21-3. Step 4 in the chart wizard
As you probably know, Excel charts can reside in a special type of sheet called a chart sheet or
they can be embedded in an ordinary worksheet. Accordingly, a Chart object can represent a chart
sheet (standalone chart) or an embedded chart. In the latter case, the Chart object is not contained
directly in a worksheet. Rather, the worksheet contains a ChartObject object that acts as a
container for the Chart object.
Thus, for instance, if we create a new chart using the chart wizard, the fourth step in the wizard
displays the dialog shown in Figure 21-3 .
Figure 21-3. Step 4 in the chart wizard
If we choose the "As new sheet" option in step 4 of the chart wizard, we can access the resulting
chart using the code:
Dim c as Chart
Set c = ThisWorkbook.Charts("Chart1")
On the other hand, choosing the "As object in" option in step 4 of the chart wizard, we access the
chart using the code:
Dim c As Chart
Set c = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
Note the space between the word Chart and the number 1 in the name of the ChartObject object,
but not in the name of the Chart object.
We emphasize that there is no ChartSheet object. The Charts property of the Application object
returns a so-called Sheets collection containing one Chart object for each chart sheet. It does not
contain Chart objects for the embedded charts.
21.2 Creating a Chart
We have seen that a PivotTable is created and added to the PivotTables collection by invoking the
PivotTableWizard method. On the other hand, creating a new chart requires a different approach,
since it depends upon whether the chart is standalone (a chart sheet) or embedded in a worksheet
(and thus contained in a ChartObject object).
Also, unlike the PivotTableWizard method, the ChartWizard method does not create a chart; it
merely formats an existing chart. Accordingly, there are three steps required to create a
meaningful chart:
Search JabSto ::

Custom Search