Microsoft Office Tutorials and References
In Depth Information
Lesson 14: Programming Charts
When I started to program Excel in the early 1990s, I remember being impressed with the
charting tools that came with Excel. They were very good back then, and today’s chart
features in Excel are downright awesome, rivaling — and usually surpassing — the charting
packages of any software application.
Because you are reading this topic, chances are pretty good that you’ve manually created your
share of charts in Excel using the Chart Wizard or by selecting a chart type from the dozens of
choices on the Ribbon. You might also have played with the Macro Recorder to do some
automation of chart creation. This lesson takes you past the Macro Recorder’s capabilities to show
how to create and manipulate embedded charts and chart sheets.
The topic of charting is one that can, and does, ill entire books. The myriad of chart types
and features that Excel makes available to you goes well beyond the scope of this lesson. What
this lesson does is to show you the syntaxes for several methods that work for embedded
charts and chart sheets, with a few different features and chart types represented in the
programming code. From the VBA examples in this lesson, you can expand your chart
programming skills by substituting the chart types and features shown for others that may be more
suited to the kinds of charts you want to develop.
In the examples, you might notice that the charts being created are declared as
a Chart type object variable, which makes it easier to refer to the charts when
you want to manipulate them in code. In any case, there are two separate object
models for charts. For a chart on its own chart sheet, it is a Chart object. For a
chart embedded on a worksheet, it is a ChartObject object. Chart sheets are a
member of the workbook’s Charts collection, and each ChartObject on a
worksheet is a member of the worksheet’s ChartObjects collection.