Page 349 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Charts

Sub AddChartSheet()
Dim Chrt As Chart
Set Chrt = Charts.Add
With Chrt
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets(“Sheet1”).Range(“A4:D7”), _
PlotBy:=xlRows
.HasTitle = True
End With
End Sub

Note When creating charts in your workbook, create the chart as an object variable. It’s
easier to reference the Chart object and manipulate the chart when using this technique.
The remaining examples in this chapter will use this method to create charts.

By default, if the chart Location property has not been set in the macro, the chart is placed on
its own sheet, rather than as an embedded object. To set the chart location, you must use the
Chapter 15
Location property. Table 15-2 defines the three location options available.
Table 15-2. Chart Location Properties
Property Location
xlLocationAsNewSheet The chart is placed on a new chart sheet.
xlLocationAsObject The chart is placed as an embedded chart object within the
specified worksheet.
xlLocationAutomatic The chart is placed as an embedded chart object within the
active worksheet.

The following example sets the chart as an embedded object in “Sheet1”:

Variable = ChartObject.Location(xlLocationAsObject,"Sheet1”)

When working with embedded charts, it’s a good idea to name the ChartObject object so that
you can easily reference the chart in later code. To rename an existing chart manually, hold
down the Ctrl key and click the chart. This will select the ChartObject object, rather than
activate the chart. Click the Name Box, and type the new name. You are also able to name the
ChartObject object within the macro by setting the Name property of the object.
As displayed in Figure 15-1, notice that the chart handles are unfilled circles to help you dis­
tinguish whether the chart is selected or has been activated. The Name Box currently displays
Chart1 as the name of the ChartObject object.

323