Page 352 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = “Product Sales"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

The recorded macro will create the chart; however, you’ll see that there are additional and
redundant lines of code added to the macro. Be sure to remove unnecessary lines of code
within a recorded macro.

Defining the Chart Object Model

The Chart Object Model at times can be overwhelming due to the layering effect. However,
use the Object Browser in the Visual Basic Editor to help you get your bearings as you begin
the task of coding procedures involving charts.
The hierarchy of a chart is determined by its location. For example, when working with an
embedded chart, if you would like to modify the text contained within the ChartTitle you’ll
need to review the object levels. The top-level object is the Application. The Application object
contains the Workbook object, and the Workbook object contains a Worksheet object. The
Chapter 15
Worksheet object contains a ChartObject object, which contains a Chart object. The Chart
object has a ChartTitle object, and the ChartTitle object contains a Characters object. The
Text property of the Characters object stores the text that is displayed as the chart’s title. To
summarize how each object is connected, refer to the following list:

Application
Workbook
Worksheet
ChartObject
Chart
ChartTitle
Characters
However, when working with a chart that is located on its own sheet you’ll see that the hier­
archy is simplified. A chart sheet is technically at the same level as the worksheet because it’s
simply a different type of sheet. Review the levels as indicated here, and notice that two levels
have been removed.

Application
Workbook
Chart
ChartTitle
Characters
The Charts collection holds the collection of chart sheets in a workbook. The Workbook
object is always the parent of the Charts collection. The Charts collection holds only the chart

326