Understanding ChartObject versus Chart in Excel VBA

[jochoalions]

In Excel VBA, how should I understand a ChartObject versus a Chart? I understand that they have different properties and different methods.

Is there a metaphore or a heuristic that would help me gain a better understanding of the programming intent for each one?

 

[Jon Peltier]

The ChartObject is the shape embedded in a sheet that contains a Chart. The ChartObject has most of the properties and behaviors of other shapes.

A Chart is the graphical object that is either contained within a ChartObject or displayed on its own sheet. The Chart contains the data and the shapes (lower-case shapes) used to display the data. If the Chart is serving as its own sheet (not a worksheet but a chart sheet) it has other properties shared with sheets. Chart sheets are not as useful nor as commonly used as they used to be.

 

[jochoalions]

Thank you, that is indeed a useful explanation. With that in mind, would the workflow be thus, to create an embedded chart, say in the sheet that contains the data? The chart once created and fine-tuned could later be moved to its own sheet.

1. Select the Data sheet, and create a ChartObject.
2. Create a Chart inside the ChartObject.
3. Add each required Data Series to the Chart.
4. Format the Chart (chart area, title, legend, etc.) as desired.
5. Format each Data Series as desired.

Each data series becomes an individual object in the Series collection, correct?, and therefore can be accessed individually, correct?

 

[Jon Peltier]

You create the chart in one step, with Excel creating the ChartObject container and its contents. You assign the data. You format all the chart elements. Just like you do manually in Excel.

Turn on the Macro Recorder and make a few charts, then look at the recorded code. It's ugly and verbose, but it will give you an idea of how the API works.

I have lots of examples on my web site, too. Some of those might be helpful.