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.