Creating an Embedded Chart In Excel VBA

Embedded charts exist within the object hierarchy beneath worksheets. The Worksheets collection contains a sub-collection called ChartObjects, which includes all the chart frames on a given worksheet. Each ChartObject represents a single chart frame. The actual chart itself is an object of type Chart and corresponds to the Chart property of a chart frame. Apart from this organizational structure, both embedded and chart sheet charts function similarly.

The following procedure creates a simple line chart embedded directly into the worksheet named « Sheet1 »:

Sub CreateEmbeddedChart()
    Dim CO As ChartObject
    Dim CH As Chart
    Set CO = ThisWorkbook.Worksheets("Sheet1").ChartObjects.Add(200, 10, 300, 150)
    Set CH = CO.Chart
    CH.ChartType = xlLine
    CH.SetSourceData Worksheets("Sheet1").Range("A1:C8")
    Set CH = Nothing
    Set CO = Nothing
End Sub

The result is shown in Figure 7.3.
Figure 7.3 illustrates the embedded chart.

Explanation:
First, a variable of type ChartObject is declared. This variable will later reference the newly created chart frame.

Next, a variable of type Chart is declared. This will refer to the chart inside the newly created chart frame.

The ChartObjects collection holds all embedded chart frames on a worksheet. The Add() method creates a new chart frame at a specified position and size on the worksheet, returning a reference to this newly created object. The four parameters of Add() represent:

• The distance in points from the left edge of the worksheet to the left edge of the chart frame
• The distance in points from the top edge of the worksheet to the top edge of the chart frame
• The width of the chart frame in points
• The height of the chart frame in points

The Chart property of a ChartObject accesses the actual chart contained within the frame.

The ChartType property specifies the type of the chart, here set to xlLine for a line chart.

The SetSourceData() method assigns the data source for the chart. In this example, the source data is the range A1:C8 on the active worksheet.