Microsoft Office Tutorials and References
In Depth Information
Chapter 18: Working with Charts
In Excel, a chart can be located in either of two places within a workbook:
h As an embedded object on a worksheet: A worksheet can contain any number of
h In a separate chart sheet: A chart sheet normally holds a single chart.
Most users create charts 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.
The fastest way to create a chart manually 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:
If a chart isn’t activated, the preceding statement generates an error.
As you see later in this chapter, you don’t need to activate a chart in order to
manipulate it with VBA.
The macro recorder and charts
If you’ve read other chapters in the topic, you know that I often recommend using the macro
recorder to learn about objects, properties, and methods. In the Excel 2007 edition of this topic,
I was forced to mention a serious problem with the macro recorder and charts: The macro
recorder simply didn’t record all your actions. Fortunately, this problem has been corrected in
Excel 2010. Recording chart macros works fairly well in Excel 2010. The generated code isn’t
perfect, but it’s much better than it was in Excel 2007.
As always, recorded macros are best viewed as a learning tool. The recorded code will almost
always steer you to the relevant objects, properties, and methods.