Charts in Excel VBA – Add a Chart, the Chart object & the ChartObject object
Contents:
Worksheet & Chart Sheet in Excel
Excel is a great tool to create charts quickly & easily, to display worksheet data in a meaningful manner for users. This section illustrates creating & manipulating charts with vba code, in excel. You have a variety of chart types in Excel, such as Column, Bar, Line, Stacked (column, bar, line, …), Pie, XYScatter & Bubble charts which are the most common. Using VBA, you can manipulate embedded charts (which are embedded or placed within a worksheet & and can be displayed alongwith their source data or other information) or a chart sheet (a separate sheet in the workbook which contains only a chart and does not display the source data or any other information). Our emphasis is more on embedded charts in this section.
Column & Bar charts (clustered or otherwise) are useful for comparison between items & to display data changes over a period of time at specified time intervals. Line charts are useful for comparison between items & to display continuous data over time & display data trends at equal time intervals. Stacked charts (column, bar, line, etc) compare an individual item’s contribution to the whole, across categories or time intervals. A Pie chart is particularly useful where relative percentages are required to be displayed. An XYScatter chart is particularly useful for comparing numerical data wherein values are not spaced evenly at regular intervals and you want to compare and show relationships by grouping sets of values by disregarding the time factor or interval. Bubble charts are similar to XY Scatter charts, except that bubble charts compare 3 sets of values instead of two sets of values compared in scatter charts, where the third value determines the size of the marker where the 2 values intersect in scatter charts.
This chapter discusses the overall concept of charts & creating them – the Chart Object that represents a chart in a workbook which can be either an embedded chart or a separate chart sheet, how to add a chart and refer to charts with index number or name. We explain in detail the Chart object & the ChartObject object and using their properties & methods.
Note: In this topic, while specifying Syntax for a property or method of an object, the prefix obj has been used to indicate an object variable viz. for the ChartArea property of the chart object, the Syntax used is: objChart.ChartArea, wherein objChart indicates a Chart object variable. Further, to keep the topic compact, with reference to objects, not all properties or methods may have been mentioned but only some common or often used ones.
Worksheet & Chart Sheet in Excel
The Worksheet Object represents a single worksheet in a workbook. The Worksheets Object refers to a collection of all worksheets in a workbook.
The Sheets Object refers to a collection of all sheets (ie. all worksheets, chart sheets, macro sheets & dialog sheets) in a workbook. The Worksheet Object is a member of both the Worksheets collection (Worksheets object) and the Sheets collection (Sheets object). The Workbook.Worksheets Property returns a Worksheets collection (ie. a Worksheets object) which refers to all worksheets in a workbook. The Workbook.Sheets Property returns a Sheets collection (ie. a Sheets object) which refers to all sheets in a workbook. Using the code MsgBox ActiveWorkbook.Worksheets.Count will return the number of worksheets in the active workbook, and the code MsgBox ActiveWorkbook.Sheets.Count will return the number of sheets in the active workbook.
A workbook can contain 4 types of sheets – worksheet, chart sheet, macro sheet (MS Excel 4.0 Macro sheet) and a dialog sheet (MS Excel 5.0 Dialog sheet). Macro sheets (also called XLM macros) & dialog sheets (used in earlier Excel versions to create customized dialog boxes / making forms, now replaced by UserForms), are still being provided & supported in Excel 2007 only for backward compatibility with earlier versions of Microsoft Excel. A macro sheet (or a dialog sheet) is not included as a part of the Worksheets collection but is a part of the Sheets collection.
Chart objects in VBA
The Chart Object represents a chart (single chart) in a workbook – a chart can be either an embedded chart or a separate chart sheet.
The Charts collection, ie. the Charts Object, refers to a collection of all chart sheets in a workbook, and excludes all embedded charts – each chart sheet is represented by a Chart object. Workbook.Charts Property – the Charts property of the Workbook object returns all chart sheets (ie. the Charts collection) in the specified workbook (Syntax: objWorkbook.Charts). A Chart Sheet contains a single chart and covers the entire worksheet. The Add method of the Charts object is used to add or create a new chart sheet, as explained later.
Charts.Item Property: The Item Property of the Charts object (collection of all chart sheets in a workbook excluding embedded charts) refers to a single chart object in a collection. Syntax: objCharts.Item(Index), where Index is the chart sheet name or index number. You can also omit using the ‘item’ word, using syntax of objCharts(ChartName) or objCharts(IndexNumber) ie. use objCharts(Index) where Index is the chart sheet name or index number viz. Charts(“Chart1”) or Charts(1). The index number starts at 1 for the first or leftmost chart sheet on the workbook tab bar and increments accordingly for each chart sheet moving towards the right (hidden chart sheets are also included), the last (rightmost) chart sheet being returned by Charts(Charts.Count). The Count property of the Charts object – objCharts.Count – returns the number of charts (chart sheets) in the collection (ie. in the workbook).
The chart sheet tab displays the name of the chart sheet. Use the Name property of the Chart object to set or return the name of a chart sheet. Syntax: objChart.Name. This property is read only for a Chart object (embedded chart), read-write for a ChartObject (embedded chart), and read-write for a Chart object (chart sheet).
The ChartObject object represents an embedded chart (single embedded chart) in a sheet. The ChartObjects object refers to a collection of all ChartObject objects (ie. embedded charts) in a single sheet (ie. in a specified chart sheet, dialog sheet or worksheet). Worksheet.ChartObjects Method – the ChartObjects method of the Worksheet object returns either: (i) a single embedded chart (a ChartObject object) – Worksheets(“Sheet1”).ChartObjects(1); or (ii) a collection of all embedded charts (ie. the ChartObjects object) – Worksheets(“Sheet1”).ChartObjects; in the specified workbook. The ChartObject object acts as a container for a Chart object – using the Chart Property of the ChartObject returns a Chart object which refers to a chart contained in the ChartObject object – Worksheets(“Sheet1”).ChartObjects(1).Chart.
Use the ChartObjects.Item Method – Syntax: objChartObjects.Item(Index) – to return a single ChartObject object, where index is the index number or name of the embedded chart. The ChartObjects.Count Property returns the number of embedded charts (ChartObject objects) in the collection (ie. ChartObjects collection).
The Name property of the ChartObject – Syntax: objChartObject.Name – is used to set or return the name of the ChartObject. The Add method of the ChartObjects object is used to create or add a new embedded chart, as explained later.
Example: Using Sheets, Worksheets, Charts & Embedded Charts in vba
Sub Sheets_Charts()
‘Using Sheets, Worksheets, Charts & Embedded Charts in vba
‘Consider a workbook with 4 sheets in the order: “Sheet1”, “Chart1”, “Sheet2”, “Sheet3” & “Chart2” ie. 3 worksheets & 2 Chart Sheets
‘”Sheet1″ has 2 embedded charts
‘—————————–
Dim ws As Worksheet
‘returns 3 (3 worksheets – “Sheet1”, “Sheet2” & “Sheet3”):
MsgBox ThisWorkbook.Worksheets.Count
‘returns the names of each of the 3 worksheets – “Sheet1”, “Sheet2” & “Sheet3”:
For Each ws In ThisWorkbook.Worksheets
MsgBox ws.Name
Next
‘—————————–
Dim obj As Object, i As Integer
‘returns 5 – 3 worksheets & 2 chart sheets:
MsgBox ThisWorkbook.Sheets.Count
‘returns the names of each of the 5 sheets – “Sheet1”, “Chart1”, “Sheet2” & “Sheet3” & “Chart2”:
For i = 1 To ThisWorkbook.Sheets.Count
MsgBox ThisWorkbook.Sheets(i).Name
Next i
‘returns the names of each of the 5 sheets – “Sheet1”, “Chart1”, “Sheet2” & “Sheet3” & “Chart2”:
For Each obj In ThisWorkbook.Sheets
MsgBox obj.Name
Next
‘returns the names of each of the 3 worksheets (“Sheet1”, “Sheet2” & “Sheet3”):
For Each obj In ThisWorkbook.Worksheets
MsgBox obj.Name
Next
‘—————————–
‘returns 2 – there are 2 chart objects (chart sheets “Chart1” & “Chart2”) in this workbook:
MsgBox ThisWorkbook.Charts.Count
‘returns “Chart1”, the first chart sheet in the workbooks tab bar
MsgBox Charts(1).Name
‘returns “Chart2”, the last sheet in the workbooks tab bar, which is a Chart Sheet – Sheets Object refers to a collection of all sheets, including chart sheets
MsgBox Sheets(5).Name
‘Change the name of “Chart2” to “ChartNew”
Charts(2).Name = “ChartNew“
‘returns “ChartNew”, the second chart sheet in the workbooks tab bar
MsgBox Charts(2).Name
‘—————————–
‘returns 0 – there is no embedded chart (ChartObject Object) in the chart sheet:
MsgBox Sheets(“Chart1”).ChartObjects.Count
‘returns 2 – there are 2 embedded charts (ChartObject Objects) in the worksheet named “Sheet1”:
MsgBox Sheets(“Sheet1”).ChartObjects.Count
‘Change the name of the second embedded chart in “Sheet1” to “EmbChart2”
Sheets(“Sheet1”).ChartObjects(2).Name = “EmbChart2“
‘returns “EmbChart2”, the second embedded chart in “Sheet1”
MsgBox Sheets(“Sheet1”).ChartObjects(2).Name
End Sub
Charts.Add Method: Use the Add method of the Charts object to add or create a new chart sheet. Syntax: objCharts.Add(Before, After, Count, Type). All the 4 arguments are optional. Before & After arguments refer to the sheet before or after which the new chart sheet is to be added – if both these are omitted, the new chart is by default added before the active sheet. The Count argument refers to the number of chart sheets to be added, default being one. The Type argument refers to the type of chart to be added, as per the XlChartType constant – note that not all chart types are available for PivotChart reports.
Some examples of XlChartType Enumeration: xlAreaStacked (value 76) – Stacked Area; xlBarClustered (57) – Clustered Bar; xlBarOfPie (71) – Bar of Pie; xlBarStacked (58) – Stacked Bar; xlColumnClustered (51) – Clustered Column; xlColumnStacked (52) – Stacked Column; xlConeBarClustered (102) – Cone Bar Clustered; xlConeBarStacked (103) – Cone Bar Stacked; xlCylinderBarClustered (95) – Clustered Cylinder Bar; xlCylinderBarStacked (96) – Stacked Cylinder Bar; xlLine (4) – Line; xlLineMarkers (65) – Line with Markers; xlLineStacked (63) – Stacked Line; xlPie (5) – Pie; xlPieOfPie (68) – Pie of Pie; xlPieExploded (69) – Exploded Pie; xlXYScatter (-4169) – Scatter; xlXYScatterLines (74) – Scatter with Lines; xl3DArea (-4098) – 3D Area; xl3DColumn (-4100) – 3D Column; xl3DAreaStacked (78) – 3D Stacked Area; xl3DBarClustered (60) – 3D Clustered Bar; xl3DBarStacked (61) – 3D Stacked Bar; xl3DPie (-4102) – 3D Pie; xl3DPieExploded (70) 3D Pie Exploded; … and so on.
ChartObjects.Add Method: Use the Add method of the ChartObjects object, to create or add a new embedded chart. Syntax: objChartObjects.Add(Left, Top, Width, Height). The Left & Top arguments specify the coordinates relative to the top & left (respectively) corner of cell A1 in a worksheet. The Width & Height arguments specify the size in points. All arguments are necessary to specify.
As explained above, the Chart Object represents a chart (single chart) in a workbook – a chart can be either an embedded chart or a separate chart sheet. Code to return a Chart object which is contained in the first embedded chart, named “EmbChart”, in Sheet1: Sheets(“Sheet1”).ChartObjects(1).Chart or Sheets(“Sheet1”).ChartObjects(“”EmbChart”,”).Chart. Code to return a Chart object (vba code) which is the first Chart sheet, named “ChSheet”: Charts(“ChSheet”) or Charts(1).
Example: Return a Chart object; use the Chart Type property with the Chart object
Sub ChartObj_ChartType()
‘Return a Chart object; use the Chart Type property of the Chart object
‘using the Chart Property of the ChartObject returns a Chart object which is contained in the first embedded chart, named “EmbChart”, in Sheet1
‘use the ChartType property of the Chart object: returns -4100, indicating a ‘xl3DColumn’ chart type
MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.ChartType
MsgBox Sheets(“Sheet1”).ChartObjects(“EmbChart”).Chart.ChartType
‘use the Item Property of the Charts object to return a single chart object in a collection – returns a Chart object which is the first Chart sheet, named “ChSheet”
‘use the ChartType property of the Chart object: returns 51, indicating a ‘Clustered Column’ chart type
MsgBox Charts(“ChSheet”).ChartType
MsgBox Charts(1).ChartType
End Sub
Example: Create a new chart sheet
Sub ChartSheet_New()
‘create a new chart sheet
‘Add method of the Charts object adds a new empty chart sheet with a default name and places it immediately before the last sheet in the active workbook
ActiveWorkbook.Charts.Add Before:=Sheets(Sheets.Count)
‘the new chart sheet becomes the active chart
With ActiveChart
‘set type of chart
.ChartType = xlColumnClustered
‘set the range of source data for the chart
.SetSourceData Source:=Sheets(“Sheet1”).Range(“A1:D6”)
‘rename the new chart sheet
.Name = “NewChartSheet“
‘moves the chart sheet to a new location and places after the last sheet in the workbook
.Move After:=Sheets(Sheets.Count)
End With
End Sub
Example: Create a new embedded chart & move to a new location – refer Images 1a (Source Data) & 1b (Embedded Chart)
Sub EmbeddedChart_New()
‘create a new embedded chart & move to a new location – refer Images 1a (Source Data) & 1b (Embedded Chart)
Dim rngSourceData As Range
Set rngSourceData = Sheets(“Sheet2”).Range(“A1:D6”)
‘declare a ChartObject
Dim oChObj As ChartObject
‘the Add method of the ChartObjects object is used to create a new empty embedded chart and add it to the collection in the active sheet
Set oChObj = ActiveSheet.ChartObjects.Add(Left:=25, Width:=300, Top:=10, Height:=225)
With oChObj.Chart
‘set type of chart
.ChartType = xlColumnClustered
‘set the range of source data for the chart – refer Image 1a
.SetSourceData Source:=rngSourceData, PlotBy:=xlColumns
‘move the embedded chart to a new location – to sheet named “Sheet4”
‘the Name argument is required to specify the sheet where to embed a new chart, because the Where argument mentions xlLocationAsObject
.Location Where:=xlLocationAsObject, Name:=”Sheet4“
End With
‘moves the embedded chart (presuming it to be the first ChartObject in “Sheet4”) to a new chart sheet created with the name “StudentMarks”
‘the Name argument is not necessary because the Where argument mentions xlLocationAsNewSheet – if the Name argument is omitted, a new chart sheet is created with the default name
Sheets(“Sheet4″).ChartObjects(1).Chart.Location Where:=xlLocationAsNewSheet, Name:=”StudentMarks“
End Sub
Example: Looping through all embedded charts within a worksheet
‘looping through all embedded charts within a worksheet
Dim oChObj As ChartObject
With ActiveSheet
If .ChartObjects.count > 0 Then
MsgBox “There are ” & .ChartObjects.count & ” Embedded Charts in Active Sheet, with the names:”
For Each oChObj In .ChartObjects
With oChObj
MsgBox .Name
End With
Next
Else
MsgBox “There is no Embedded Chart in Active Sheet”
End If
End With
| Commonly used Properties of the ChartObject object:
|
||
| Property | Syntax | Description |
| Chart Property | objChartObject.Chart | The ChartObject object acts as a container for a Chart object – using the Chart Property of the ChartObject returns a Chart object which refers to a chart contained in the ChartObject object viz. return a Chart object:
Sheets(“Sheet1”).ChartObjects(1).Chart Then use the ChartType Property of the Chart object to set chart type of “Line with Markers”: Sheets(“Sheet1”).ChartObjects(1).Chart.ChartType = xlLineMarkers |
| Height Property | objChartObject.Height | Sets or returns the height, in points, of the embedded chart. |
| Width Property | objChartObject.Width | Sets or returns the width, in points, of the embedded chart. |
| Left Property | objChartObject.Left | Sets or returns the distance, in points, from the left edge of the embedded chart to the left edge of column A on a worksheet. Ex. Align the left edge of the embedded chart with the left edge of the worksheet’s column C:
Sheets(1).ChartObjects(1).Left = Sheets(1).Columns(“C”).Left |
| Top Property | objChartObject.Top | Sets or returns the distance, in points, from the top edge of the embedded chart to the top of row 1 on a worksheet |
| Name Property | objChartObject.Name | Sets or returns the name of the embedded chart, as a string value. |
| RoundedCorners Property | objChartObject.RoundedCorners | sets or returns a Boolean value determining if the embedded chart has rounded corners or not – True indicates rounded corners. |
| Shadow Property | objChartObject.Shadow | sets or returns a Boolean value determining if the embedded chart has a shadow or not |
| TopLeftCell Property | objChartObject.TopLeftCell | Returns the address of the cell which is under the upper-left corner of the embedded chart. This property returns a Range object, & is read-only. Ex:
MsgBox Sheets(“Sheet1”).ChartObjects(1).TopLeftCell.Address |
| BottomRightCell Property | objChartObject.BottomRightCell | Returns the address of the cell which is under the lower-right corner of the embedded chart. This property returns a Range object, & is read-only. Ex:
MsgBox Sheets(“Sheet1”).ChartObjects(1).BottomRightCell.Address |
| Locked Property | objChartObject.Locked | Sets or returns a Boolean value, which is True if the embedded chart is locked and False indicating that the embedded chart can be modified on a protected sheet. |
| Placement Property | objChartObject.Placement | Sets or returns a value (an XlPlacement constant) which refers to how the embedded chart is attached to its underlying cells.
XlPlacement constants: xlMoveAndSize (value 1) indicates that the embedded chart is moved & sized with the cells; xlMove (value 2) indicates that the embedded chart is moved with the cells; xlFreeFloating (value 3) indicates that the embedded chart is free floating and is not moved or sized with its underlying cells. |
| ShapeRange Property | objChartObject.ShapeRange | Returns (read-only) a ShapeRange object representing a shape range. A Shape Range (ie. ShapeRange Object) is a set of shapes, and it can represent either a specified subset of shapes (say shape 1 & shape 3), or can represent all selected shapes on a document. A set of shapes can be returned by specifying an Index Number or Name viz. Shapes.Range(index), where index is the index number or name, and you can also use the Range property & the Array function to build an array of index numbers (or names) to return specific number of shapes – refer below example. |
|
Sub Chart_ShapeRange() ‘refer sheet “Sheet1” which contains 5 shapes: 4 are embedded charts, out of which 3 charts are of type xl3DColumn (value -4100), and 1 is an oval shape
Dim shp As Shape ‘————————— Dim objChtObjShpRng As ShapeRange ‘create a shape range representing all embedded charts in “Sheet1” Set objChtObjShpRng = Sheets(“Sheet1”).ChartObjects.ShapeRange ‘returns names of all 4 charts For Each shp In objChtObjShpRng MsgBox shp.Name Next shp ‘returns names of 3 charts which are of type xl3DColumn (value -4100) For Each shp In objChtObjShpRng If shp.Chart.ChartType = -4100 Then MsgBox shp.Name End If Next shp ‘————————— Dim objShpRng As ShapeRange ‘use the Range property, & the Array function, to build an array of index numbers (or names), to return specific number of shapes Set objShpRng = Sheets(“Sheet1”).Shapes.Range(Array(1, 3, 5)) ‘returns names of 2 charts (chart 1 & 3), and the oval shape For Each shp In objShpRng MsgBox shp.Name Next shp ‘————————— Dim obj As Object ‘create a ShapeRange object representing all shapes in “Sheet1” ‘select all shapes in “Sheet1” & use the ShapeRange property of the Selection object to create a ShapeRange object containing all shapes Sheets(“Sheet1”).Activate Sheets(“Sheet1”).Shapes.SelectAll ‘using the ShapeRange property of the Selection object to return all shapes in the selection Set objShpRng = Selection.ShapeRange ‘returns names of all 5 shapes, including the 4 charts For Each obj In objShpRng MsgBox obj.Name Next obj End Sub
|
||
| Visible Property | objChartObject.Visible | Set or return a Boolean value which determines whether the embedded chart is visible or not Ex. For the embedded chart to be visible:
Sheets(“Sheet1”).ChartObjects(1).Visible = True |
| Commonly used Methods of the ChartObject Object:
|
||
| Method | Syntax | Description |
| Activate Method | objChartObject.Activate | Activates an embedded chart and makes it the active chart – refer the Activate Method of the Chart object where this has been explained in detail. |
| Copy Method | objChartObject.Copy | Copies the embedded chart to the Clipboard. |
| Cut Method | objChartObject.Cut | Cuts the embedded chart to the Clipboard. Refer below code to copy or cut an embedded chart & paste to another sheet. |
| Sheets(“Sheet1”).ChartObjects(1).Copy Sheets(“Sheet2”).Activate Range(“A1”).Select ActiveSheet.Paste |
||
| Delete Method | objChartObject.Delete | Deletes an embedded chart (ChartObject object) Ex.
Sheets(“Sheet1”).ChartObjects(2).Delete |
| Select Method | objChartObject.Select(Replace) | Selects the specified ChartObject object (embedded chart) – refer the Select Method of the Chart object where this has been explained in detail. |
| Duplicate Method | objChartObject.Duplicate | Duplicates an embedded chart and references the duplicate copy. |
|
Sub Chart_DuplicateMethod() Dim objChtDupl As Object ‘returns object type: ChartObject ‘using the Duplicate Method to return reference to the duplicate copy of a ChartObject (embedded chart) ‘Duplicate Method is used to obtain a reference to a Shape in a worksheet, corresponding to a ChartObject, whereas using the Copy Method for a ChartObject object creates a copy which is also a ChartObject object ‘returns the same name for the duplicate copy – “Chart 5” ‘change name of the duplicate copy End Sub
|
||
Child Objects for the ChartObject Object: Above we have discussed: the ShapeRange object – use the ShapeRange Property – objChartObject.ShapeRange – to return the ShapeRange object representing a shape range which is a set of shapes on a document; the Chart object – use the Chart Property – objChartObject.Chart – to return a Chart object which refers to a chart contained in the ChartObject object. Some other child objects which are often used with the ChartObject Object include: Interior Object – use the Interior property – objChartObject.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border Property – objChartObject.Border – to return a Border object, to manipulate a chart element’s border; Range Object – representing a single cell or a range of cells.
| Commonly used Properties of the Chart Object:
|
||
| Property | Syntax | Description |
| AutoScaling Property | objChart.AutoScaling | To automatically scale a 3-D chart for the size to be closer to its 2-D equivalent, set this property to True. For using this property, it is required to set the the RightAngleAxes property to True. Set or return a Boolean value, using the AutoScaling Property. |
| ChartArea Property | objChart.ChartArea | Returns a ChartArea object which refers to the chart area in a chart. The area where chart data is plotted is the Plot Area – the Plot Area is surrounded by the Chart Area. A 2-D chart’s chart area contains: the axes, the chart title, the axis titles & the legend. A 3-D chart’s chart area contains the chart title & the legend, without including the area where the data is plotted within the chart area (ie. the plot area). The ChartArea object is used to format the chart area. |
| ChartStyle Property | objChart.ChartStyle | Sets or returns the chart style for the chart, using an integer value from 1 to 48 corresponding to the options available on the Chart Styles group on the Design tab on the Ribbon. |
| ChartTitle Property | objChart.ChartTitle | Returns a ChartTitle object which represents the chart title. The chart title is accessed & manipulated through the properties & methods of the ChartTitle object. |
| ChartType Property | objChart.ChartType | Return or set the chart type, as per constants / values defined per the XlChartType Enumeration. Note that not all chart types are available for PivotChart reports. |
| DataTable Property | objChart.DataTable | Returns a DataTable object representing the chart data table which displays the chart values. The data table is accessed & manipulated through the properties & methods of the DataTable object. Using the Format property of the DataTable object returns a ChartFormat object which contains the line, fill & effect formatting for the data table – some other child objects which are often used include the Border Object & Font Object. The Chart object is the parent of the DataTable object. |
| HasDataTable Property | objChart.HasDataTable | This property uses a Boolean value (Read-write). If you wish to display a data table, set this property to True (False will hide the data table). |
|
Sub Chart_DataTable() ’embedded chart ‘add a data table to an embedded chart ‘use the DataTable property of the Chart object to return a DataTable object ‘include chart legend with the data table – set ShowLegendKey property to True so that the data table contains the legend ‘use the Font property of the DataTable object to return a Font object to manipulate the font ‘data table not to have vertical cell borders ‘use the Border property of the DataTable object to return a Border object to manipulate the border ‘use the Format property of the DataTable object to return a ChartFormat object which contains the line, fill & effect formatting for the data table End With End With End Sub
|
||
| DepthPercent Property | objChart.DepthPercent | Sets or returns a 3-D chart’s depth as a percentage of its width. You can use a Long value within a range of 20 to 2000 percent. |
| DisplayBlanksAs Property | objChart.DisplayBlanksAs | Set or return how you want to plot blank cells on a chart, as per constants defined in the XlDisplayBlanksAs Enumeration: xlNotPlotted (value 1) – Blanks are not plotted; xlZero (value 2) – Blanks are plotted as zero; xlInterpolated (value 3) – values are interpolated into the chart. |
| Elevation Property | objChart.Elevation | Sets or returns the chart elevation (for a 3-D chart), in degrees. The chart elevation means the elevation of the 3-D chart view, which is the height at which the chart is viewed. Default elevation of a new chart is 0 degrees – you can tilt the chart up by changing the chart elevation to say, 25 degrees. You can use a value within a range of -90 to 90 degrees, except for 3-D Bar charts wherein the range should be between 0 to 44. For most charts the default value is 15 degrees. |
| Floor Property | objChart.Floor | Returns the Floor object, which represents the floor of a 3-D chart. You can thus manipulate the Floor object to set the thickness of the floor (Floor.Thickness Property), use formatting (Floor.Format Property), set the floor color – Floor.Interior.Color = RGB(255, 0, 0) -, and so on. |
| HasAxis Property | objChart.HasAxis(Index1, Index2) | Set (or returns) the axes which will be displayed for a chart. Atleast one argument is required to be specified. The Index1 argument specifies the axis type as defined in the XlAxisType Enumeration: xlCategory (value 1) – Axis displays categories, also referred as the x-axis; xlValue (value 2) – Axis displays values, also referred as y-axis; xlSeriesAxis (value 3) – Axis displays data series for 3-D charts, also referred as the z-axis representing depth of the chart. The Index2 argument specifies the Axis Group as defined in the XlAxisGroup Enumeration: xlPrimary (value 1) – primary axis (default axis); xlSecondary (value 2) – secondary axis. The axis group can either be xlPrimary which is the default, or it can be xlSecondary where the chart has multiple series. 3-D charts cannot have secondary axis. Note that changing the chart type or the AxisGroup property might create or delete axes. To turn on the primary x-axis for a chart:
Sheets(“Sheet1”).ChartObjects(1).Chart.HasAxis(xlCategory, xlPrimary) = True |
| HasLegend Property | objChart.HasLegend | This property uses a Boolean value – True displays and turns on a legend for the chart (Read-write). The Legend is visible and the Legend object’s properties & methods can be used only if the HasLegend property is True. There can be only one Legend in a chart, and multiple LegendEntry objects can be contained in the Legend object. |
| HasTitle Property | objChart.HasTitle | This property uses a Boolean value – True displays a chart title (Read-write). The ChartTitle object exists and can be used only if the HasTitle property returns True. |
| HeightPercent Property | objChart.HeightPercent | Sets or returns a 3-D chart’s height as a percentage of its width. You can use a Long value within a range of 5 to 500 percent. |
| Legend Property | objChart.Legend | Returns a Legend object, which refers to the chart legend and can be manipulated through its properties & methods. There can be only one Legend in a chart, and multiple LegendEntry objects can be contained in the Legend object. |
| Name Property | objChart.Name | Sets or returns the name of a ChartObject object or a Chart object, as a string value. This property is read only for a Chart object (embedded chart), read-write for a ChartObject (embedded chart), and read-write for a Chart object (chart sheet). |
|
‘Name property is read only for a Chart object (embedded chart): ‘read-write for a ChartObject (embedded chart): ‘Name property is read-write for a Chart object (chart sheet): |
||
| Parent Property | objChart.Parent | Returns the parent object of the specified chart object. |
| Perspective Property | objChart.Perspective | Sets or returns the perspective for the 3-D chart view. You can use a Long value in the range of 0 to 100 for this property. If the RightAngleAxes property is set to True (ie. if the chart axes are at right angles), the Perspective Property will have no effect. |
| PlotArea Property | objChart.PlotArea | Returns a PlotArea object which refers to the plot area of a chart. The area where chart data is plotted is the Plot Area – the Plot Area is surrounded by the Chart Area. A 2-D chart’s plot area contains: the data markers, gridlines, data labels, trendlines & optional chart items placed in the chart area. A 3-D chart’s plot area additionally contains: the walls, floor, axes, axis titles & tick-mark labels in the chart. The PlotArea Object is used to format the plot area. |
| PlotBy Property | objChart.PlotBy | Set or return how to plot data on the chart – to plot data by columns use the constant xlColumns (value 2), and to plot data by rows use the constant xlRows (value 1). Where the source data range is arranged so that data series is in a column (the series name is in the first row and the values appear in below rows within the column), use xlColumns, and if data series is in a row, use xlRows. This property is read-only for PivotChart reports and returns xlColumns always. Ex. MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.PlotBy – returns 2 for data plotted by columns. |
| RightAngleAxes Property | objChart.RightAngleAxes | Set or return the chart axes to be at right angles, for 3-D Line, Column & Bar charts. Use a Boolean value for this property, so that using True will set the axes to intersect at right angles. This property is not dependent on or affected by chart rotation or elevation. Note that if the RightAngleAxes property is set to True, using the Perspective Property will have no effect. |
| Rotation Property | objChart.Rotation | Sets or returns the rotation of the 3-D chart view, in degrees. The rotation of the 3-D chart view indicates the rotation of the plot area around the z-axis (the depth axis). You can turn a chart around & around by changing the value of the Rotation property. You can use a value within a range of 0 to 360 degrees, except for 3-D Bar charts wherein the range should be between 0 to 44. For most charts the default value is 20 degrees. Note that rotation value is rounded to the nearest integer. |
| Visible Property | objChart.Visible | Sets or return a value (as specified in the XlSheetVisibility Enumeration) which determines whether the chart object (chart sheet) is visible or not viz. xlSheetHidden (value 0) – hides the chart sheet which can be made visible via menu, xlSheetVeryHidden (value 2) – hides the chart sheet which can be made visible only by using this property and not via menu by the user, xlSheetVisible (value -1) – make the chart sheet visible. Ex. Charts(1).Visible = xlSheetVeryHidden – hides thechart sheet, and to make the chart sheet visible use either of the following: Charts(1).Visible = xlSheetVisible or Charts(1).Visible = True. |
| Walls Property | objChart.Walls | Returns a Walls Object, which refers to the walls of a 3-D chart (all walls are returned as a unit because there is no object that refers to a single wall). |
| BackWall Property | objChart.BackWall | Returns a Walls Object to individually format the back wall of a 3-D chart. |
| SideWall Property | objChart.SideWall | Returns a Walls Object to individually format the side wall of a 3-D chart. |
| ‘set border color for border of all walls to red Sheets(“Sheet1”).ChartObjects(1).Chart.Walls.Border.ColorIndex = 3 ‘individually format the side wall – set interior pattern to Checkerboard ‘individually format the back wall – set interior color to blue ‘set floor interior color to red ‘set floor thickness to 12 |
||
| Commonly used Methods of the Chart Object:
|
||
| Method | Syntax | Description |
| Activate Method | objChart.Activate | Use the Activate Method of the Chart object to activate a chart sheet and make it the active chart. Use the Activate Method of the ChartObject object to activate an embedded chart (ChartObject object) and make it the active chart – Syntax: objChartObject.Activate. The ActiveChart property can be used to refer to a chart which is the active object – a chart sheet is active if it has been selected by the user or it has been activated using the Activate method; an embedded chart is active if it has been selected by the user or if the ChartObject object in which it is contained has been activated using the Activate method. Example: activate chart sheet – Charts(1).Activate, activate embedded chart – Sheets(“Sheet1”).ChartObjects(1).Activate, return name of active chart – MsgBox ActiveChart.Name. |
| Axes Method | objChart.Axes(Type, AxisGroup) | Returns a single Axis or Axes collection (collection of all Axis objects) in a chart. Both arguments are optional to specify. Specify the axis to be returned in the Type argument – you can use any of the 3 constants for this argument: xlCategory (value 1) – Axis displays categories, also referred as the x-axis; xlValue (value 2) – Axis displays values, also referred as y-axis; xlSeriesAxis (value 3) – Axis displays data series for 3-D charts, also referred as the z-axis representing depth of the chart. Specify the axis group in the AxisGroup argument – xlPrimary (value 1) – primary axis (default axis); xlSecondary (value 2) – secondary axis. The axis group can either be xlPrimary which is the default, or it can be xlSecondary where the chart has multiple series. 3-D charts cannot have secondary axis. |
| Copy Method | objChart.Copy(Before, After) | Copies a chart object (chart sheet only) to another location in the workbook. Use the arguments to specify the sheet before (or after) which to place the copied sheet, and you can specify only one argument at a time ie. either Before or After, and if no argument is specified a new workbook is created wherein the copied sheet is placed. Ex. copy chart sheet named “ChSheet” and place the copy after Sheet1:
Charts(“ChSheet”).Copy After:=Sheets(“Sheet1”) |
| Delete Method | objChart.Delete | Use the Delete Method of the Chart object to delete a chart sheet. Use the Delete Method of the ChartObject object to delete an embedded chart (ChartObject object) Ex. Charts(“ChSheet”).Delete, or Sheets(“Sheet1”).ChartObjects(2).Delete. |
| Select Method | objChart.Select(Replace) | Use the Select Method of the Chart object to select the specified chart object (chart sheet), and use the Select Method of the ChartObject object to select the specified ChartObject object (embedded chart). The Replace argument is optional, where a False value is used to extend the previous selection with the specified chart and include both in the current selection, whereas a True value is used to only include the specified chart in the current selection excluding or replacing the previous selection. |
|
‘selects the 2 chart objects (chart sheets) ‘selects the 2 chart objects (chart sheets) ‘selects second embedded chart (ChartObject object) only: ‘selects the 2 embedded charts (ChartObject objects): ‘to deselect the active ChartObject:
|
||
| Location Method | objChart.Location(Where, Name) | Moves the chart to a new location as specified. The Where argument is required to be specified, and it specifies the location where the chart is to be located – use xlLocationAsNewSheet (value 1) to move a chart to a new sheet; use xlLocationAsObject (value 2) to embed a chart in an existing sheet; use xlLocationAutomatic (value 3) to let excel control the location. The Name argument specifies the name of the sheet wherein the chart is to be embedded if the Where argument specifies xlLocationAsObject; and if the Where argument specifies xlLocationAsNewSheet the Name argument specifies the name of the new sheet. The Name argument is necessary to specify only if the Where argument specifies xlLocationAsObject, otherwise it is Optional. |
|
‘moves the embedded chart (the first ChartObject in “Sheet1”) to a new chart sheet created with the name “RevenuesChSheet” Sheets(“Sheet1″).ChartObjects(1).Chart.Location Where:=xlLocationAsNewSheet, Name:=”RevenuesChSheet“
|
||
| Move Method | objChart.Move(Before, After) | Moves the chart (chart sheet) to another location in the workbook. Both the arguments are optional, and only one can be used at a time. Before & After arguments refer to the sheet before or after which the chart is to be placed – if both these arguments are omitted, a new workbook is created containing the moved chart. Example: move the chart sheet (named “RevenuesChSheet”) to a new location and places it after the last sheet in the workbook:
Charts(“RevenuesChSheet”).Move After:=Sheets(Sheets.count) |
| PrintPreview Method | objChart.PrintPreview(EnableChanges) | Displays a print preview of the chart object. The EnableChanges argument (uses a Boolean value) is optional, and setting to True value will enable the user to change the margins & page setup options in print preview. Ex:
Sheets(“Sheet1”).ChartObjects(2).Chart.PrintPreview EnableChanges:=True |
| PrintOut Method | objChart.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas) | Prints the chart. All arguments are optional. The From & To arguments specify the starting page no & ending page no respectively, and omitting these will start printing from the first page & end at the last page (Note that page reference is to printed pages & not in re. of pages in the sheet or workbook). Not specifying the Copies argument will print one copy by default. Set Preview argument to True to display a print preview for the chart, where False is the default value which will print the chart immdeiately. Specify the printer name using the ActivePrinter argument. Set the PrintToFile argument to True to print to a file, instead of printing to a printer, and in this case it is required to specify a file name using the PrToFileName argument. To collate multiple copies, set the Collate argument to True – this will print multiple copies for collation instead of printing all copies of one page, then all copies of the next page, and so on. Setting the argument IgnorePrintAreas to True will print the entire chart object not taking into account the print area, whereas if set to False it will print only the specified print area. Ex:
Sheets(“Sheet1″).ChartObjects(2).Chart.PrintOut , , Copies:=2, Preview:=False, ActivePrinter:=”HP LaserJet 1022 on Ne02:” |
|
‘Print Preview all embedded charts in a sheet
|
||
|
‘Print all embedded charts in a sheet
|
||
|
‘Printing a chart sheet – page setup & printout: ‘use the PageSetup object to manipulate page setup attributes viz. margin, paper size, header, footer, etc ‘using ChartSize is only applicable to chart sheets (not valide for embedded charts) – xlScreenSize prints the chart the same size as it appears on the screen End With ‘print out 1 copy of the chart sheet
|
||
| SeriesCollection Method | objChart.SeriesCollection(Index) | Returns either a single Series object or the SeriesCollection object. The Series object refers to a single series in a chart, and is a member of the SeriesCollection object. A SeriesCollection object refers to a collection of all the Series objects in a chart. The Index argument specifies the number or name of the series – index number represents the order in which the series were added, with the first series added to the chart being index number 1 ie. objChart.SeriesCollection(1), and the last added series index number being objChart.SeriesCollection(SeriesCollection.Count). |
| ChartGroups Method | objChart.ChartGroups(Index) | Returns either a ChartGroup object (single chart group) or a ChartGroups object. The ChartGroup object refers to one or more series plotted in a chart with the same format, and is a member of the ChartGroups object. A ChartGroups object refers to a collection of all the chart groups objects in a chart. The Index argument specifies the chart group index number. Heirarchy: Chart > one or more chart groups in a chart (ex. a line chart group and a bar chart group …) > one or more Series objects in a chart group > one or more Points objects in a series. |
| SetSourceData Method | objChart.SetSourceData(Source, PlotBy) | The Source argument is necessary to specify and refers to the range containing the source data for the chart. The PlotBy argument (optional) specifies how to plot data on the chart – to plot data by columns use the constant xlColumns (value 2), and to plot data by rows use the constant xlRows (value 1). |
| SetElement Method | objChart.SetElement(Element) | Specifies chart elements to set on a chart. It is necessary to specify the Element argument which specifies the type of chart element as per the MsoChartElementType enumeration constants. This method covers commands in the Layout tab of Chart Tools: complete Labels group / Axes group / Analysis group and the PlotArea / Chart Wall / Chart Floor buttons in the Background group.
MsoChartElementType Enumeration specifies if the chart elements are to be displayed and how to display them viz. msoElementChartTitleNone (value 0) – Do not display chart title; msoElementChartTitleAboveChart (value 2) – Display title above chart; msoElementDataLabelNone (value 200) – Do not display data label; msoElementDataLabelLeft (value 206) – Display data label to the left; msoElementLegendNone (value 100) – Do not display legend; msoElementLegendRight (value 101) – Display legend at the right; msoElementPlotAreaNone (value 1000) – Do not display plot area; msoElementPlotAreaShow (value 1001) – Display plot area; msoElementPrimaryCategoryAxisNone (value 348) – Do not display primary category axis; msoElementPrimaryCategoryAxisTitleBelowAxis (value 302) – Display primary category axis title below the axis; msoElementPrimaryValueGridLinesNone (value 328) – Do not display grid lines along primary value axis; msoElementPrimaryValueGridLinesMajor (value 330) – Display major gridlines along primary value axis; and so on. |
| ApplyDataLabels Method | objChart.ApplyDataLabels(Type, LegendKey, AutoText, HasLeaderLines, ShowSeriesName, ShowCategoryName, ShowValue, ShowPercentage, ShowBubbleSize, Separator) | Use this method to apply data labels to all the chart series. All arguments are optional to specify. The Type argument specifies the type of data label applied to a chart: xlDataLabelsShowValue (value 2 – Default) – value for the point; xlDataLabelsShowPercent (value 3) – percentage of the total, & is available only for pie charts & doughnut charts; xlDataLabelsShowLabel (value 4) – category for the point; xlDataLabelsShowLabelAndPercent (value 5) – category for the point & percentage of the total, & is available only for pie charts & doughnut charts; xlDataLabelsShowBubbleSizes (value 6) – show bubble size in reference to the absolute value; xlDataLabelsShowNone (value -4142) – no data labels displayed.
Set the LegendKey argument to True to display legend key next to the data points, where False is the default value. Set the AutoText argument to True to automatically generate appropriate text based on content. Set the HasLeaderLines argument to True to display leader lines for the series. For the arguments of ShowSeriesName / ShowCategoryName / ShowValue / ShowPercentage / ShowBubbleSize, pass a boolean value to enable (ie. display) or disable the series name / category names / data values / percentages / bubble size, for the data label. The Separator argument specifies the separator for the data label. |
|
Apply Data Lables, & display Category Names, Values & Percentages separated by “/” (forward slash) & also display Legend Keys: |
||
| ChartWizard Method | objChart.ChartWizard(Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle) | Use this method to quickly format a chart & modify specific properties. This method allows you to make modifications in a chart without setting each property separately and by changing only the specified properties. All arguments are optional – however the Source argument is necessary to specify unless the Selection is an embedded chart on the active worksheet or the active sheet is an existing chart, else the method fails.
Source argument specifies source data range for a new chart, and if omitted, the selected embedded chart on the active worksheet or the active chart sheet is considered. The Gallery argument specifies the chart type as per constants defined in XlChartType Enumeration (viz. xlLineMarkers, xlColumnClustered, xlPie, …) . The Format argument specifies an option number (1 to 10) depending on the chart type, and if omitted, a default value is chosen based on data source & chart type. The PlotBy argument specifies how to plot data for series on the chart – to plot data by columns use the constant xlColumns (value 2), and to plot data by rows use the constant xlRows (value 1). The CategoryLabels argument specifies the number of category labels contained in the source range rows or columns, as an integer value, with a minimum of 0 (zero) where the source range does not contain category labels. The SeriesLabels argument specifies the number of series labels contained in the source range rows or columns, as an integer value, with a minimum of 0 (zero) where the source range does not contain series labels. Specify True for the HasLegend argument for the chart to have a legend. Specify text for the chart title / category axis title / value axis title, using the Title / CategoryTitle / ValueTitle arguments respectively. The ExtraTitle argument specifies the secondary value axis title (2-D chart) or the series axis title (3-D charts). |
|
Example: create a chart & use the ChartWizard Method of the Chart object – refer Image 3  Sub ChartWizard_1() ‘create a chart & use the ChartWizard Method of the Chart object – refer Image 3 Dim rngSourceData As Range, wsData As Worksheet, wsChart As Worksheet ‘declare a ChartObject Dim oChObj As ChartObject Set wsData = Sheets(“Sheet1”) ‘set sheet for embedded chart Set wsChart = Sheets(“Sheet1”) ‘set source data range Set rngSourceData = wsData.Range(“A1:D5”) ‘the Add method (of the ChartObjects object) is used to create a new empty embedded chart and add it to the collection (ChartObjects object) in the specified sheet Set oChObj = wsChart.ChartObjects.Add(Left:=wsChart.Columns(“A”).Left, Width:=320, Top:=wsChart.Rows(6).Top, Height:=200) With oChObj.Chart ‘chart type ‘Line with Markers’; 2 category labels (in 2 columns – A & B); 1 series labels (row 1); .ChartWizard Source:=rngSourceData, Gallery:=xlLineMarkers, Format:=1, PlotBy:=xlColumns, CategoryLabels:=2, SeriesLabels:=1, HasLegend:=True, title:=”Sales-Profit”, CategoryTitle:=”Qtr – Yr”, ValueTitle:=”$ in thousands” ‘set distance between the levels of labels, and the distance between the first level and the axis line .Axes(xlCategory).TickLabels.Offset = 0 End With End Sub
|
||
Child objects (of the Chart object) which are often used with charts: ChartArea Object; ChartGroup Object; ChartTitle Object; Corners Object; DataTable Object; Floor Object; Hyperlinks Object; Legend Object; PageSetup Object; PivotLayout Object; PlotArea Object; Shapes Object; Tab Object; Walls Object. Many of these are discussed in detail below.
Example: Add an embedded chart, use properties & methods of the ChartObject object & Chart object – refer image 4a & 4b
Sub Chart_Properties_Methods()
‘Add an embedded chart, use properties & methods of the ChartObject object & Chart object – refer image 4a & 4b
Dim rngSourceData As Range, wsData As Worksheet, wsChart As Worksheet
Set wsData = Sheets(“Sheet1”)
Set wsChart = Sheets(“Sheet2”)
Set rngSourceData = Union(wsData.Range(“B24:B28”), wsData.Range(“D24:D28”))
‘declare a ChartObject
Dim oChObj As ChartObject
‘the Add method (of the ChartObjects object) is used to create a new empty embedded chart and add it to the collection (ChartObjects object) in the specified sheet
Set oChObj = wsChart.ChartObjects.Add(Left:=2, Width:=350, Top:=5, Height:=200)
‘using the Chart Property of the ChartObject object returns a Chart object which refers to a chart
With oChObj.Chart
‘use ChartType Property of the Chart object to set type of chart – Line with Markers
.ChartType = xlLineMarkers
‘use SetSourceData Method of the Chart object to set the range of source data for the chart
.SetSourceData Source:=rngSourceData, PlotBy:=xlColumns
‘the Parent property of the Chart object returns its Parent object ie. ChartObject object (oChObj)
With .Parent
‘set the embedded chart to be free-floating so that it does not move or size with its underlying cells
.Placement = xlFreeFloating
‘align the left edge of the embedded chart with the left edge of the worksheet’s column B
.Left = wsChart.Columns(“B“).Left
‘set rounded corners for the embedded chart
.RoundedCorners = True
‘using the Name Property of the ChartObject object – set the name of the embedded chart to “AnnualSalesProfit”
.Name = “AnnualSalesProfit“
End With
‘using the Chart.SeriesCollection Method to return a single series (Series object) by its Index number – set the axis group for the specified series using the AxisGroup Property of the Series object
‘use the XlAxisGroup Enumeration constants to specfy the type of axis group as xlPrimary (Primary axis group)
.SeriesCollection(1).AxisGroup = xlPrimary
.SeriesCollection(2).AxisGroup = xlPrimary
‘using the XValues property of the Series object, set an array of X values (ie. Category Labels), for series 1
.SeriesCollection(1).XValues = wsData.Range(“A25:A28”)
‘use the NewSeries Method of the SeriesCollection object to create a new series
With .SeriesCollection.NewSeries
.AxisGroup = xlSecondary
‘name the new series
.Name = wsData.Range(“C24”)
‘using the Values property of the Series object, set Y values for new series
.Values = wsData.Range(“C25:C28”)
End With
‘using constants specified in the MsoChartElementType Enumeration, display the axis title & determine how to display it (setting the HasTitle property to True is not required here)
‘Place primary value axis title below the axis
.SetElement msoElementPrimaryValueAxisTitleBelowAxis
‘use the Caption Property of the ChartTitle object to set the text for the chart title
.Axes(xlValue, xlPrimary).AxisTitle.Caption = “Sales & Costs“
.SetElement msoElementSecondaryValueAxisTitleBelowAxis
.Axes(xlValue, xlSecondary).AxisTitle.Caption = “Profits“
‘refer the Axis object – Value axis in the Secondary axis group – Axis object is a member of the Axes collection
With .Axes(xlValue, xlSecondary)
‘set the minimum and maximum values for the value axis
.MaximumScale = WorksheetFunction.RoundUp(WorksheetFunction.Max(wsData.Range(“C25:C28”)) * 1.55, -4)
.MinimumScale = WorksheetFunction.RoundDown(WorksheetFunction.Min(wsData.Range(“C25:C28”)) * 0.85, -4)
.MajorUnit = 20000
End With
‘using constant values in MsoChartElementType Enumeration to specify if the chart elements are to be displayed and how to display them
‘Display chart title above chart
.SetElement msoElementChartTitleAboveChart
‘Display major gridlines along primary value axis
.SetElement msoElementPrimaryValueGridLinesMajor
‘turn off legend
.SetElement msoElementLegendNone
‘Display data table with legend keys
.SetElement msoElementDataTableWithLegendKeys
End With
End Sub
Below is an example for creating an embedded chart, of type line with markers, and manipulating various chart elements – it is divided into 8 parts / subs, for clear step by step explanations. Sub 1: add an embedded chart; Sub 2: manipulate Chart Title; Sub 3: manipulate Chart Area; Sub 4: manipulate Chart Axis & Axis Title; Sub 5: manipulate Chart Series; Sub 6: manipulate Plot Area; Sub 7: align chart elements; Sub 8: manipulate Chart Legend.
Example: Part 1 of 8 – Add an embedded chart – refer Image 1.1
Sub EmbChart_ChartObject_1()
‘Add an embedded chart – refer Image 1.1
Dim rngSourceData As Range, wsData As Worksheet, wsChart As Worksheet
Set wsData = Sheets(“Sheet18”)
Set wsChart = Sheets(“Sheet19”)
Set rngSourceData = wsData.Range(“C1:D9”)
‘declare a ChartObject
Dim oChObj As ChartObject
‘delete existing embedded charts in the worksheet
For Each oChObj In wsChart.ChartObjects
oChObj.Delete
Next
‘the Add method (of the ChartObjects object) is used to create a new empty embedded chart and add it to the collection (ChartObjects object) in the specified sheet – left edge align to the left edge of column B, top edge align to the top of row 2
Set oChObj = wsChart.ChartObjects.Add(Left:=wsChart.Columns(“B“).Left, Width:=450, Top:=wsChart.Rows(2).Top, Height:=255)
‘using the Chart Property of the ChartObject object returns a Chart object which refers to a chart (contained in the ChartObject object)
With oChObj.Chart
‘use ChartType Property of the Chart object to set type of chart – Line with Markers
.ChartType = xlLineMarkers
‘use SetSourceData Method of the Chart object to set the range of source data for the chart
.SetSourceData Source:=rngSourceData, PlotBy:=xlColumns
‘the ChartTitle object exists and can be used only if the HasTitle property (of the Chart object) is True
.HasTitle = True
‘using the Chart.SeriesCollection Method to return a single series (Series object) by its name or Index number
‘the AxisGroup Property of the Series object sets the axis group for series – using the XlAxisGroup Enumeration to specfy the type of axis group as xlPrimary (Primary axis group)
.SeriesCollection(wsData.Range(“C1”).Value).AxisGroup = xlPrimary
‘specfy the type of axis group as xlSecondary (Secondary axis group) for series
.SeriesCollection(wsData.Range(“D1”).Value).AxisGroup = xlSecondary
‘refer ChartObject object (oChObj)
With .Parent
‘set the embedded chart to be free-floating so that it does not move or size with its underlying cells
.Placement = xlFreeFloating
‘set rounded corners for the embedded chart
.RoundedCorners = True
‘Change the name of the embedded chart to “QtrlySalesProfitChart” using the Name Property
.Name = “QtrlySalesProfitChart“
End With
End With
End Sub