Page 347 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Chapter 15

Charts

Introducing Charts . . . . . . . . . . . . . . . . 321 Final Thoughts on
Manipulating Charts . . . . . . . . . . . . . . . 327 Programming Charts . . . . . . . . . . . . . . . 339

Microsoft Excel’s charting feature is an impressive tool. A chart allows the user to graphically
display almost any type of data stored in a worksheet. There are times when a chart is more
meaningful than a list of numbers. For example, a user might have a better grasp of the
increase and decrease of monthly sales if shown using a line or bar chart. To write the same
kind of information in simple text would take many paragraphs and not flow all that well.
When creating charts, there are more than 100 types to choose from. With the many objects
contained within the charts, such as the Legend, the x-axis and the y-axis, and the Chart
Titles, the end result will differ greatly from chart to chart depending on your formatting
requirements.

The Chart object itself and the objects contained within the chart can be modified directly by
the user or with a Visual Basic for Applications (VBA) macro.
In this chapter, you’ll learn about the key concepts required to write the VBA code that
generates and manipulates charts. The code is designed to show you how to create and gain
access to the ChartObjects collection within the workbook. You’ll also learn how to modify
existing charts, their data series, and their formats, as well as how to use descriptive chart
labels that refer to cell references.

Introducing Charts

The chart itself is considered an object within the Excel workbook; the ChartObject object
acts as a container for the items located within it. These items include the source data plotted
on the Chart Area, the Legend, and the Chart Titles. Each object within the chart contains
its own properties and methods. Because of the complexity of the chart Object Model,
manipulating charts with VBA code can be a challenge. A good understanding of the Chart
model is vital when programming Chart objects to ensure that you are using the correct
object and property.

Note Refer to the “Defining the Chart Object Model” section located later in this chapter
to review how the Chart Object Model is structured.

321