Class Chart (Excel VBA)

The class Chart represents a chart in a workbook.

The main procedures of class Chart are Activate, Copy, Delete, Select, Sheets.Add, Sheets.Copy, Sheets.Delete and Sheets.Select


To use a Chart class variable it first needs to be instantiated, for example

Dim chr as Chart
Set chr = ActiveChart

The following procedures can be used to set variables of type Chart: Application.ActiveChart, Location, ChartObject.Chart, Shape.Chart, ShapeRange.Chart, Sheets.Item, Sheets.Item, Sheets.Add, Sheets.Add, Sheets.Visible, Sheets.Visible, Window.ActiveChart, Workbook.ActiveChart, Worksheets.Add, Application.Charts, Application.Sheets, Application.Worksheets, Window.SelectedSheets, Workbook.Charts, Workbook.Sheets and Workbook.Worksheets

For Each

Here is an example of processing the Chart items in a collection.

Dim chr As Chart
For Each chr In Charts
Next chr


Some procedures in this class have been grouped together in themes and are described on separate theme pages

Name with its procedures ChartTitle, CodeName, HasTitle and SeriesNameLevel
Copy with its procedures Copy, CopyPicture and Sheets.Copy
Display/Show with its procedures DisplayBlanksAs, ShowAllFieldButtons, ShowAxisFieldButtons, ShowDataLabelsOverMaximum, ShowExpandCollapseEntireFieldButtons, ShowLegendFieldButtons and ShowValueFieldButtons
Export with its procedures Export and ExportAsFixedFormat
Print with its procedures PageSetup, PrintedCommentPages, PrintOut, PrintPreview, Sheets.HPageBreaks, Sheets.PrintOut, Sheets.PrintPreview and Sheets.VPageBreaks
Protect with its procedures Protect and Unprotect
Select with its procedures Select and Sheets.Select


These are the main methods of the Chart class

Activate - Makes the current chart the active chart.


Delete - Deletes the object.


Sheets.Add - Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.

ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)

Sheets.Delete - Deletes the object.


Other Methods

ApplyChartTemplate - Applies a standard or custom chart type to a chart.

ApplyDataLabels - Applies data labels to all the series in a chart.

ApplyLayout - Applies the layouts shown in the ribbon.

Axes - Returns an axes that represents either a single axis or a collection of the axes on the chart.

ChartGroups - Returns an chartgroups that represents either a single chart group (a ChartGroup chartgroups) or a collection of all the chart groups in the chart (a ChartGroups chartgroups). The returned collection includes every type of group.

ChartObjects - Returns an chartobjects that represents either a single embedded chart (a Chartchartobjects chartobjects) or a collection of all the embedded charts (a Chartchartobjectss chartobjects) on the sheet.

ChartWizard - Modifies the properties of the given chart. You can use this method to quickly format a chart without setting all the individual properties. This method is noninteractive, and it changes only the specified properties.

CheckSpelling - Checks the spelling of an object.

ClearToMatchColorStyle - Clears all colors on the specified chart that do not follow the color style applied to the chart.

ClearToMatchStyle - Clears the chart elements formatting to automatic.

Evaluate - Converts a Microsoft Excel name to an object or a value.

FullSeriesCollection - Enables retrieving the filtered out series specified by the Index argument.

GetChartElement - Returns information about the chart element at specified x and y coordinates. This method is unusual in that you specify values for only the first two arguments. Microsoft Excel fills in the other arguments, and your code should examine those values when the method returns.

Location - Moves the chart to a new location.

Move - Moves the chart to another location in the workbook.

OLEObjects - Returns an oleobjects that represents either a single OLE oleobjects (an OLEoleobjects) or a collection of all OLE oleobjectss (an OLEoleobjectss collection) on the chart or sheet. Read-only.

Paste - Pastes chart data from the Clipboard into the specified chart.

Refresh - Causes the specified chart to be redrawn immediately.

SaveAs - Saves changes to the chart or worksheet in a different file.

SaveChartTemplate - Saves a custom chart template to the list of available chart templates.

SeriesCollection - Returns an seriescollection that represents either a single series (a Series seriescollection) or a collection of all the series (a SeriesCollection collection) in the chart or chart group.

SetBackgroundPicture - Sets the background graphic for a chart.

SetDefaultChart - Specifies the name of the chart template that Microsoft Excel uses when creating new charts.

