Excel Object Model: Chart

Chart elements

Chart elements include

Axes
Axes Titles
Chart Title
Data Labels
Data Table
Error Bars
Grid Line
Legend

At least some of these elements seem to be related to the msoChartElementType enumeration.

Methods and properties

`Activate()`
`Activate()`	An event
`Application`
`ApplyChartTemplate()`
`ApplyDataLabels()`
`ApplyLayout()`
`AutoScaling`
`Axes()`
`BackWall`
`BarShape`	Used for 3D bar or column charts: a member of the `XlBarShape` enumeration (`xlBox`, `xlConeToMax`, `xlConeToPoint`, `xlCylinder`, `xlPyramidToMax` or `xlPyramidToPoint`)
`BeforeDoubleClick()`	An event
`BeforeRightClick()`	An event
`Calculate()`	An event
`CategoryLabelLevel`
`ChartArea`	A `ChartArea` object which represents the chart's complete chart area.
`ChartColor`	A number that represents chart's color scheme
`ChartGroups()`
`ChartObjects()`
`ChartStyle`	A number in the range from 1 to 48 representing the chart style.
`ChartTitle`	Note that `ChartTitle` is an object, to change the text of the title use `chart.chartTitle.text = …`.
`ChartType`	A member of the `XlChartType` enumeration.
`ChartWizard()`
`CheckSpelling()`
`ClearToMatchColorStyle()`
`ClearToMatchStyle()`
`CodeName`
`Copy()`
`CopyPicture()`
`Creator`
`DataTable`
`Deactivate()`	An event
`Delete()`
`DepthPercent`
`DisplayBlanksAs`	A member of `XlDisplayBlanksAs` enumeration
`Elevation`
`Evaluate()`
`Export()`	Creates a file that contains the image of a `chart` object.
`ExportAsFixedFormat()`
`Floor`
`FullSeriesCollection()`
`GapDepth`
`GetChartElement()`	Returns information about the element located at a given locaion (pixel x/y coordinates). This method is useful in combination with a chart's mouseUp event to create hyperlinks from the chart.
`HasAxis`
`HasDataTable`
`HasLegend`	See also the `Legend` property
`HasTitle`
`HeightPercent`
`Hyperlinks`
`Index`
`Legend`	A `Legend` object. See also the `HasLegend` property.
`Location()`
`MailEnvelope`
`MouseDown()`	An event
`MouseMove()`	An event
`MouseUp()`	An event
`Move()`
`Name`
`Next`, `Previous`	The next/previous worksheet
`OLEObjects()`
`PageSetup`
`Parent`
`Paste()`
`Perspective`
`PivotLayout`
`PlotArea`
`PlotBy`
`PlotVisibleOnly`
`PrintedCommentPages`
`PrintOut()`
`PrintPreview()`
`Protect()`
`ProtectContents`
`ProtectData`
`ProtectDrawingObjects`
`ProtectFormatting`
`ProtectionMode`
`ProtectSelection`
`Refresh()`
`Resize()`	An event
`RightAngleAxes`
`Rotation`
`SaveAs()`
`SaveChartTemplate()`
`Select()`
`Select()`	An event
`SeriesChange()`	An event
`SeriesCollection()`	Returns a `Series` or a `SeriesCollection` object
`SeriesNameLevel`
`SetBackgroundPicture()`
`SetDefaultChart()`
`SetElement()`
`SetSourceData()`
`Shapes`
`ShowAllFieldButtons`
`ShowAxisFieldButtons`
`ShowDataLabelsOverMaximum`
`ShowExpandCollapseEntireFieldButtons`
`ShowLegendFieldButtons`
`ShowReportFilterFieldButtons`
`ShowValueFieldButtons`
`SideWall`
`Tab`
`Unprotect()`
`Visible`
`Walls`

Chart types (xlChartType enumeration)

A chart can be drawn in various types which are identified by the xlChartType enumeration.

The chart's property that corresponds its type is .chartType (not .type). Besides that, a chart has also a .subtype property.

			Default chart style	Example
