Class Chart (Excel VBA)

The class Chart represents a chart in a workbook.

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

Set

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

Dim chr As Chart
Set chr = Application.Sheets.Add()

The following procedures can be used to set variables of type Chart: Application.ActiveChart, Location, ChartObject.Chart, Charts.Item, Charts.Add2, 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 chrChart As Chart
For Each chrChart In Application.Charts
	
Next chrChart

Themes

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, Charts.Copy and Sheets.Copy
Display/Show with its procedures DisplayBlanksAs, ShowAllFieldButtons, ShowAxisFieldButtons, ShowDataLabelsOverMaximum, ShowExpandCollapseEntireFieldButtons, ShowLegendFieldButtons and ShowValueFieldButtons
Import/Export with its procedures Export and ExportAsFixedFormat
Print with its procedures PageSetup, PrintedCommentPages, PrintOut, PrintPreview, Charts.HPageBreaks, Charts.PrintOut, Charts.PrintPreview, Charts.VPageBreaks, Sheets.HPageBreaks, Sheets.PrintOut, Sheets.PrintPreview and Sheets.VPageBreaks
Protect with its procedures Protect and Unprotect
Add with its procedures Charts.Add2 and Sheets.Add

Methods

These are the main methods of the Chart class

Activate - Makes the current chart the active chart.

Delete - Deletes the object.

Select - Selects the object.

Charts.Delete - Deletes the object.

Charts.Select - Selects the object.

Sheets.Delete - Deletes the object.

Sheets.Select - Selects the object.

Other Methods

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

Charts("Chart1").SeriesCollection(1). _ 
 ApplyDataLabels Type:=xlDataLabelsShowLabel

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

With Charts("Chart1").Axes(xlCategory) 
 .HasTitle = True 
 .AxisTitle.Text = "July Sales" 
End With

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.

Charts("Chart1").ChartWizard _ 
 Gallery:=xlLine, _ 
 HasLegend:=True, CategoryTitle:="Year", ValueTitle:="Sales"

CheckSpelling - Checks the spelling of an object.

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.

Private Sub Chart_MouseMove(ByVal Button As Long, _ 
 ByVal Shift As Long, ByVal X As Long, ByVal Y As Long) 
 Dim IDNum As Long 
 Dim a As Long 
 Dim b As Long 
 
 ActiveChart.GetChartElement X, Y, IDNum, a, b 
 If IDNum = xlLegendEntry Then _ 
 MsgBox "WARNING: Move away from the legend" 
End Sub

Location - Moves the chart to a new location.

Worksheets(1).ChartObjects(1).Chart _ 
 .Location xlLocationAsNewSheet, "Monthly Sales"

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

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

SetSourceData - Sets the source data range for the chart.

Charts(1).SetSourceData Source:=Sheets(1).Range("a1:a10"), _ 
 PlotBy:=xlColumns

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

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

x = Array("Sheet1", "Sheet5", "Sheet7") 
Sheets(x).FillAcrossSheets _ 
 Worksheets("Sheet1").Range("A1:C5")

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

Worksheets("Sheet1").Move _ 
 after:=Worksheets("Sheet3")

Properties

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.

With Charts("Chart1") 
 .RightAngleAxes = True 
 .AutoScaling = True 
End With

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.

    Sheets(1).Range("C1:E1").Value2 = "Sample_Row1"
    Sheets(1).Range("C2:E2").Value2 = "Sample_Row2"
    Sheets(1).Range("A3:A5").Value2 = "Sample_ColA"
    Sheets(1).Range("B3:B5").Value2 = "Sample_ColB"
    Sheets(1).Range("C3:E5").Formula = "=row()"
    Dim crt As Chart
    Set crt = Sheets(1).ChartObjects.Add(0, 0, 500, 200).Chart
    crt.SetSourceData Sheets(1).Range("A1:E5")
    ' Set the series names to only use column B
    crt.SeriesNameLevel = 1
    ' Use columns A and B for the series names
    crt.SeriesNameLevel = xlSeriesNameLevelAll
    ' Use row 1 for the category labels
    crt.CategoryLabelLevel = 0
    ' Use rows 1 and 2 for the category labels
    crt.CategoryLabelLevel = xlCategoryLabelLevelAll

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

With Charts("Chart1").ChartArea 
 .Interior.ColorIndex = 3 
 .Border.ColorIndex = 5 
End With

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.

With Worksheets(1).ChartObjects(1).Chart 
 If .ChartType = xlBubble Then 
 .ChartGroups(1).BubbleScale = 200 
 End If 
End With

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

With Worksheets(1).ChartObjects(1).Chart 
 .HasDataTable = True 
 .DataTable.HasBorderOutline = True 
End With

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

Charts("Chart1").DepthPercent = 50

DisplayValueNotAvailableAsBlank

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

Charts("Chart1").Elevation = 34

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

Charts("Chart1").Floor.Interior.ColorIndex = 5

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.

Charts("Chart1").GapDepth = 200

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

Charts("Chart1").HasAxis(xlValue, xlPrimary) = True

HasDataTable true if the chart has a data table.

With Worksheets(1).ChartObjects(1).Chart 
 .HasDataTable = True 
 With .DataTable 
 .HasBorderHorizontal = False 
 .HasBorderVertical = False 
 .HasBorderOutline = True 
 End With 
End With

HasLegend true if the chart has a legend.

With Charts("Chart1") 
 .HasLegend = True 
 .Legend.Font.ColorIndex = 5 
End With

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

Charts("Chart1").HeightPercent = 80

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.

Charts("Chart1").HasLegend = True 
Charts("Chart1").Legend.Font.ColorIndex = 5

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.

Charts("Chart1").RightAngleAxes = False 
Charts("Chart1").Perspective = 70

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

Set objNewSheet = Worksheets.Add 
objNewSheet.Activate 
intRow = 1 
For Each objPF In _ 
 Charts("Chart1").PivotLayout.PivotFields 
 objNewSheet.Cells(intRow, 1).Value = objPF.Caption 
 intRow = intRow + 1 
Next objPF

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

Charts("Chart1").PlotArea.Interior.ColorIndex = 8

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.

Worksheets(1).ChartObjects(1).Chart.PlotBy = xlColumns

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

Charts("Chart1").PlotVisibleOnly = True

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.

Worksheets(1).ChartObjects(1).Chart.ProtectData = True

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.

Worksheets(1).ChartObjects(1).Chart.ProtectFormatting = True

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.

MsgBox ActiveSheet.ProtectionMode

ProtectSelection true if chart elements cannot be selected.

Worksheets(1).ChartObjects(1).Chart.ProtectSelection = True

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.

Charts("Chart1").RightAngleAxes = True

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.

Charts("Chart1").Rotation = 30

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.

ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveChart.ShowReportFilterFieldButtons = True

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.

Charts("Chart1").Walls.Border.ColorIndex = 3

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

Charts.Item returns a single object from a collection.

With Charts.Item("Chart1").SeriesCollection(1).Trendlines(1) 
 .Forward = 5 
 .Backward = .5 
End With

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

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

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.Item("sheet1").Activate

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.