SetElement - Sets chart elements on a chart.

SetSourceData - Sets the source data range for the chart.

Sheets.FillAcrossSheets - Copies a range to the same area on all other worksheets in a collection.

Sheets.Move - Moves the sheet to another location in the workbook.


AutoScaling true if Microsoft Excel scales a 3D chart so that it's closer in size to the equivalent 2D chart. The RightAngleAxes property must be True.

BackWall returns a Walls object that allows the user to individually format the back wall of a 3D chart.

BarShape returns or sets the shape used with the 3D bar or column chart.

CategoryLabelLevel returns an XlCategoryLabelLevel constant referring to the level of where the category labels are being sourced from.

ChartArea returns a ChartArea object that represents the complete chart area for the chart.

ChartColor returns or sets an Integer that represents the color scheme for the chart. Read/write.

ChartStyle returns or sets the chart style for the chart.

ChartType returns or sets the chart type.

DataTable returns a DataTable object that represents the chart data table.

DepthPercent returns or sets the depth of a 3D chart as a percentage of the chart width (between 20 and 2000 percent).


Elevation returns or sets the elevation of the 3D chart view, in degrees.

Floor returns a Floor object that represents the floor of the 3D chart.

GapDepth returns or sets the distance between the data series in a 3D chart as a percentage of the marker width. The value of this property must be between 0 and 500.

HasAxis returns or sets which axes exist on the chart. Read/write Variant.

HasDataTable true if the chart has a data table.

HasLegend true if the chart has a legend.

HeightPercent returns or sets the height of a 3D chart as a percentage of the chart width (between 5 and 500 percent).

Hyperlinks returns a Hyperlinks collection that represents the hyperlinks for the chart.

Index returns a Long value that represents the index number of the object within the collection of similar objects.

Legend returns a Legend object that represents the legend for the chart.

MailEnvelope represents an email header for a document.

Name returns or sets a String value representing the name of the object.

Next returns a Worksheet worksheet that represents the next sheet.

Parent returns the parent object for the specified object. Read-only.

Perspective returns or sets a Long value that represents the perspective for the 3D chart view.

PivotLayout returns a PivotLayout object that represents the placement of fields in a PivotTable report and the placement of axes in a PivotChart report.

PlotArea returns a PlotArea object that represents the plot area of a chart.

PlotBy returns or sets the way columns or rows are used as data series on the chart. Can be one of the following XlRowCol constants: xlColumns or xlRows.

PlotVisibleOnly true if only visible cells are plotted. False if both visible and hidden cells are plotted.

Previous returns a Worksheet worksheet that represents the previous sheet.

ProtectContents true if the contents of the sheet are protected. For a chart, this protects the entire chart. To turn on content protection, use the Protect method with the Contents argument set to True.

ProtectData true if series formulas cannot be modified by the user.

ProtectDrawingObjects true if shapes are protected. To turn on shape protection, use the Protect method with the DrawingObjects argument set to True.

ProtectFormatting true if chart formatting cannot be modified by the user.

ProtectionMode true if user-interface-only protection is turned on. To turn on user interface protection, use the Protect method with the UserInterfaceOnly argument set to True.

ProtectSelection true if chart elements cannot be selected.

RightAngleAxes true if the chart axes are at right angles, independent of chart rotation or elevation. Applies only to 3D line, column, and bar charts. Read/write Boolean.

Rotation returns or sets the rotation of the 3D chart view (the rotation of the plot area around the z-axis, in degrees). The value of this property must be from 0 to 360, except for 3D bar charts, where the value must be from 0 to 44. The default value is 20. Applies only to 3D charts. Read/write Variant.

Shapes returns a Shapes collection that represents all the shapes on the chart sheet.

ShowReportFilterFieldButtons returns or sets whether to display the report filter field buttons on a PivotChart.

SideWall returns a Walls object that allows the user to individually format the side wall of a 3D chart.

Tab returns a Tab object for a chart.

Visible returns or sets an XlSheetVisibility value that determines whether the object is visible.

Walls returns a Walls object that represents the walls of the 3D chart.

Sheets.Count returns a Long value that represents the number of objects in the collection.

Sheets.Item returns a single Object object from the collection.

Sheets.Parent returns the parent object for the specified object. Read-only.

Sheets.Visible returns or sets a Variant value that determines whether the boolean is visible.