`xl3DArea`	-4098	3D Area
`xl3DAreaStacked`	78	3D Stacked Area
`xl3DAreaStacked100`	79	100% Stacked Area
`xl3DBarClustered`	60	3D Clustered Bar
`xl3DBarStacked`	61	3D Stacked Bar
`xl3DBarStacked100`	62	3D 100% Stacked Bar
`xl3DColumn`	-4100	3D Column
`xl3DColumnClustered`	54	3D Clustered Column
`xl3DColumnStacked`	55	3D Stacked Column
`xl3DColumnStacked100`	56	3D 100% Stacked Column
`xl3DLine`	-4101	3D Line
`xl3DPie`	-4102	3D Pie
`xl3DPieExploded`	70	Exploded 3D Pie
`xlArea`	1	Area
`xlAreaStacked`	76	Stacked Area
`xlAreaStacked100`	77	100% Stacked Area
`xlBarClustered`	57	Clustered Bar	216
`xlBarOfPie`	71	Bar of Pie
`xlBarStacked`	58	Stacked Bar
`xlBarStacked100`	59	100% Stacked Bar
`xlBubble`	15	Bubble
`xlBubble3DEffect`	87	Bubble with 3D effects
`xlColumnClustered`	51	Clustered Column	201	This example combines `xlColumnClustered` and `xlLine` in a clustered chart.
`xlColumnStacked`	52	Stacked Column
`xlColumnStacked100`	53	100% Stacked Column
`xlConeBarClustered`	102	Clustered Cone Bar
`xlConeBarStacked`	103	Stacked Cone Bar
`xlConeBarStacked100`	104	100% Stacked Cone Bar
`xlConeCol`	105	3D Cone Column
`xlConeColClustered`	99	Clustered Cone Column
`xlConeColStacked`	100	Stacked Cone Column
`xlConeColStacked100`	101	100% Stacked Cone Column
`xlCylinderBarClustered`	95	Clustered Cylinder Bar
`xlCylinderBarStacked`	96	Stacked Cylinder Bar
`xlCylinderBarStacked100`	97	100% Stacked Cylinder Bar
`xlCylinderCol`	98	3D Cylinder Column
`xlCylinderColClustered`	92	Clustered Cone Column
`xlCylinderColStacked`	93	Stacked Cone Column
`xlCylinderColStacked100`	94	100% Stacked Cylinder Column
`xlDoughnut`	-4120	Doughnut
`xlDoughnutExploded`	80	Exploded Doughnut
`xlFunnel`			419
`xlLine`	4	Line
`xlLineMarkers`	65	Line with Markers
`xlLineMarkersStacked`	66	Stacked Line with Markers
`xlLineMarkersStacked100`	67	100% Stacked Line with Markers
`xlLineStacked`	63	Stacked Line
`xlLineStacked100`	64	100% Stacked Line
`xlPie`	5	Pie	251
`xlPieExploded`	69	Exploded Pie
`xlPieOfPie`	68	Pie of Pie
`xlPyramidBarClustered`	109	Clustered Pyramid Bar
`xlPyramidBarStacked`	110	Stacked Pyramid Bar
`xlPyramidBarStacked100`	111	100% Stacked Pyramid Bar
`xlPyramidCol`	112	3D Pyramid Column
`xlPyramidColClustered`	106	Clustered Pyramid Column
`xlPyramidColStacked`	107	Stacked Pyramid Column
`xlPyramidColStacked100`	108	100% Stacked Pyramid Column
`xlRadar`	-4151	Radar
`xlRadarFilled`	82	Filled Radar
`xlRadarMarkers`	81	Radar with Data Markers
`xlRegionMap`	140	Map chart
`xlStockHLC`	88	High-Low-Close
`xlStockOHLC`	89	Open-High-Low-Close
`xlStockVHLC`	90	Volume-High-Low-Close
`xlStockVOHLC`	91	Volume-Open-High-Low-Close
`xlSurface`	83	3D Surface
`xlSurfaceTopView`	85	Surface (Top View)
`xlSurfaceTopViewWireframe`	86	Surface (Top View wireframe)
`xlSurfaceWireframe`	84	3D Surface (wireframe)
`xlXYScatter`	-4169	Scatter
`xlXYScatterLines`	74	Scatter with Lines
`xlXYScatterLinesNoMarkers`	75	Scatter with Lines and No Data Markers
`xlXYScatterSmooth`	72	Scatter with Smoothed Lines
`xlXYScatterSmoothNoMarkers`	73	Scatter with Smoothed Lines and No Data Markers		This example uses an `xlXYScatterSmoothNoMarkers` chart to draw the graph of a function and then export it as an image.
`xlCombination`	-4111	Seen in ZIC, is this a combo chart?

TODO:

compare the chart type with the shape type.
xlWaterfall

Creating a chart in VBA

In VBA, a chart is created using the addChart2 method of a worksheet's Shapes object:

dim shp as shape
set shp = activeSheet.shapes.addChart2 …
…

Creating images from a chart

The export method of a chart creates an image with the chart's content:

cht.export _
    fileName   := "P:\ath\to\file.png", _
    filterName := "png"

It turns out, that export creates empty (zero byte) files if the exported chart is on the part of the worksheet that is currently visible. Therefore, it might be advisable to use application.goto to move the worksheet such that the chart is visible.

seriesCollection / fullSeriesCollection

The seriesCollection contains the series objects that were added to the chart.

The series objects provide the data that is displayed in a chart.

There is also a .fullSeriesCollection property that contains series objects that are not available in the .seriesCollection property because the corresponding data is filtered (for example because the corresponding rows or columns are hidden).

TODO

activeSheet.chartObjects("fooChart"). …

application.activeChart

Links

How to use a macro to add labels to data points in an xy scatter chart or in a bubble chart in Excel