Page 351 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Charts

End With
End With
End Sub

Note Remember the default location is a chart sheet. So when the Location method of
the Chart object is used, the Chart object is re-created and any reference to the original
Chart object, which is the chart sheet, is destroyed. It’s necessary to assign the return
value of the Location method to the Chrt object variable so that it refers to the new Chart
object. To test this, step through your code and view the workbook. You’ll notice that the
chart sheet is initially created and then removed after the Location has been set to
xlLocationAsObject.

As you can see, the event procedure AddEmbeddedChart has introduced some additional set­
tings. The ChartTitle is assigned to a formula referring to cell A1. The location of the embed­
ded chart on the worksheet was set using the Top and Left properties of cell A9. The Parent
property of the Chart object was used to refer to the ChartObject object, and it was set by
defining the Top and Left properties of the ChartObject object to be the same as the Top prop­
erty of cell A9 and the Left property of cell A1. The chart is aligned with the top of cell A9, but
the chart will align with the left edge of cell A1. The AddEmbeddedChart macro finally assigns Chapter 15
the new name to the ChartObject object so that it can easily be referenced in the future.

Note When defining the chart title as a formula, you must use the R1C1 addressing
method, not the A1 addressing method.

Inside Out

The Recorded Macro and Creating Charts
The recorded macro generates code that is reasonably efficient. However, manipulating the
chart is easier if the chart is created as an object. The following example displays the
recorded macro, which uses the Add method to create a new chart. The macro defines the
ChartType property and then uses the SetSourceData method to define the ranges plotted
on the chart. The Location property defines the chart as a chart sheet and assigns the
name Product Sales to the sheet. Then the macro sets the HasTitle property to True so that
it can define the ChartTitle property. Finally, the code sets the HasTitle property of the axes
back to False, which is an unnecessary step.
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets(“Sheet1”).Range(“A3:D7”), _
PlotBy:= xlRows

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Product Sales"
continued

325