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 left edge of the sheet and 1/2 row down from the top of the sheet. The dimensions of the chart are 8 columns wide and 20 rows high.

Example 21-1. Creating an Embedded Chart

Sub CreateAChart()

' Create an embedded chart

Dim co As ChartObject Dim cw As Long, rh As Long

' Get data for positioning chart cw = Columns(1).Width rh = Rows(1).Height

' Position chart using column width and row height units Set co = ActiveSheet.ChartObjects.Add(cw * 3, rh * 0.5, cw * 8, rh * 20)

' Name it co.Name = "ChartExample"

Debug.Print co.Name Debug.Print co.Chart.Name

' Set chart type co.Chart.ChartType = xlLine End Sub

The output of the Debug.Print statements are:

ChartExample Sheet1 Chart 1

The chart appears as in Figure 21-4. (Yes, the chart is empty.)

Figure 21-4. The results of creating an embedded chart object

1

A

S

: "B— E

7 ■

G H | 1

J K L

Duarlrly

J

A

J

U

C

an

5

D

100

JJ

F

IB

'0

■ 1

■3 13

B [?

■u

■9

21

¡2

23

You may have noticed that the chart in Figure 21-4 is a trifle uninteresting. We will need to use the various properties and methods of the Chart object (or the ChartWizard method) to create a useful chart.

Note that, although the documentation does not discuss the matter, experimentation shows that the Name property of the Chart object appears to be read-only. Indeed, the code:

co.Chart.Name = "AChart"

results in the error message: "Method Name of object _Chart failed."

Note also that we can run the CreateAChart procedure multiple times without error and this will produce multiple ChartObject objects with the same name! Thus, the name property seems to be of little use for both ChartObject objects and embedded Chart objects. In fact, after running the CreateAChart procedure twice and getting two charts named ExampleChart, the code:

Debug.Print ActiveSheet.ChartObjects(1).Name Debug.Print ActiveSheet.ChartObjects(2).Name ActiveSheet.ChartObjects("ChartExample").Left = 600

actually produces the output:

ChartExample ChartExample and moves one of the charts to the new position specified by the Left property!

0 0

Post a comment