Microsoft Office Tutorials and References
In Depth Information
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
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 To p 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 To p and Left properties of the ChartObject object to be the same as the To p 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
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.
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.
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets(“Sheet1”).Range(“A3:D7”), _
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Product Sales"