Microsoft Office Tutorials and References
In Depth Information
21.2.1 Creating Chart Sheets
Decide whether to create a standalone chart (a chart sheet) or an embedded chart.
Create the standalone chart or embedded chart as described in the following section.
Format the chart using either the ChartWizard method or using individual properties and
methods of the chart object.
21.2.1 Creating Chart Sheets
The Workbook object has a Charts property that returns the Charts collection of all chart sheets in
the workbook. We can use the Add method of the Charts collection to create and add a new
chartsheet to the workbook.
The syntax for the Add method is:
ChartsObject .Add( Before , After , Count )
As usual, this method returns a Chart object. The Before parameter specifies the sheet before
which the new sheet is added, and the After parameter specifies the sheet after which the new
sheet is added. Only one of these parameters can be specified at one time or an error will result. If
neither is set, the new chart is inserted before the active sheet.
The optional Count parameter is an integer that specifies the number of sheets to be added. The
default value is 1 .
For example, the following code creates a new chart sheet named "Sales":
Dim ch As Chart
Set ch = ThisWorkbook.charts.Add()
ch.Name = "Sales"
The Add method cannot be used to format the chart. As mentioned earlier, this must be done using
the various properties of the Chart object or the ChartWizard method, discussed later in the
21.2.2 Creating Embedded Charts
The Worksheet object also has a ChartObjects property that returns a ChartObjects collection,
which is the collection of all ChartObjects in the worksheet. As we have mentioned, a ChartObject
object is a container for a Chart object—that is, an embedded chart.
The ChartObjects collection has an Add method that is used to create a new embedded chart. The
syntax is:
ChartsObjectObject .Add( Left , Top , Width , Height )
where the required Left and Top parameters give the coordinates of the upper-left corner of the
chart (in points) relative to the upper-left corner of cell A1 on the worksheet, and Width and
Height specify the initial size of the chart (also in points). Recall that the InchesToPoints method
can be used to convert inches to points.
Note that the Add method returns a ChartObject object, rather than a Chart object. This is a bit
confusing, since the method creates both a ChartObject object and the contained Chart object. The
code in Example 21-1 creates a new ChartObject object called ExampleChart along with its
contained Chart object. It positions the chart so that its upper-left corner is three columns from the
Search JabSto ::

Custom Search