Chart object

The Chart object allows you to create, modify, and format a chart. The Charts collection contains all of the currently open chart sheets in a workbook. It does not contain charts that are embedded in worksheets.

Creating a chart

Use the Add method to create a new chart and add it to the Charts collection.

{Add a new chart to the Charts collection.}
chart = app.Charts.Add();

Retrieving a chart

You can retrieve a chart from the Charts collection with the Item property by the number associated with a chart.

{Retrieve the first chart.}
chart = app.Charts.Item[1];

You can also retrieve charts by the name of the chart.

{Retrieve the chart named "Chart1".}
chart = app.Chart.Item["Chart1"];

The Charts collection does not contain charts that you have embedded in a worksheet. You must use the ChartObjects method to retrieve an embedded chart as a ChartObject object. You must provide either the name or number associated with the chart.

{Retrieve the third embedded chart from the worksheet.}
embedChart = worksheet.ChartObjects(3);

Setting the source data

To set the source data for a chart, use the SetSourceData method.

{Specify a range.}
cellRange = worksheet.Range["A1:B5"];
{Set the source data.}

Setting the chart type

To set the type of chart, use the ChartType property. Chart types are expressed by constants contained in the xlChartType enumeration.

{Set the chart type to a pie chart.}
chart.ChartType = Excel.xlPie;

Setting the chart location

A chart can either be a separate sheet or be embedded in a worksheet. To specify the location of a chart, use the Location method and the constants contained in the xlChartLocation enumeration. After using the Location method, you need use the ActiveChart property to retrieve that chart again.

{Embed a chart in a worksheet.}
chart.Location(Excel.xlLocationAsObject, "Sheet1");
{Retrieve the chart again.}
chart = app.ActiveChart;

An embedded chart is not contained in the Charts collection.


Using the chart wizard

Use the ChartWizard method to set several properties for a chart at one time. The ChartWizard method allows you to set the labels, the source data, the chart type, the properties of the legend, as well as formats for the chart. All parameters are optional.

{Create a 3D pie chart from the values in the specified cellRange. The chart does not have a legend and is titled "Sales Performance".}
chart.ChartWizard(cellRange, Excel.xl3DPie, none, none, none, none, false, "Sales Peformance", none, none, none);

The ChartWizard method does not accept all of the values from the xlChartType enumeration. Refer to the Microsoft Excel Object Library help file for more information.

Documentation Feedback