In MS Excel, you can create different chart types and format them appropriately. From the VBA point of view, the workbook’s Sheets collection includes two families of sheets: Worksheets (worksheets) and Charts (chart sheets). The Charts family contains charts created on chart sheets. This family does not include charts embedded directly on worksheets. Such charts belong to the ChartObjects family. Thus, a ChartObject is embedded in a Worksheet, whereas a Chart is embedded in a Workbook.
The Workbook and Application objects share the ActiveChart property, which returns the active chart in the workbook, regardless of which family it belongs to. The Chart object has a number of child objects listed in Table.
Table. Objects subordinate to the Chart object
| Object | Description |
| ChartArea | The area in which the chart is drawn |
| PlotArea | The chart plotting area |
| Floor | The horizontal plane (floor) of a 3-D chart |
| Walls (BackWall, Walls) | The vertical planes (walls) of a 3-D chart |
| Corners | The corners of a 3-D chart |
| PageSetup | Page setup parameters |
| ChartTitle | The chart title |
| SeriesCollection | The range of data plotted on the value (y) axis |
| Trendlines | Trendline(s) |
| Axis | Chart axes |
| AxisTitle | Axis titles |
| DisplayUnitLabel | Axis display unit label |
| Gridlines | Gridlines |
| TickLabels | Tick labels on the axes |
| DataTable | The chart’s data table |
| Legend | The legend |
| Shapes | The drawing shapes within the chart |
| SeriesCollection | Data series |
| DataLabels | Data labels |
| Points | Data points |
If a chart is located on a worksheet, then the object hierarchy—for example, to address the chart title—can be represented as:
Application
Workbook
Worksheet
ChartObject
Chart
ChartTitle
For charts that are on chart sheets, the object hierarchy is slightly different:
Application
Workbook
Chart
ChartTitle
Adding a new element to the ChartObjects and Charts families
The ChartObjects and Charts families have the methods Add (create a new family element) and Delete (remove a family element), and the Count property (return the number of elements in the family).
Add method of the ChartObjects family:
Add(Left, Top, Width, Height)
- Left, Top — set the coordinates on the worksheet of the chart’s upper-left corner.
- Width, Height — set the chart’s width and height.
All parameters are optional.
Add method of the Charts family:
Add(Before, After, Count)
- Before — specifies before which sheet the chart is added.
- After — specifies after which sheet the chart is added.
- Count — specifies how many charts to add.
All parameters are also optional.
Properties of the Chart object
The Chart object has more than 50 properties that determine the chart’s appearance (see Excel Help for detailed descriptions). The main properties are shown in Table, and the main chart types (values of the ChartType property) are shown in Table.
Table. Key properties of the Chart object
| Property | Description |
| Area3DGroup | Returns a ChartGroup object encapsulating information about a 3-D area |
| AutoScaling | Enables automatic scaling for 3-D charts |
| Bar3DGroup | Returns a ChartGroup object for a 3-D bar chart |
| ChartArea | Returns a ChartArea object |
| ChartTitle | Returns a ChartTitle object |
| ChartType | Sets the chart type (valid values in Table 6.4) |
| Column3DGroup | Returns a ChartGroup object for 3-D columns |
| Corners | Returns a Corners object |
| DataTable | Returns a DataTable object |
| DepthPercent | Sets the depth percentage for a 3-D chart |
| DisplayBlanksAs | Specifies how empty cells are interpreted: xlNotPlotted, xlInterpolated, xlZero |
| Elevation | Sets the viewing angle for a 3-D chart |
| Floor | Returns a Floor object |
| GapDepth | Sets the gap between series in a 3-D chart |
| HasAxis | Specifies whether the chart has axes |
| HasDataTable | Specifies whether the chart has a data table |
| HasLegend | Checks whether the chart has a legend |
| HasTitle | Checks whether the chart has a title |
| HeightPercent | Sets chart height as a percentage of its width |
| Hyperlinks | Returns the Hyperlinks collection |
| Index | Returns the index value within Charts |
| Legend | Returns a Legend object |
| PageSetup | Returns a PageSetup object |
| Perspective | Sets perspective for a 3-D chart |
| PlotArea | Returns a PlotArea object |
| PlotBy | Defines how data is laid out: xlColumns or xlRows |
| PlotVisibleOnly | Specifies whether to include hidden cells |
| ProtectContents, ProtectData, ProtectDrawingObjects, ProtectFormatting, ProtectionSelection, ProtectGoalSeek | Boolean properties indicating whether protection is enabled for the corresponding chart element |
| Rotation | Returns the rotation angle of a 3-D chart around the z-axis |
| Visible | Controls chart visibility |
| Walls | Returns a Walls object |
Table. Valid values of the ChartType property
| Type of Chart | Constants (in English) |
|---|---|
| Column Chart | xlColumnClustered, xl3DColumnClustered, xlColumnStacked, xl3DColumnStacked, xlColumnStacked100, xl3DColumnStacked100, xl3DColumn |
| Bar Chart | xlBarClustered, xl3DBarClustered, xlBarStacked, xl3DBarStacked, xlBarStacked100, xl3DBarStacked100 |
| Line Chart | xlLine, xlLineMarkers, xlLineStacked, xlLineMarkersStacked, xlLineStacked100, xlLineMarkersStacked100, xl3DLine |
| Pie Chart | xlPie, xlPieExploded, xl3DPie, xl3DPieExploded, xlPieOfPie, xlBarOfPie |
| XY Scatter Chart | xlXYScatter, xlXYScatterSmooth, xlXYScatterSmoothNoMarkers, xlXYScatterLines, xlXYScatterLinesNoMarkers |
| Area Chart | xlArea, xl3DArea, xlAreaStacked, xl3DAreaStacked, xlAreaStacked100, xl3DAreaStacked100 |
| Doughnut Chart | xlDoughnut, xlDoughnutExploded |
| Radar Chart | xlRadar, xlRadarMarkers, xlRadarFilled |
| Surface Chart | xlSurface, xlSurfaceTopView, xlSurfaceWireframe, xlSurfaceTopViewWireframe |
| Bubble Chart | xlBubble, xlBubble3DEffect |
| Stock Chart | xlStockHLC, xlStockVHLC, xlStockOHLC, xlStockVOHLC |
| Cylinder Chart | xlCylinderColClustered, xlCylinderBarClustered, xlCylinderColStacked, xlCylinderBarStacked, xlCylinderColStacked100, xlCylinderBarStacked100, xlCylinderCol |
| Cone Chart | xlConeColClustered, xlConeBarClustered, xlConeColStacked, xlConeBarStacked, xlConeColStacked100, xlConeBarStacked100, xlConeCol |
| Pyramid Chart | xlPyramidColClustered, xlPyramidBarClustered, xlPyramidColStacked, xlPyramidBarStacked, xlPyramidColStacked100, xlPyramidBarStacked100, xlPyramidCol |
Methods of the Chart object
Like any object, Chart has methods to control its appearance and behavior. The methods most useful in practice are shown in Table.
Table. Methods of the Chart object
| Method | Description |
| Activate | Activates the chart |
| ApplyDataLabels | Applies specified data labels |
| AutoFormat | Applies autoformat |
| Axes | Returns the Axes collection for setting axis properties |
| ChartObjects | Returns the ChartObjects collection |
| ChartWizard | Creates/sets up a chart using the wizard |
| CheckSpelling | Checks spelling |
| Copy | Copies the chart to the specified location |
| CopyPicture | Copies the chart to the clipboard as a picture |
| Delete | Deletes the chart |
| Deselect | Clears selection from the chart |
| Export | Exports the chart to a graphics file |
| GetChartElement | Returns information about the chart element at a specified point |
| Location | Sets the chart location |
| Move | Moves the chart |
| Paste | Pastes chart data from the clipboard |
| PrintOut | Prints the chart |
| SendToBack | Sends the chart behind other objects |
| Protect | Sets protection |
| Refresh | Refreshes the chart |
| SaveAs | Saves the modified chart to a new file |
| Select | Selects the chart |
| SeriesCollection | Returns the series collection |
| SetBackgroundPicture | Sets a background picture |
| SetSourceData | Specifies the source range for the chart |
| Unprotect | Removes protection from the chart |
Events of the Chart object
The Chart object also exposes a number of events (Table 6.6) that allow you to track various user actions.
Table. Events of the Chart object
| Event | Description |
| Activate | Occurs when the chart is activated |
| BeforeDoubleClick | Occurs before a double-click |
| BeforeRightClick | Occurs before a right-click |
| Calculate | Occurs when data change |
| Deactivate | Occurs when the chart is deactivated |
| DragOver | Occurs when a range is dragged over the chart |
| DragPlot | Occurs when a range is dragged and dropped into the chart |
| MouseDown, MouseUp | Occur when the user presses/releases any mouse button |
| MouseMove | Occurs when the user moves the mouse pointer over the chart |
| Resize | Occurs when the chart is resized |
| Select | Occurs when a chart element is selected |
| SeriesChange | Occurs when the reference to a data series changes |