Page 137 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
The Application Object

ActiveChart Property
The ActiveChart property lets you manipulate the currently selected chart, whether it is an
embedded chart or a chart sheet. All of the properties of the chart are available, which means
you can change the data points on the chart, the format of the chart, and other attributes.
The following example automatically builds a three-dimensional column chart in the
SalesByCategory.xls workbook using the existing data and positions it beneath the data
source, as shown in Figure 6-3.

Sub BuildChart()
Charts.Add 'Add a new chart object
ActiveChart.ChartType = xl3DColumn 'Set the Chart type to 3D Column
ActiveChart.SetSourceData _
Source:=Sheets("ByCategory").Range("C1:G13"), _
PlotBy:=xlColumns 'Set the data source
ActiveChart.Location Where:=xlLocationAsObject, Name:="ByCategory"
With ActiveChart 'Format the chart
.HasTitle = True
.ChartTitle.Characters.Text = "Monthly Sales by Category"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Month"
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Characters.Text = "Category"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Sales"
End With
ActiveSheet.Shapes(1).IncrementLeft -133.5 'Position the chart
ActiveSheet.Shapes(1).IncrementTop 214.5
ActiveSheet.Shapes(1).ScaleWidth 1.77, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(1).ScaleHeight 1.35, msoFalse, msoScaleFromTopLeft
End Sub

For more information on charts and how to change them programmatically, see Chapter 15, “Charts.”

Chapter 6

111