Chart Elements in Excel VBA (Part 2) – Chart Series, Data Labels, Chart Legend

Chart Elements in Excel VBA (Part 2) – Chart Title, Chart Area, Plot Area, Chart Axes, Chart Series, Data Labels, Chart Legend

Contents:

Chart Series

DataLabels Object / DataLabel Object

Chart Legend

This chapter discusses some important chart elements contained on a chart, which include: chart area (ChartArea object); chart title (ChartTitle object); plot area (PlotArea object); chart series (Series object – single series in a chart, SeriesCollection object – a collection of all the Series objects in a chart, Error Bars, Leader Lines, Trendlines, Point object – single points, Points object – collection of all Point objects in a series); chart axis (Axis Object – a single axis, Axes Object  – collection of all the Axis objects in a chart, Gridlines object – major & minor gridlines, TickLabels Object – tick marks on an axis); chart Legend (Legend object, LegendEntry object – a single legend entry, LegendEntries object – a collection of legend entries, LegendKey object); data labels (DataLabel object – a single data label, DataLabels object – all data labels in a chart or series).

Individual chart elements can be formatted & manipulated in vba code, such as: 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 plot area); Plot Area (area where chart data is plotted – 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.); Data Series (chart series are related data points plotted in a chart, each series having a distinct color or pattern and is represented in the chart legend); Chart Axis (the plot area is bordered by a line that is used as a frame of reference for measurement – axis which displays categories is referred as the x-axis, axis which displays values is referred as y-axis, for 3-D charts the z-axis represents depth of the chart); Chart or Axis Title (descriptive text displaying title for chart or axis, by default centered at the chart top or aligned to an axis); Tick Marks (small lines of measurement which intersect an axis, equated to divisions on a ruler); Gridlines (tick marks are extended by Gridlines from horizontal or vertical axis across the chart’s plot area which enables easier display & readability of chart data); Data Labels (applied to a chart series, a label provides additional information about a data marker or a point in a series – you can apply data labels to each or all points of a data series to display the series name / category names / data values / percentages / bubble size), Chart Legend (a graphic visually linking a legend entry with its associated series or trendline so that any formatting change in one automatically changes the other’s formatting).

This Chapter illustrates Chart Series, Data Labels & Chart Legend. Chart Elements – Chart Title, Chart Area, Plot Area & Chart Axes – are illustrated in Part 1.

Chart Series

A SeriesCollection object refers to a collection of all the Series objects in a chart. The Series object refers to a single series in a chart, and is a member of the SeriesCollection object.

The SeriesCollection method of the Chart object (SeriesCollection Method) returns either a single Series object or the SeriesCollection object. Syntax: objChart.SeriesCollection(Index). The Index argument (optional) 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(objSeriesCollection.Count).

SeriesCollection.Item Method. To refer to a single Series (ie. Series object) from Series collection, use the the Item Method of the SeriesCollection object. Syntax: objSeriesCollection.Item(Index), where the Index argument is necessary, and it 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, as explained above. Use the Count Property of the SeriesCollection object to return the number of Series in a Series Collection viz. MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection.Count.

Sub SeriesCollectionObj_SeriesObj()
‘series collection & series object

Dim objSeries As Series

’embedded chart

With Sheets(“Sheet12”).ChartObjects(1).Chart

‘returns number of series in the series collection

MsgBox .SeriesCollection.count

‘refer single series (series object) – last series in the series collection – you can alternatively use: With .SeriesCollection(.SeriesCollection.count)

With .SeriesCollection.Item(.SeriesCollection.count)

‘turn on data labels

.HasDataLabels = True

End With

‘refer series collection using the Chart.SeriesCollection method – each series object in the series collection

For Each objSeries In .SeriesCollection

‘return name of each series in the series collection

MsgBox objSeries.Name

If objSeries.HasDataLabels = True Then

‘return series name if data labels is turned on

MsgBox objSeries.Name & ” series has data labels”

End If

Next objSeries

End With

End Sub

The Add method of the SeriesCollection object is used to create a new series and add to the chart as a member of its SeriesCollection. Syntax: objSeriesCollection.Add(Source, Rowcol, SeriesLabels, CategoryLabels, Replace). All arguments except the Source argument are optional to specify. The Source argument specifies the range for Source Data, for the new series. The Rowcol argument can either specify, xlRows (value 1) which specifies that values for the new series are in the rows of the source range, or, xlColumns (value 2)  which specifies that values for the new series are in the columns of the source range. For the SeriesLabels & CategoryLabels argument, True setting will indicate that the Series Lables (name of the data series) or Category Labels are contained in the first row or column of the source range, False setting indicates otherwise, and omitting the argument will make Excel attempt to determine if Labels are located in the first row or column. Setting the Replace argument to True where CategoryLabels is True, the current categories existing for the series will be replaced by the categories specified in the source range, and setting Replace to False (default) will not replace. You cannot use this method for PivotChart reports.

You can also use the NewSeries Method of the SeriesCollection object, to create a new seriesSyntax: objSeriesCollection.NewSeries. Using this method returns a Series object representing the new series whereas the Add method does not return a Series object. This method however, like the Add method, also cannot be used for PivotChart reports.

The Extend Method of the SeriesCollection object is used to extend an existing series collection by adding new data points. Syntax: objSeriesCollection.Extend(Source, Rowcol, CategoryLabels). All arguments have the same meaning as in the SeriesCollection.Add method above, and only the Source argument is necessary to specify. Ex. to extend the chart series by adding data points as per values in cells B6:C6 in Sheet1: Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection.Extend Source:=Worksheets(“Sheet12”).Range(“B6:C6”). This method also cannot be used for PivotChart reports.

Commonly used Properties of the Series object:

   

Property Syntax Description
AxisGroup Property objSeries.AxisGroup Returns a value per XlAxisGroup Enumeration (value 1 for xlPrimary, or value 2 for xlSecondary), specifying the AxisGroup for the series object.
Type Property objSeries.Type Sets or returns the series type as per the following constants: xl3DArea, xl3DBar, xl3DColumn, xl3DSurface, xlArea, xlBar, xlColumn, xlDoughnut, xlLine, xlPie, xlRadar, xlXYScatter.
Format Property objSeries.Format Returns a ChartFormat object which contains the line, fill & effect formatting for a chart element. The ChartFormat object provides access to the new Office Art formatting options that are available in Excel 2007 so that by using this object you can apply many new graphics to chart elements using vba formatting.
Name Property objSeries.Name returns the name of the Series object, as a string value.
Shadow Property objSeries.Shadow Sets or returns a Boolean value determining if the series has a shadow or not. Use True to add a shadow:

Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1).Shadow = True

PlotOrder Property objSeries.PlotOrder Sets or returns the plot order for a series within a collection of series with the same chart type (ie. within the chart group). Note that when the plot order of one series is changed within a chart group, the plot orders of the other series plotted in that chart group will also be adjusted. Also, in a chart where series are not plotted with the same format (ie. where you have more than one chart type), the plot order for the entire chart cannot be set. Ex. code to to make the series which is 3rd in plot order in Chart1 to appear as 1st in plot order – Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(3).PlotOrder = 1 –  this makes  series one to appear second in the plot order, and pushes the series 2 to series 3.
XValues Property objSeries.Xvalues Using the XValues Property, the x values (ie. values for the x-axis or horizontal axis) for a chart series are set or returned as an array. You can set this property to either the values contained in a range of cells (ie. Range object) or to an array of values, but not to both as a combination. This property is read-only for PivotChart reports.
Values Property objSeries.Values Using the Values Property, the values (ie. values for the value axis or y-axis) for a chart series are set or returned as an array. You can set this property to either the values contained in a range of cells (ie. Range object) or to an array of values, but not to both as a combination.
Has3DEffect Property objSeries.Has3DEffect This property (read-write) uses a Boolean value, & applies only to bubble charts – to set (or return) a three-dimensional appearance for the series, use True.
BubbleSizes Property objSeries.BubbleSizes The BubbleSizes Property of the Series object, sets or returns a string that refers to the worksheet cells which contain size data to set the bubble sizes (ie. size of the marker points). To set the cell reference you need to use R1C1-style notation, viz. objSeries.BubbleSizes = “=Sheet1!r2c4:r7c4”, and using the property to return the cell reference returns a string value in A1-style notation viz. Sheet1!$D$2:$D$7. You can also use the BubbleScale property of the ChartGroup object, to set or return a scale factor for the bubbles in a chart group, as an integer value between 0 to 300 which corresponds to a percentage of the original or default bubble size, viz. to scale the bubbles to 40% of their original size:

 

Sheets(“Sheet1”).ChartObjects(1).Chart.ChartGroups(1).BubbleScale = 40

 

Both the BubbleSizes & BubbleScale properties are applicable only to Bubble charts. A ChartGroup object represents all (can be one or more) series plotted in a chart with the same format  (line, bar, bubble, …).

Sub Series_XValues_Values_BubbleSizes_BubbleScale()
‘set the x values, y values & bubbles size for a series, and set a scale factor for the bubbles in a chart group

Dim ws As Worksheet
Set ws = Sheets(“Sheet1”)

With ws.ChartObjects(1).Chart

With .SeriesCollection(1)

.Name = ws.Range(“B1”)
‘using the XValues Property to set the x values (ie. values for the x-axis or horizontal axis) for a chart series, to the values contained in a range of cells (ie. Range object)
.XValues = ws.Range(“B2:B7”)
‘using the Values Property to set the y values (ie. values for the y-axis or value axis) for a chart series, to the values contained in a range of cells (ie. Range object)
.Values = ws.Range(“A2:A7”)
‘set the size of marker points (ie. bubbles size), using R1C1-style notation for the cell reference
.BubbleSizes = “=Sheet1!r2c4:r7c4

End With
‘scale the bubbles to 40% of their original size (for a ChartGroup)
.ChartGroups(1).BubbleScale = 40
‘returns a string value in A1-style notation ie. Sheet1!$D$2:$D$7
MsgBox .SeriesCollection(1).BubbleSizes
‘returns 40
MsgBox .ChartGroups(1).BubbleScale

End With

End Sub

HasDataLabels Property objSeries.HasDataLabels This property (read-write) uses a Boolean value – a chart series will have data labels only if the HasDataLabels property (of the Series object) is True viz. set to True for the data labels to be visible: objSeries.HasDataLabels = True. To turn on data labels for series 2 of a chart: Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(2).HasDataLabels = True. To turn off data labels for a series, set to False.
MarkerSize Property objSeries.MarkerSize This property sets or returns the data-marker size for data markers on a chart series, in points, which can be a value within 2 & 72.
MarkerStyle Property objSeries.MarkerStyle Sets or returns the marker style – this property is read-write, and for a series, or for a point, in a line / scatter / radar chart, it specifies the marker style as per constants defined in the XlMarkerStyle EnumerationxlMarkerStyleSquare (value 1: square markers); xlMarkerStyleDiamond (value 2: diamond-shaped markers); xlMarkerStyleTriangle (value 3: triangular markers); xlMarkerStyleStar (value 5: square markers with an asterisk); xlMarkerStyleCircle (value 8: circular markers); xlMarkerStylePlus (value 9: square markers with plus sign); xlMarkerStyleDash (value -4115: long bar markers); xlMarkerStyleDot (value -4118: short bar markers); xlMarkerStylePicture (value -4147: picture markers); xlMarkerStyleX (value -4168: square markers with an X); xlMarkerStyleAutomatic (value -4105: automatic markers); xlMarkerStyleNone (value -4142: no markers).
Smooth Property objSeries.Smooth This property (read-write) uses a Boolean value & is applicable only to Line & Scatter charts, & is used to turn on (True) or turn off (False – default) curve smoothing. Refer below codes where True sets the curve for a line chart to be smooth.
Image 1a

 

‘chart series without markers where Smooth is False (default) – refer Image 1a
Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1).Smooth = False

 

Image 1b

‘chart series without markers where Smooth is True – refer Image 1b
Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1).Smooth = True

 

MarkerBackground

Color Property

objSeries.

MarkerBackgroundColor

For a line, xy scatter & radar chart, this property sets the background color of point markers (ie. fill color for all markers in the series) using RGB values, and also returns the background color of point markers as a color index value (ie. as an index into the current color palette).
MarkerBackground

ColorIndex Property 

objSeries.

MarkerBackgroundColorIndex 

For a line, xy scatter & radar chart, this property sets or returns the background color of point markers (ie. fill color for all markers in the series) – this property specifies color as an index value into the current color palette, or as one of the constants – xlColorIndexAutomatic (value -4105: color is automatic) or xlColorIndexNone (value -4142: no color). 
MarkerForeground

Color Property 

objSeries.

MarkerForegroundColor 

For a line, xy scatter & radar chart, this property sets the foreground color of point markers (marker line ie. outline around the marker, color for all markers in the series) using RGB values, and also returns the foreground color of point markers as a color index value (ie. as an index into the current color palette).  
MarkerForeground

Color Property 

objSeries.

MarkerForegroundColor 

For a line, xy scatter & radar chart, this property sets or returns the foreground color of point markers (marker line ie. outline around the marker, color for all markers in the series) – this property specifies color as an index value into the current color palette, or as one of the constants – xlColorIndexAutomatic (value -4105: color is automatic) or xlColorIndexNone (value -4142: no color). 
Image 2

 

Sub Series_Line_Markers()
‘manipulating series line & markers – refer Image 2

’embedded chart – refer series 1
With Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1)

‘using the Line Property of the ChartFormat object, returns a LineFormat object that contains line formatting properties
‘switching the line visibility off & on seems necessary to set the line colour using the ForeColor Property of the LineFormat object (in Excel 2007)
.Format.Line.Visible = msoFalse
.Format.Line.Visible = msoTrue
‘using the Weight Property of the LineFormat object to set the weight for the line
‘sets both Series Line width AND Marker Border Line width to 2.5 pt
.Format.Line.Weight = 2.5
‘set the Series Line color AND the Marker Border Line color
.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
‘using the Style Property of the LineFormat object to set the style for the line (using a MsoLineStyle value)
‘2 thin lines
.Format.Line.Style = msoLineThinThin
‘on using the Border object for a series, the series line format resets – line style to Single & thickness to 2 points
‘use the Weight Property of the Border object to set the border weight (using a XlBorderWeight enumeration value)
‘reapply series line thickness only – sets series line width to 1 pt
.Border.Weight = xlThin
‘set the primary color of the Border using the Color property of the Border object – use the RGB function to create a color value; or use the ColorIndex Property of the Border object to set the border color.
‘ColorIndex = xlColorIndexAutomatic or xlColorIndexNone, or use ColorIndex 3 for red, 5 for blue, 4 for green, 6 for yellow, and so on.
‘set green color for line
.Border.ColorIndex = 4
‘alternatively:
‘.Border.Color = RGB(0, 255, 0)
‘using the LineStyle Property of the Border object to set (reapply) the line style for the series line, using the
XlLineStyle Enumeration of xlDash to specify the line style
.Border.LineStyle = xlDash
‘sets markers style to circular markers (MarkerStyle property of Series object)
.MarkerStyle = xlMarkerStyleCircle
‘sets the data-marker size to 8 points (MarkerSize property of Series object)
.MarkerSize = 8
‘sets the background color of point markers (ie. fill color for all markers in the series) using RGB values (MarkerBackgroundColor property of Series object)
.MarkerBackgroundColor = RGB(0, 0, 255)

End With

‘refer series 2
With Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(2)

‘sets the fill color for all markers in the series using RGB values (MarkerBackgroundColor property of Series object), to red
.MarkerBackgroundColor = RGB(255, 0, 0)
.Format.Line.Visible = msoFalse
.Format.Line.Visible = msoTrue
‘sets both Series Line width AND Marker Border Line width to 1.5 pt
.Format.Line.Weight = 1.5
‘set the Series Line color AND the Marker Border Line color to red
‘note that SchemeColor value is not the same as the ColorIndex value
.Format.Line.ForeColor.SchemeColor = 2
‘alternatively:
‘.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
‘use the Points Method of the Series object to return a single point (Point object) or a collection of all the points (Points collection)
Dim pt As Point
For Each pt In .Points

‘use the MarkerStyle Property of the Point object set the marker style for a point
pt.MarkerStyle = xlMarkerStyleSquare
‘set marker size to 6 points
pt.MarkerSize = 6

Next

‘use the Points Method of the Series object to return a single point (Point object) – refer point no 3
With .Points(3)

‘use the MarkerStyle Property of the Point object set the marker style for a point
.MarkerStyle = xlMarkerStyleStar
.MarkerSize = 8
‘this sets the Marker weight (marker’s border line width) & color and also sets the series line thickness & color for point no 2 to point no 3
.Format.Line.Weight = 3
.Format.Line.Visible = msoFalse
.Format.Line.Visible = msoTrue
‘set color to blue
.Format.Line.ForeColor.RGB = RGB(0, 0, 255)

End With

End With

End Sub

BarShape Property  objSeries.BarShape  For a column chart or a 3-D bar chart, this property sets or returns the shape used with a series, by specifying the shape as per constants defined in XlBarShape Enumeration: xlBox (value 0 – Box); xlPyramidToPoint (value 1 – Pyramid, coming to point at value); xlPyramidToMax (value 2 – Pyramid, truncated at value); xlCylinder (value 3 – Cylinder); xlConeToPoint (value 4 – Cone, coming to point at value); xlConeToMax (value 5 – Cone, truncated at value). 
    ‘set the bar shape, for series 1 of embedded chart 1 in Sheet 1, to Cylinder

Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1).BarShape = xlCylinder 

PictureType Property objSeries.PictureType Sets or returns the picture display format on a column or bar picture chart, as per constants defined in XlChartPictureType EnumerationxlStretch (value 1, picture is stretched the full length of the stacked bar), xlStack (value 2, picture is sized to repeat a maximum of 15 times in the longest stacked bar), xlStackScale (value 3, picture is sized to a specified number of units & repeated the length of the bar).
PictureUnit2 Property objSeries.PictureUnit2 The PictureUnit2 Property sets or returns the stack or scale unit for each picture (ie. n units per picture) as a double value, only where the PictureType property is set to xlStackScale or else the property has no effect.
ApplyPictToFront Property / ApplyPictToEnd Property / ApplyPictToSides Property objSeries.ApplyPictToFront / objSeries.ApplyPictToEnd / objSeries.ApplyPictToSides For a series where pictures are already applied, these properties are used to change the picture orientation – True displays the pictures on the front of the column or bar of the point(s) / on the end of the column or bar of the point(s) / on the sides of the column or bar of the point(s), respectively.
Image 3

 

Sub Series_Picture()
‘set an image to the fill in a series; set the picture display format on a column chart; set the stack or scale unit for each picture; – refer Image 3

‘for the Plot Area Fill, ie. FillFormat object
With Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1)

‘use the UserPicture Method of the FillFormat object to set an image to the specified fill
.Fill.UserPicture “C:\Users\Amit Tandon\Documents\Photos\Dollar.JPG”

‘use the PictureType Property to set the picture display format on a column chart
‘all points in series 1 have streched picture (default value)
.PictureType = xlStretch

‘set PictureType Property for point 2 to xlStack (picture is sized to repeat a maximum of 15 times)
.Points(2).PictureType = xlStack

‘set point 3 to xlStackScale – picture is sized to a specified no. of units & repeated the length of the bar
.Points(3).PictureType = xlStackScale
‘use the PictureUnit2 Property to set the stack or scale unit for each picture
‘scales to 1000 units per picture ie. a value (in Point 3 of Series 1) of 5000 units will show 5 pictures
.Points(3).PictureUnit2 = 1000

End With

End Sub

ErrorBars Property objSeries.ErrorBars ErrorBars: You can turn on error bars for a series, which indicate the degree of uncertainty for chart data. Error bars can be used for data series only in area, bar, column, line & scatter groups on a 2-D chart, where xy scatter & bubble charts can display error bars for the x values or the y values or both. Error bars (x or y) can either be turned on for all points in a series, or none of them – you cannot have a single error bar. Use the ErrorBar method to create the error bars. The ErrorBars property of the series object is used to return a reference to the ErrorBars object. Use the ErrorBars object to manipulate the appearance of error bars.
Formula Property / FormulaR1C1 Property objSeries.Formula / objSeries.FormulaR1C1 The Formula Property sets or returns a String value for the series formula in A1-style notation, in the same format as displayed in the formula bar with the equal sign. When a series is selected, the formula bar displays that series’ formula. Using the Formula property will return this formula as a string value. The series formula describes data in the series, with the four elements: =objSeries(Series Name, X-Values, Y-Values, Plot Order). The FormulaR1C1 Property of the Series object is also similar to the Formula property except that it sets or returns the series formula using R1C1 format for cell references.

 

The Series Name can be a text string within double quotation marks viz. “Profit ($)”, a reference to a range consisting of one or more worksheet cells viz. Sheet1!$B$1, a reference to a named range, or it can be left blank. The X-Values can be a literal array of numeric values or text labels enclosed in curly braces viz. {2009,2010,2011,2012} or {“Jan”,”Feb”,”Mar”,”Apr”,”May”,”June”}, a reference to a range consisting of one or more worksheet cells viz. Sheet1!$A$2:$A$5 or Sheet1!R2C1:R5C1, a reference to a named range, or it can be left blank. The Y-Values can be a literal array of numeric values enclosed in curly braces viz. {6000,7200,4700,8100}, a reference to a range consisting of one or more worksheet cells viz. Sheet1!$B$2:$B$5 or Sheet1!R2C2:R5C2, or a reference to a named range. The Plot Order should be a whole number with a value between 1 & the total number of series.

FormulaLocal Property / FormulaR1C1Local Property objSeries.FormulaLocal / objSeries.FormulaR1C1Local Formula property of the Series object constructs & returns formula strings using English functions & US number formats – the FormulaLocal property of the Series object is the same as the Formula property, where the user’s language settings are used and not English to create the formula so that formula strings are used as they appear on the worksheet & constructed using the Office UI language & WRS number formats. The FormulaR1C1Local Property of the Series object sets or returns the series formula using R1C1-style notation in the language of the user ie. where the user’s language settings are used.
Image 4a

 

Sub Series_Formulas_1()
‘set String values for the series formula in vba – refer Image 4a

Dim ws As Worksheet, srs As Series
Set ws = Sheets(“Sheet1”)
Dim oChObj As ChartObject
Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“A”).Left, Width:=300, Top:=ws.Rows(6).Top, Height:=180)

With oChObj.Chart

.ChartType = xlLineMarkers
.HasTitle = True
.ChartTitle.Text = “LineChart”
.ChartTitle.Font.Size = 12
Set srs = .SeriesCollection.NewSeries
‘set series formula by assigning values for Series Name, X Values, Y Values & Plot Order – refer Image 30a for the below 4 codes
srs.Formula = “=Series(Sheet1!$B$1,Sheet1!$A$2:$A$5,Sheet1!$B$2:$B$5,1)
srs.Formula = “=SERIES(“”Sales ($)””,Sheet1!R2C1:R5C1,Sheet1!R2C2:R5C2,1)
srs.Formula = “=Series(Sheet1!$B$1,{“”Jan””,””Feb””,””Mar””,””Apr””},Sheet1!$B$2:$B$5,1)
srs.Formula = “=Series(Sheet1!$B$1,{“”Jan””,””Feb””,””Mar””,””Apr””},{6000,7200,4700,8100},1)
‘X Values are an array of numbers
srs.Formula = “=Series(Sheet1!$B$1,{2009,2010,2011,2012},Sheet1!$B$2:$B$5,1)

End With

End Sub

Image 4b

 

Sub Series_Formulas_2()
‘substitute or replace part of series formula in vba – refer Image 4b
‘continuing with previous example (Image 4a), the series Y Values are replaced from $B$2:$B$5 to $C$2:$C$5 and series name is replaced from “Sheet15!$B$1” to “Sheet15!$C$1”

Dim oChObj As ChartObject, srs As Series, strOld As String, strNew As String
Set oChObj = Sheets(“Sheet1”).ChartObjects(1)

With oChObj.Chart

Set srs = .SeriesCollection(1)

‘using the vba Replace function to replace part formula – refer Image 4b:
‘change Y Values of series
srs.Formula = Replace(srs.Formula, “Sheet1!$B$2:$B$5”, “Sheet1!$C$2:$C$5”)
‘change series Name
srs.Formula = Replace(srs.Formula, “Sheet1!$B$1”, “Sheet1!$C$1”)

‘using the worksheet Substitute function to change or replace the whole series formula – refer Image 4b:
strOld = srs.Formula
strNew = “=Series(Sheet1!$C$1,Sheet1!$A$2:$A$5,Sheet1!$C$2:$C$5,1)
srs.Formula = WorksheetFunction.Substitute(srs.Formula, strOld, strNew)

End With

End Sub

LeaderLines Property objSeries.LeaderLines Use the LeaderLines property of the Series object to get a reference to the LeaderLines object which manipulates the appearance of leader lines. LeaderLines are lines which connect data labels to the data points in a series, & are available only for pie charts.
HasLeaderLines Property objSeries.HasLeaderLines This property (read-write) uses a Boolean value & is applicable only to pie-charts – a chart series will have leader lines only if the HasLeaderLines property (of the Series object) is True viz. the leader lines will be visible only if this property returns True or is set to True: objSeries.HasLeaderLines = True.
 
Commonly used Methods of the Series object:

 

Method Syntax Description
Delete Method objSeries.Delete deletes the series object
Select Method  objSeries.Select  selects the series object 
ClearFormats Method  objSeries.ClearFormats  clears all formatting from a chart series (from a Series object) 
Points Method  objSeries.Points(Index)  Returns (read-only) either a single point (Point object) or all points (Points object – collection of all Point objects) in a series. The Index argument (optional) specifies the number or name of the point – refer below code. 

‘use the Points Method of the Series object to return a collection of all the points (Points object)
Dim pt As Point
For Each pt In Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(2).Points

‘use the MarkerStyle Property of the Point object set the marker style for a point
pt.MarkerStyle = xlMarkerStyleSquare
‘set marker size to 6 points
pt.MarkerSize = 6

Next

‘use the Points Method of the Series object to return a single point (Point object)
‘set the marker style for point no. 3 in series 2 of embedded chart, to Star
Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(2).Points(3).MarkerStyle = xlMarkerStyleStar

DataLabels Method  objSeries.DataLabels(Index)  Returns (read-only) either a single data label (DataLabel object) or all data labels (DataLabels object) in a series. The Index argument (optional) specifies the number of the data label. The DataLabels object is a collection of all DataLabel objects for each point, and where the series does not have definable points (viz. an area series) the DataLabels object comprises of a single DataLabel object. The DataLabel Property of the Point object returns a DataLabel object (ie. single data label) associated with that point. Using the ApplyDataLabels Method of the Series Object / Chart Object you can apply labels to all points of a data series or to all data series of a chart uniformly,  
Sub Series_DataLabels_1()

‘series 1 of embedded chart
With Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1)

‘set the HasDataLabels Property to True to turn on data labels for a series
.HasDataLabels = True

‘using the DataLabels Method & the Index argument to return a single data label in a series
.DataLabels(1).Font.Size = 6

‘Points Method of the Series object returns a single point (Point object) & the DataLabel Property of the Point object returns a DataLabel object (ie. single data label) associated with that point
.Points(2).DataLabel.Font.Size = 12

‘using the DataLabels Method to return all data labels in a series
.DataLabels.Font.Color = vbRed

End With

End Sub

ApplyDataLabels Method  objSeries.

ApplyDataLabels(Type, LegendKey, AutoText, HasLeaderLines, ShowSeriesName, ShowCategoryName, ShowValue, ShowPercentage, ShowBubbleSize, Separator) 

Use this method to apply data labels to a 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 for a chart series:

Sheets(“Sheet1″).ChartObjects(1).Chart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent, LegendKey:=True, ShowValue:=True, Separator:=”/

Trendlines Method  objSeries.Trendlines(Index)  A trendline shows the trend or direction of data in a chart series, reflecting the data fluctuations. The Trendlines Method returns (read-only) either a single Trendline (Trendline object) or all Trendlines (Trendlines object) in a single series. The Index argument (optional) specifies the number or name of the Trendline – index number represents the order in which the trendlines were added, with the first trendline added to the series having index number 1 ie. objSeries.Trendlines(1), and the last added trendline being objSeries.Trendlines(objSeries.Trendlines.Count). You can manipulate a trendline by using the Trendline Object through its properties & methods.

 

Use the Type Property of the Trendline object to set or return the type of trendline as per constants specified in XlTrendlineType Enumeration: xlPolynomial (value 3); xlPower (value 4); xlExponential (value 5); xlMovingAvg (value 6); xlLinear (value -4132); xlLogarithmic (value -4133). These constants represent using an equation to calculate the least squares fit through points, for example the xlLinear constant uses the linear equation. 

  To create a new trendline for a series, use the Add Method of the Trendlines object. Syntax: objTrendlines.Add(Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name). Trendlines are not allowed for 3-D, pie, doughnut & radar chart types. All arguments are optional to specify. The Type argument specifies the type of trendline to add,  as per constants specified in XlTrendlineType Enumeration – refer above.

 

The Order argument specifies the trendline order if the Type is xlPolynomial, & should be an integer between 2 to 6. The Period argument specifies the trendline period for the xlMovingAvg Type, & is an integer greater than 1 & less than the number of data points in the series to include for the moving average. Use the Forward & Backward arguments to project a trendline beyond the series in either direction wherein the axis is automatically scaled for the new range: the Forward argument specifies the number of periods (units in the case of scatter charts) for which the trendline is extended forward to; the Backward argument specifies the number of periods (units in the case of scatter charts) for which the trendline is extended backwards to.

 

The Intercept argument specifies where the x-axis is crossed by the trendline, where the intercept is automatically set by the regression, as default when the argument is omitted. The DisplayEquation argument when set to True (default is False) displays the trendline formula or equation in the same data label that displats the R-squared value. The DisplayRSquared argument when set to True (default is False) displays the trendline’s R-squared value in the same data label that displats the equation. The Name argument displays the text for the trendline name in its legend, and is the argument is omitted the name is generated automatically by Microsoft Excel. 

Example: Add & manipulate trendlines for scatter chart – refer Image 5

 

Image 5

 

Sub Series_Trendlines_1()
‘add & manipulate trendlines for scatter chart – refer Image 5
‘Trendlines are often used to show sales or profit trends & predict future sales / profits. Trendlines are generated to show the trend or direction of data in a chart series, reflecting the data fluctuations over a period of time and in this process the equation of the trendline is calculated.

‘add 3 trendlines for series 1 (Profit)
With Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1)

‘delete existing trendlines – deleting a single trendline will reindex the collection, hence trendlines should be deleted in reverse order
For i = .Trendlines.count To 1 Step -1

.Trendlines(i).Delete

Next i

‘the Type argument specifies the type of trendline to add
.Trendlines.Add Type:=xlLinear
.Trendlines.Add Type:=xlLogarithmic
.Trendlines.Add Type:=xlPolynomial

With .Trendlines(1)

.Format.Line.Weight = 2
‘set trendline color to blue
.Border.ColorIndex = 5

End With

With .Trendlines(2)

.Type = xlMovingAvg
‘the Period argument specifies the trendline period for the xlMovingAvg Type
.Period = 2
‘set trendline color to green
.Border.ColorIndex = 4
.Border.Weight = xlMedium

End With

With .Trendlines(3)

.Order = 4
‘set trendline color to red
.Border.ColorIndex = 3
.Border.Weight = xlMedium
.DisplayEquation = False
‘setting DisplayRSquared property to True turns on data labels for a trendline
.DisplayRSquared = True
‘set R-squared value / datalabel font color to red
.DataLabel.Font.ColorIndex = 3

End With

End With

‘add 1 trendline for series 2 (Advt Exp)
With Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(2)

‘delete existing trendlines – deleting a single trendline will reindex the collection, hence trendlines should be deleted in reverse order
For i = .Trendlines.count To 1 Step -1

.Trendlines(i).Delete

Next i
‘the Type argument specifies the type of trendline to add
.Trendlines.Add Type:=xlPower
With .Trendlines(1)

‘set trendline color to dark red
.Border.ColorIndex = 9
.Format.Line.DashStyle = msoLineLongDash
.Name = “2Period MvgAvg (Advt)
.Format.Line.Weight = 1.75
‘to dislay equation
‘.DisplayEquation = True
‘set number format for equation / datalabel
‘.DataLabel.NumberFormat = “0.00E+00

End With

End With

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend

‘set font color to dark red for the legend entry of trendline “2Period MvgAvg (Advt)”
.LegendEntries(.LegendEntries.count).Font.ColorIndex = 9

End With

 

End Sub

ErrorBar Method objSeries.ErrorBar(Direction, Include, Type, Amount, MinusValues) The ErrorBar method is used to apply error bars to a series. The Direction, Include & Type arguments are necessary to specify, while the Amount & MinusValues arguments are optional.

 

The Direction argument specifies the direction of the bars as to the axis values which receives error bars: xlY (value 1) specifies bars for y-axis values in the Y direction; xlX (value -4168) specifies bars for x-axis values in the X direction.

 

The Include argument specifies the error-bar parts which are to be included: xlErrorBarIncludeBoth (value 1) – includes both positive & negative error range; xlErrorBarIncludePlusValues (value 2) – includes only positive error range; xlErrorBarIncludeMinusValues (value 3) – includes only negative error range; xlErrorBarIncludeNone (value -4142) – no error bar range included.

 

The Type argument specifies the type as to the range marked by error bars: xlErrorBarTypeFixedValue (value 1) – error bars with fixed-length; xlErrorBarTypePercent (value 2) – percentage of range to be covered by the error bars; xlErrorBarTypeStError (value 4) – shows standard error range; xlErrorBarTypeCustom (value -4114) – range is set by fixed values or cell values; xlErrorBarTypeStDev (value -4155) – Shows range for specified number of standard deviations.

 

The Amount argument specifies the error amount, and when Type is xlErrorBarTypeCustom it is used for only the positive error amount. The MinusValues argument specifies the negative error amount when Type is xlErrorBarTypeCustom.

Child Objects for the Series Object: Above, we have discussed: the ChartFormat object – use the Format PropertyobjSeries.Format – to return the ChartFormat object which contains the line, fill & effect formatting for the chart series; ErrorBars object – use the ErrorBars PropertyobjSeries.ErrorBars – to turn on error bars for a series which indicate the degree of uncertainty for chart data; LeaderLines object – use the LeaderLines propertyobjSeries.LeaderLines – to get a reference to the LeaderLines object which manipulates the appearance of leader lines. Some other child objects which are often used with the Series Object include: ChartFillFormat Object – use the Fill PropertyobjSeries.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements; Interior Object – use the Interior propertyobjSeries.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border PropertyobjSeries.Border – to return a Border object, to manipulate a chart element’s border.

Example: Create & manipulate error bars for a 2-D line chart – refer Images 6a & 6b

To download Excel file with live code, click here.

Image 6a
Image 6b

Sub ErrorBars_1()
‘create & manipulate error bars for a 2-D line chart – refer Images 6a & 6b

Dim oChObj As ChartObject, rngSourceData As Range, ws As Worksheet

Set ws = Sheets(“Sheet1”)

Set rngSourceData = ws.Range(“B1:C7”)

Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“A”).Left, Width:=290, Top:=ws.Rows(8).Top, Height:=190)

With oChObj.Chart

.ChartType = xlLineMarkers

.SetSourceData Source:=rngSourceData, PlotBy:=xlColumns

.Axes(xlCategory).CategoryNames = ws.Range(“A2:A7”)

.HasTitle = True

With .ChartTitle

.Caption = ws.Range(“B1″) & ” – ” & ws.Range(“C1”)

.Font.Size = 12

.Font.Bold = True

End With

With .SeriesCollection(1)

‘specifies bars for y-axis values which receives error bars; includes both positive & negative error range; range to be covered by the error bars is specified as percentage; error amount specified as 10%;

.ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypePercent, Amount:=10

‘where you specify bars to include only positive error range, the error bar will NOT be drawn in the negative direction – refer Image 28b

‘where a positive error bar has negative values, it will be drawn in the negative direction.

‘.ErrorBar Direction:=xlY, Include:=xlErrorBarIncludePlusValues, Type:=xlErrorBarTypePercent, Amount:=10

‘set color/style formatting for the vertical(Y) Error Bars

With .ErrorBars

.Format.Line.Visible = msoFalse

.Format.Line.Visible = msoTrue

‘using ErrorBars.EndStyle Property – to not apply caps, use xlNoCap

.EndStyle = xlCap

‘.Format.Line.DashStyle = msoLineSysDash

.Format.Line.Transparency = 0

.Format.Line.Weight = 1.5

.Format.Line.ForeColor.RGB = RGB(255, 0, 0)

End With

End With

End With

End Sub

For below Examples, refer section on “Create Line, Column, Pie, Scatter, Bubble charts”.

Example: Create a pie chart with leader lines & custom color the slices of the pie – refer above section.

Example: Create XY Scatter chart having single series with X & Y values – refer above section.

Example: Create XY Scatter chart with multiple series where all series share the same Y values with distinct X values – refer above section.

Example: Create Bubble chart multiple series where all series share the same Y values with distinct X values – refer above section.

Example: Create & manipulate both X & Y error bars, types xlErrorBarTypePercent & xlErrorBarTypeCustom, for a bubble chart – refer above section.

Example: Setting forth the object heirarchy for formatting series line & points of embedded chart (chart type line & markers) – refer Image 7 – series 4

Image 7

Sub Object_Heirarchy()
‘Setting forth the object heirarchy for formatting series line & points of embedded chart (chart type line & markers)
‘refer Image 7 – series 4

‘—————————————————

‘set red inside color (ie. fill color) for all markers of a chart series – does not set the series line color or the border color for markers

‘Setting forth the object heirarchy:

‘”Sheet1″ contains a collection of embedded charts (ChartObjects object)

‘”Chart 5″ is the name of a single embedded chart (ChartObject object) in that collection

‘the Chart Property of the ChartObject returns a Chart object which refers to a chart contained in the ChartObject object

‘The Chart object has SeriesCollection object (a collection of all the Series objects in a chart), and one of the Series object has an index number 4

‘The Format property of the Series object returns a ChartFormat object which contains the line, fill & effect formatting for that Series

‘The Fill Property of the ChartFormat Object returns a FillFormat object containing fill formatting properties

‘The ForeColor Property returns a ColorFormat object that represents the foreground fill ‘ solid color

‘The RGB property (explicit Red-Green-Blue value) sets a color for the ColorFormat object

Sheets(“Sheet1”).ChartObjects(“Chart 5”).Chart.SeriesCollection(4).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)

‘—————————————————

‘set the Line color AND the Marker Border Line (for all markers) color of a chart series

‘Setting forth the object heirarchy:

‘”Sheet1″ contains a collection of embedded charts (ChartObjects object)

‘”Chart 5″ is the name of a single embedded chart (ChartObject object) in that collection

‘the Chart Property of the ChartObject returns a Chart object which refers to a chart contained in the ChartObject object

‘The Chart object has SeriesCollection object (a collection of all the Series objects in a chart), and one of the Series object has an index number 4

‘The Format property of the Series object returns a ChartFormat object which contains the line, fill & effect formatting for that Series

‘The Line Property of the ChartFormat Object returns a LineFormat object (can be a Line itself or a border) containing line & arrowhead formatting properties

‘The ForeColor Property returns a ColorFormat object that represents the foreground fill / solid color

‘The RGB property (explicit Red-Green-Blue value) sets a color for the ColorFormat object

Sheets(“Sheet1”).ChartObjects(“Chart 5”).Chart.SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(255, 0, 0)

‘—————————————————

‘set the dash style for BOTH the Series Line AND the Marker Border Line, for a single point of a chart series – for marker point no 3 & series line point no 2 to point no 3

‘Setting forth the object heirarchy:

‘”Sheet1″ contains a collection of embedded charts (ChartObjects object)

‘”Chart 5″ is the name of a single embedded chart (ChartObject object) in that collection

‘The Chart Property of the ChartObject returns a Chart object which refers to a chart contained in the ChartObject object

‘The Chart object has SeriesCollection object (a collection of all the Series objects in a chart), and one of the Series object has an index number 4

‘The Points Method of the Series object returns a Point object (single point)

‘The Format property of the Series object returns a ChartFormat object which contains the line, fill & effect formatting for that Series

‘The Line Property of the ChartFormat Object returns a LineFormat object (can be a Line itself or a border) containing line & arrowhead formatting properties

‘The DashStyle Property of the LineFormat object sets the dash style for the line

Sheets(“Sheet1”).ChartObjects(“Chart 5”).Chart.SeriesCollection(4).Points(3).Format.Line.DashStyle = msoLineDashDotDot

‘—————————————————

‘set color for chart series line, for a single point – series line from point no 1 to point no 2

‘Setting forth the object heirarchy:

 

‘”Sheet1″ contains a collection of embedded charts (ChartObjects object)

‘”Chart 5″ is the name of a single embedded chart (ChartObject object) in that collection

‘The Chart Property of the ChartObject returns a Chart object which refers to a chart contained in the ChartObject object

‘The Chart object has SeriesCollection object (a collection of all the Series objects in a chart), and one of the Series object has an index number 4

‘The Points Method of the Series object returns a Point object (single point)

‘The Border Property returns a Border object

‘The Color Property (of the Border object) sets the primary color for the Border object

‘The RGB function creates a color value

Sheets(“Sheet1”).ChartObjects(4).Chart.SeriesCollection(4).Points(2).Border.Color = RGB(0, 0, 255)

‘—————————————————

End Sub

A Combination Chart means where two or more chart types are combined for a single chart. Combination charts can be created using the Chart.ApplyCustomType Method, however the ApplyCustomType Method may not work in Excel 2007, & you might alternatively need to use ApplyChartTemplate Method or ApplyLayout Method of the Chart object
Below is an illustration to create a Combination Chart in Excel 2007 – a dynamic Column-Line chart (clustered columns combined with line marker), wherein first you create a clustered column chart type and then add a new series of Type “xlLine” on secondary axis.

Example: Combination Chart – create a dynamic ColumnLine chart – clustered columns combined with line marker – refer Image 8

Image 8

Sub Chart_Combination_ColumnLine_Dynamic()
‘Combination Chart (where two or more chart types are combined for a single chart), without using the Chart.ApplyCustomType Method
‘create a dynamic Column-Line chart – clustered columns combined with line marker – create a clustered column chart type and then add a new series of Type “xlLine” on secondary axis – refer Image 8

‘Dynamic Chart – the parent data is given in the range “A10:F16”. You can select the Student Names in range “A3:A6” and the appropriate data will update from the Parent Data

Dim rngSourceData As Range, ws As Worksheet

Set ws = Sheets(“Sheet1”)

Set rngSourceData = ws.Range(“B2:E6”)

‘declare a ChartObject

Dim oChObj As ChartObject

‘delete existing embedded charts in the worksheet

For Each oChObj In ws.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 I, top edge align to the

top of row 2

Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“I”).Left, Width:=370, Top:=ws.Rows(2).Top, Height:=210)

‘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 – Clustered Column

.ChartType = xlColumnClustered

‘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

‘refer the Axis object – category axis

With .Axes(xlCategory, xlPrimary)

‘use the CategoryNames property for setting the category names for a category axis, as a text array, to the values contained in a range of cells (ie. Range object) or to a text array containing category names.

.CategoryNames = ws.Range(“A3:A6”)

‘the TickLabels object represents all the tick-mark labels (as a unit) – set font to bold

.TickLabels.Font.Bold = True

End With

‘use the NewSeries Method of the SeriesCollection object, to create a new series. Using this method returns a Series object representing the new series whereas the Add method does not return a Series object.

Dim MySeries As Series

Set MySeries = .SeriesCollection.NewSeries

‘plot the new series as line with markers, on secondary axis

With MySeries

.Type = xlLine

‘set the axis group (Primary axis group) for the specified series using the Series.AxisGroup Property

.AxisGroup = xlSecondary

‘using the MarkerStyle Property of the Series object to set the marker style for the series, per the XlMarkerStyle constants (using a diamond-shaped marker here)

.MarkerStyle = xlMarkerStyleDiamond

‘using the MarkerSize property of the Series object to set the data-marker size, in points

.MarkerSize = 7

.Name = ws.Range(“F2”)

‘using the Values property of the Series object, set Y values for new series

.Values = ws.Range(“F3:F6”)

‘set red color for series line

.Border.ColorIndex = 3

‘set red border line color for marker

.MarkerForegroundColor = RGB(255, 0, 0)

‘set red inside color (ie. fill color) for marker

.MarkerBackgroundColor = RGB(255, 0, 0)

End With

‘Format property of the Series object returns returns the ChartFormat object – use the Fill Property of the ChartFormat object to return a FillFormat object (which contains fill formatting properties), to set the foreground fill color for

the Series

‘set fill color for each series (columns)

.SeriesCollection(ws.Range(“B2”).Value).Format.Fill.ForeColor.RGB = RGB(0, 176, 240)

.SeriesCollection(ws.Range(“C2”).Value).Format.Fill.ForeColor.RGB = RGB(228, 108, 10)

.SeriesCollection(ws.Range(“D2”).Value).Format.Fill.ForeColor.RGB = RGB(126, 194, 52)

.SeriesCollection(ws.Range(“E2”).Value).Format.Fill.ForeColor.RGB = RGB(153, 130, 180)

‘set fill color for chart area

.ChartArea.Format.Fill.ForeColor.RGB = RGB(252, 213, 181)

‘set fill color for plot area

.PlotArea.Format.Fill.ForeColor.RGB = RGB(242, 220, 219)

‘the ChartTitle object represents the chart title

With .ChartTitle

‘set the text for the chart title, using the Text Property of the ChartTitle object

.Text = “Column-Line Chart: Student Marks

‘set the font to Bold Arial 11 points

.Font.Name = “Arial

.Font.Size = 11

.Font.Bold = True

‘set font color for chart title

.Font.Color = RGB(0, 30, 90)

‘set fill color for the ChartTitle

.Format.Fill.ForeColor.RGB = RGB(255, 192, 0)

‘set border color using Color Property of the Border object

.Border.Color = RGB(0, 30, 90)

End With

End With

End Sub

Example: Part 5 of 8 – Manipulate Chart Series – refer Image 1.5

Image 1.5

Sub EmbChart_ChartSeries_5()
‘manipulate Chart Series – refer Image 1.5

Dim wsData As Worksheet, wsChart As Worksheet

‘declare a ChartObject object

Dim oChObj As ChartObject

Set wsData = Sheets(“Sheet18”)

Set wsChart = Sheets(“Sheet19”)

‘set ChartObject object by index number

Set oChObj = wsChart.ChartObjects(1)

‘using the Chart Property of the ChartObject returns a Chart object which refers to a chart

With oChObj.Chart

‘using the Chart.SeriesCollection Method to return a single series (Series object) by its name

With .SeriesCollection(wsData.Range(“C1”).Value)

‘using the MarkerStyle Property of the Series object to set the marker style for the series, per the XlMarkerStyle constants (using a diamond-shaped marker here)

.MarkerStyle = xlMarkerStyleDiamond

‘using the MarkerSize property of the Series object to set the data-marker size, in points

.MarkerSize = 8

‘use the Border property to return a Border object – set the primary color of the Border using the Color property of the Border object – use the RGB function to create a color value, or use the ColorIndex Property of the Border object

to set the border color

‘set red color for series line

.Border.Color = RGB(255, 0, 0)

‘alternatively:

‘.Border.ColorIndex = 3

‘set red line color for marker border

.MarkerForegroundColorIndex = 3

‘alternatively:

‘.MarkerForegroundColor = RGB(255, 0, 0)

‘set red inside color (ie. fill color) for marker

.MarkerBackgroundColorIndex = 3

‘alternatively:

‘.MarkerBackgroundColor = RGB(255, 0, 0)

‘you might need to differentiate a period or point for an important happening

‘use the Points Method of the Series object to return a single point (Point object) ie. point no 3

With .Points(3)

‘use the MarkerStyle Property of the Point object to set the marker style for a point: xlMarkerStyleCircle – Circular marker

.MarkerStyle = xlMarkerStyleCircle

‘Use the Format Property of the Point object to return the ChartFormat object which contains the line, fill & effect formatting for the series, and provides access to the Office Art formatting options so that you can apply many new

graphics. The Line property of the ChartFormat object returns a LineFormat object that contains line formatting properties.

‘this sets – the Marker weight (marker’s border line width) & color, and also sets the series line thickness & color – for point no 2 to point no 3

.Format.Line.Weight = 3

.Format.Line.Visible = msoFalse

.Format.Line.Visible = msoTrue

‘dark red

.Format.Line.ForeColor.RGB = RGB(192, 0, 0)

‘set orange inside color (ie. fill color) for marker

.MarkerBackgroundColor = RGB(255, 192, 0)

End With

End With

‘———————————————-

‘Series2

With .SeriesCollection(wsData.Range(“D1”).Value)

‘set blue color for series line

.Border.ColorIndex = 5

‘set blue border line color for marker

.MarkerForegroundColor = RGB(0, 0, 255)

‘set blue inside color (ie. fill color) for marker

.MarkerBackgroundColor = RGB(0, 0, 255)

.Format.Line.Style = msoLineThinThin

‘use the Points Method of the Series object to return a single point (Point object) or a collection of all the points (Points collection)

Dim pt As Point

For Each pt In .Points

With pt

‘use the MarkerStyle Property of the Point object set the marker style for a point

.MarkerStyle = xlMarkerStyleCircle

‘this sets the Marker weight (marker’s border line width) and also sets the series line width / thickness

.Format.Line.Weight = 3

End With

Next

End With

‘———————————————-

‘use the NewSeries Method of the SeriesCollection object, to create a new series. Using this method returns a Series object representing the new series whereas the Add method does not return a Series object.

With .SeriesCollection.NewSeries

‘set the axis group (Primary axis group) for the specified series using the Series.AxisGroup Property

.AxisGroup = xlPrimary

‘using the MarkerStyle Property of the Series object to set the marker style for the series, per the XlMarkerStyle constants (using a diamond-shaped marker here)

.MarkerStyle = xlMarkerStyleDiamond

‘using the MarkerSize property of the Series object to set the data-marker size, in points

.MarkerSize = 8

.Name = wsData.Range(“E1”)

‘using the Values property of the Series object, set Y values for new series

.Values = wsData.Range(“E2:E9”)

‘using the XValues property of the Series object, set an array of X values or Category Labels, for new series

.XValues = wsData.Range(“A2:B9”)

‘use the Format Property of the Series object to return the ChartFormat object which contains the line, fill & effect formatting for the series

With .Format

‘set red inside color (ie. fill color) for marker

.Fill.ForeColor.RGB = RGB(149, 55, 53)

‘using the Line Property of the ChartFormat object, returns a LineFormat object that contains line formatting properties

‘switching the line visibility off & on seems necessary to set the line colour using the ForeColor Property of the LineFormat object (in Excel 2007)

.Line.Visible = msoFalse

.Line.Visible = msoTrue

‘set the Series Line color AND the Marker Border Line color

.Line.ForeColor.RGB = RGB(149, 55, 53)

‘using the Style Property of the LineFormat object to set the style for the line (using a MsoLineStyle value)

.Line.Style = msoLineSingle

‘using the DashStyle Property of the LineFormat object to set the dash style for the line (using a MsoLineDashStyle constant) – this sets the dash style for BOTH the Series Line AND the Marker Border Line – set a solid line

.Line.DashStyle = msoLineSolid

‘using the Weight Property of the LineFormat object to set the weight for the line

‘sets both Series Line width AND Marker Border Line width to 2.5 pts

.Line.Weight = 2.5

End With

‘use the Weight Property of the Border object to set the border weight (using a XlBorderWeight enumeration value)

‘reapply series line thickness only – sets series line width to 2 pts (so that Marker Border Line width remains at 2.5 pts)

.Border.Weight = xlMedium

‘using the LineStyle Property of the Border object to set (reapply) the line style for the series line, using the XlLineStyle Enumeration of xlDash to specify the line style

.Border.LineStyle = xlDash

End With

End With

End Sub

DataLabels Object / DataLabel Object

Above we have discussed using the DataLabels Method to return (read-only) either a single data label (DataLabel object) or all data labels (DataLabels object) in a series, and using the ApplyDataLabels Method to to apply data labels to a chart series. We now explain how you can manipulate the data label(s) for a series, using the properties & methods of the DataLabels Object / DataLabel Object. DataLabels.Count PropertyobjDataLabels.Count – returns the number of data labels in a collection / series. The DataLabels.Item MethodobjDataLabels.Item(Index) – returns a single data label in the collection or series.

Example: Manipulate data labels in a series, using the DataLabels object / DataLabel object

Sub Series_DataLabels_1()
‘manipulate data labels in a series, using the DataLabels object / DataLabel object

Dim i As Integer

 

‘refer series 1 of embedded chart

With Sheets(“Sheet1”).ChartObjects(1).Chart.SeriesCollection(1)

‘a chart series will have data labels only if the HasDataLabels property (of the Series object) is True

If .HasDataLabels = True Then

MsgBox “No of data labels in series is ” & .DataLabels.count

‘use the Format property of the DataLabels object to return a ChartFormat object which contains the line, fill & effect formatting for the data labels collection

With .DataLabels.Format.Fill

‘set white background for all data labels

.ForeColor.RGB = RGB(255, 255, 255)

End With

‘use the Border property of the DataLabels object to return a Border object for the data labels collection

With .DataLabels.Border

‘set linestyle as dotted line

.LineStyle = xlDot

‘set the weight / thickness

.Weight = xlThin

‘set line color to red

.Color = vbRed

End With

‘Count property returns the number of data labels in a series

For i = 1 To .DataLabels.count

‘set the format code for data labels, using a String value

.DataLabels(i).NumberFormat = “0.0

‘use the Font property of the DataLabel object to return a Font object for a data label

With .DataLabels(i).Font

.Bold = True

‘set different font color for each data label

.ColorIndex = 2 + i

‘set increasing font size, subject to maximum of 10 points

.Size = WorksheetFunction.Min(10, 7 + i)

End With

‘return caption of each data label

MsgBox .DataLabels(i).Caption

Next i

Else

‘if series does not have data labels

MsgBox “No Data Labels in Series”

End If

End With

End Sub

Some Common Properties & Methods for DataLabels Object / DataLabel Object:

 

Property / Method
Syntax Description
Format Property objDataLabels.Format / objDataLabel.Format Returns a ChartFormat object which contains the line, fill & effect formatting for data labels.
HorizontalAlignment Property objDataLabels.HorizontalAlignment / objDataLabel.HorizontalAlignment Sets or returns the horizontal alignment for data labels as per constants – xlLeft, xlRight, xlCenter, xlJustify, xlDistributed.
VerticalAlignment Property  objDataLabels.VerticalAlignment / objDataLabel.VerticalAlignment Sets or returns the vertical alignment for data labels as per constants – xlBottom, xlTop, xlCenter, xlJustify, xlDistributed
AutoScaleFont Property  objDataLabels.AutoScaleFont / objDataLabel.AutoScaleFont  Set (or returns) True (default) where the text font size of data labels will change whenever the chart size changes. False setting will keep the font at the same size even when there is a change in the parent chart size. 
AutoText Property  objDataLabels.AutoText / objDataLabel.AutoText  Set to True for series to automatically generate appropriate caption or text of data labels, based on context. Set this property to False to turn off Auto Text. This property is Read-Write. 
NumberFormat Property  objDataLabels.NumberFormat / objDataLabel.NumberFormat  Sets or returns the format code (similar to the Format Codes option in the Format Cells dialog box) using a String value 
NumberFormatLocal Property  objDataLabels.NumberFormatLocal / objDataLabel.NumberFormatLocal  Sets or returns the format code as a String in the language of the user. 
DataLabels.NumberFormatLinked  objDataLabels.NumberFormatLinked / objDataLabel.NumberFormatLinked  Boolean, read-write: To link the number format to the cells so that the number format in the labels changes when changes are made in the cells, use the True setting. 
Orientation Property  objDataLabels.Orientation / objDataLabel.Orientation  Sets or returns the text orientation as an integer value from -90 to 90 degrees or as per the constants xlHorizontal, xlVertical, xlDownward, xlUpward
Position Property  objDataLabels.Position / objDataLabel.Position  Sets or returns the data label position – xlLabelPositionAbove (value 0, Data label above the data point); xlLabelPositionBelow (value 1, Data label below the data point); xlLabelPositionOutsideEnd (value 2, Data label outside the data point at the top edge); xlLabelPositionInsideEnd (value 3, Data label inside the data point at the top edge); xlLabelPositionInsideBase (value 4, Data label inside the data point at the bottom edge); xlLabelPositionBestFit (value 5, Data label positioned by Microsoft Excel); xlLabelPositionMixed (value 6, Data labels in multiple positions); xlLabelPositionCustom (value 7, Custom position for Data label); xlLabelPositionCenter (value -4108, Data label is centered on the data point or is inside a bar or pie chart); xlLabelPositionLeft (value -4131, Data label to the left of the data point); xlLabelPositionRight (value -4152, Data label to the right of the data point) . 
ReadingOrder Property  objDataLabels.ReadingOrder / objDataLabel.ReadingOrder  Sets or returns the reading order per the constants – xlRTL (right-to-left), xlLTR (left-to-right), or xlContext
Separator Property  objDataLabels.Separator / objDataLabel.Separator  Sets or returns the separator used for the data labels. Using a string will set that string as the separator; using the constant xlDataLabelSeparatorDefault (= 1) will set the default data label separator, which could be a comma or a newline, depending on the data label. When the property returns 1,  it means that the default separator of comma “,” has not been changed, & setting the property to a value of 1 will change back to the default separator. To use this property, the chart must be active, else you will get a run-time error. 
Shadow Property  objDataLabels.Shadow / objDataLabel.Shadow  Sets or returns a Boolean value determining if the data labels have a shadow or not . Use True to add a shadow. 
ShowBubbleSize Property  objDataLabels.ShowBubbleSize / objDataLabel.ShowBubbleSize  Boolean, read-write: True setting displays the bubble size for the data labels. To use this property, the chart must be active, else you will get a run-time error. 
ShowCategoryName Property  objDataLabels.ShowCategoryName / objDataLabel.ShowCategoryName  Boolean, read-write: True setting displays the category name for the data labels. To use this property, the chart must be active, else you will get a run-time error. 
ShowLegendKey Property  objDataLabels.ShowLegendKey / objDataLabel.ShowLegendKey  Boolean, read-write: True setting displays the legend key for the data labels.  
ShowPercentage Property  objDataLabels.ShowPercentage / objDataLabel.ShowPercentage  Boolean, read-write: True setting displays the percentage value for the data labels. To use this property, the chart must be active, else you will get a run-time error. 
ShowSeriesName Property  objDataLabels.ShowSeriesName / objDataLabel.ShowSeriesName  Boolean, read-write: True setting enables the series name to be shown for the data labels. To use this property, the chart must be active, else you will get a run-time error. 
ShowValue Property  objDataLabels.ShowValue / objDataLabel.ShowValue  Boolean, read-write: True setting enables the value to be displayed for the data labels. To use this property, the chart must be active, else you will get a run-time error. 
 
Delete Method objDataLabels.Delete Deletes the DataLabels collection
Select Method objDataLabels.Select Selects the data labels
 
Some Properties of the DataLabel object ONLY:

 

Caption Property objDataLabel.Caption Sets or returns the text for a data label, as a string value.
Text Property objDataLabel.Text Sets or returns the text for a data label, as a string value.
Characters Property objDataLabel.Characters(Start, Length) Use the Characters property which returns a Characters object representing a range of characters within the text string of data label, and this will enable formatting these specific characters. Both arguments are optional – the Start argument specifies the first character for the range of characters where 1 is the default value which means that omitting the argument will start the range of characters returned from the first character. The Length argument specifies the number of characters & omitting it will return all characters after the character specified in the Start argument.
Left Property objDataLabel.Left Sets or returns the distance, in points, from the left edge of the chart area to the left edge of the data label.
Top Property objDataLabel.Top Sets or returns the distance, in points, from the top edge of the chart area to the top edge of the data label.

Child Objects for DataLabels Object / DataLabel Object: Above, we have discussed the ChartFormat object – use the Format PropertyobjDataLabels.Format – to return the ChartFormat object which contains the line, fill & effect formatting for the chart DataLabels / DataLabel objects. Some other child objects which are often used with both DataLabels / DataLabel Objects include: ChartFillFormat Object – use the Fill PropertyobjDataLabels.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements; Interior Object – use the Interior property objDataLabels.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border PropertyobjDataLabels.Border – to return a Border object, to manipulate a chart element’s border; Font Object – use the Font PropertyobjDataLabels.Font – to return a Font object, to manipulate the font attributes viz. font name, font size, font color, … For the DataLabel object, there is an additional child object as discussed above – the Characters object – use the Characters propertyobjDataLabel.Characters(Start, Length) – to return a Characters object representing a range of characters within the text string of data label, and this will enable formatting these specific characters.

Example: Apply & manipulate data labels in a chart – refer image 9

To download Excel file with live code, click here.

Image 9

Sub Series_DataLabels()
‘apply & manipulate data labels in a chart – refer image 9

Dim i As Integer

‘refer embedded chart

With Sheets(“Sheet1”).ChartObjects(1).Chart

‘refer series “Avg” of embedded chart

With .SeriesCollection(“Avg”)

.ApplyDataLabels ShowCategoryName:=False, AutoText:=True, LegendKey:=False

With .DataLabels

.AutoScaleFont = True

.Font.Size = 10

.Font.Italic = True

‘use ColorIndex 3 for red, 5 for blue, 4 for green, 6 for yellow, …

.Font.ColorIndex = 3

.ShowSeriesName = True

.Position = xlLabelPositionRight

.Orientation = xlHorizontal

.Separator = xlDataLabelSeparatorDefault

End With

End With

‘refer series “Total” of embedded chart

With .SeriesCollection(“Total”)

.ApplyDataLabels ShowCategoryName:=True, LegendKey:=False

With .DataLabels

.ShowValue = False

.ShowSeriesName = True

.AutoScaleFont = False

.Font.Name = “Arial

.Font.Size = 9

.Font.Bold = True

.Font.Color = RGB(0, 32, 96)

.Position = xlLabelPositionAbove

.Orientation = 10

.Separator = “ /

End With

End With

‘refer series “Physics” of embedded chart

With .SeriesCollection(“Physics”)

.ApplyDataLabels

With .DataLabels

.AutoScaleFont = False

.Font.Name = “Calibri

.Font.Size = 9

.Font.Bold = True

.Font.Color = RGB(0, 176, 240)

.Position = xlLabelPositionAbove

.ShowValue = True

.Orientation = -7

.ShowCategoryName = True

.ShowSeriesName = True

.Separator = “

End With

‘set below position the data point, for every odd data label in series

For i = 1 To .DataLabels.count

If i Mod 2 <> 0 Then

.DataLabels(i).Position = xlLabelPositionBelow

End If

Next i

End With

End With

End Sub

Chart Legend

The Legend Property of the Chart object returns a Legend object, which refers to the chart legend and can be manipulated through its properties & methods. Syntax: objChart.Legend. The Legend is visible and the Legend object’s properties & methods can be used only if the HasLegend property (of the Chart object) is True. The HasLegend property uses a Boolean value – True displays and turns on a legend for the chart (Read-write) – Syntax: objChart.HasLegend.

There can be only one Legend in a chart, and multiple LegendEntry objects can be contained in the Legend object. The LegendEntries Object is a collection of LegendEntries containing all LegendEntry objects in a Legend. The LegendEntries Method of the Legend object is used to return a single legend entry (LegendEntry object) or a collection of legend entries (LegendEntries object) for the legend – Syntax: objLegend.LegendEntries(Index). To return a single LegendEntry object use the Index argument, where the index is the number of the legend entry’s position in the legend starting from top ie. LegendEntries(1), to bottom ie. LegendEntries(LegendEntries.Count). A Legend entry cannot be returned by name.

A Legend Entry has 2 parts – text & entry marker. The text part refers to the series (or trendline) name which is associated with the legend entry. The legend entry is visually linked with its associated series or trendline, by the entry marker which can be formatted using the LegendKey object (using the LegendKey object will format both the entry marker & its associated series or trendline). To restore a legend entry after its deletion, you will need to remove and recreate the legend (wherein the legend entry was contained) by first setting the HasLegend property of the chart to False and then setting it back to True. The Text part cannot be changed, font can be formatted but not the pattern, and you cannot return the legend entry’s associated series or trendline.

The LegendKey object controls the appearance of the series or trendline associated with a legend entry. The LegendKey Object refers to the legend key in a Legend which is a graphic visually linking a legend entry with its associated series or trendline so that any formatting change in one automatically changes the other’s formatting. In this manner the LegendKey object contains the formatting properties for the entry marker and its associated series or trendline. The LegendKey object is returned by using the LegendKey property of the Legend Entry, & a LegendKey object is contained in each LegendEntry object.

Commonly used Properties of the Legend object:

 

Property Syntax Description
AutoScaleFont Property objLegend.AutoScaleFont Set (or returns) True (default) where the legend’s text font size will change whenever the chart size changes. False setting will keep the legend font at the same size even when there is a change in the parent chart size.
Format Property objLegend.Format Returns a ChartFormat object which contains the line, fill & effect formatting for a chart element. The ChartFormat object provides access to the new Office Art formatting options that are available in Excel 2007 so that by using this object you can apply many new graphics to chart elements using vba formatting.
Height Property objLegend.Height Sets or returns the height, in points, of the legend.
    ‘to set height of legend to 105 points
Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Height = 105
Width Property objLegend.Width Sets or returns the width, in points, of the legend.
    ‘to set width of legend to 78 points
Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Width = 78
Left Property objLegend.Left Sets or returns the distance, in points, from the legend’s left edge to the left edge of the chart area
    ‘to return distance from legend’s left edge to chart’s left edge
Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Left = 290
Top Property objLegend.Top Sets or returns the distance, in points, from the legend’s top edge to the top edge of the chart area.
    ‘to return distance from legend’s top edge to chart’s top edge
Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Top
Name Property objLegend.Name returns the name of the Legend object, as a string value.
Parent Property objLegend.Parent returns the parent object for the Legend viz. returns the chart name:

MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Parent.Name

Shadow Property objLegend.Shadow Sets or returns a Boolean value determining if the chart legend has a shadow or not. Use True to add a shadow:

Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Shadow = True

Position Property objLegend.Position Sets or returns the position for the legend on the chart, as per values defined in XlLegendPosition Enumeration: xlLegendPositionCorner (value 2) – upper right-hand corner of the chart border; xlLegendPositionBottom (value -4107) – below the chart; xlLegendPositionLeft (value -4131) – left of the chart; xlLegendPositionRight (value -4152) – right of the chart; xlLegendPositionTop (value -4160) – above the chart; xlLegendPositionCustom (value -4161) – custom position.
    ‘displays the legend above the chart at the top & returns the value -4160

Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Position = xlLegendPositionTop

MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Position

IncludeInLayout  Property
objLegend.IncludeInLayout
This property (read-write) uses a Boolean value – True (default) value will have the legend occupy the chart layout space when chart layout is being determined. In Excel, when you “Format Legend”, In the legend properties window there is a checkbox “Show the legend without overlapping the chart” – this check box is selected when the property returns “True”, & you can deslect the check box (sets property to False) for the legend to cover or overlap the chart or plot area if you need more space on your chart.

 

Example: Manipulate legend font (font for all legend entries) – use the Font property of the Legend object to return the Font object

Sub legend_Font()
‘manipulate legend font (font for all legend entries) – use the Font property of the Legend object to return the Font object

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Font

.Name = “Arial

.Italic = True

.Size = 9

.Color = RGB(0, 0, 255)

.Strikethrough = False

.Superscript = False

.Subscript = False

.Background = xlAutomatic

.Underline = xlUnderlineStyleSingle

End With

End Sub

Example: format shadow, border & fill for chart Legend – refer Image 1

Image 1

Sub legend_Format()
‘format shadow, border & fill for chart legend – refer Image 1

‘remove shadow, border & fill for chart legend – set fill / interior color – remove fill

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend

‘remove shadow

.Shadow = False

‘remove border

.Format.Line.Visible = False

‘remove fill

.Format.Fill.Visible = msoFalse

‘sets fill / interior color

.Interior.Color = RGB(230, 185, 184)

‘changes fill / interior color to green

.Interior.Color = vbGreen

‘.Interior.ColorIndex = 4

‘remove fill

.Format.Fill.Visible = msoFalse

End With

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend

‘turn on the border for the chart area

.Format.Line.Visible = True

‘using the Weight Property of the LineFormat object, to set the weight of the border

.Format.Line.Weight = 1.25

‘use the ColorIndex Property of the Border object to set the border color: to set no line color use ColorIndex = xlNone, or use ColorIndex 3 for red, 5 for blue, 4 for green, 6 for yellow, and so on.

.Border.ColorIndex = 3

.Border.LineStyle = xlDot

.Shadow = True

End With

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Format.Shadow

.Visible = msoTrue

‘specify the type of shadow displayed as second shadow type

‘.Type = msoShadow2

‘set the style as outer shadow effect

.Style = msoShadowStyleOuterShadow

‘set blurr degree of the shadow

.Blur = 4

‘do not rotate the shadow when rotating the shape

.RotateWithShape = msoFalse

‘set the fill color

.ForeColor.RGB = RGB(255, 0, 0)

‘set the horizontal offset of the shadow from the legend, in points – a positive value offsets the shadow to the right while a negative value offsets it to the left

.OffsetX = 2

‘set the vertical offset of the shadow from the legend, in points – a positive value offsets the shadow to the right while a negative value offsets it to the left

.OffsetY = 4

‘set the degree of transparency as a value from 0.0 (opaque) through 1.0 (clear)

.Transparency = 0.4

‘set the size of the shadow

.Size = 100

End With

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Format.Fill

‘set the fill to be visible

.Visible = msoTrue

‘set the fill color that is mapped to the theme color scheme – specify the Accent 2 theme color

.ForeColor.ObjectThemeColor = msoThemeColorAccent2

‘set a value to lighten or darken a color –  a number from -1 (darkest) to 1 (lightest) where Zero (0) is neutral

.ForeColor.TintAndShade = 0.65

‘specify fill to a two-color gradient – gradient running vertically down the shape – and specify gradient variant as 2

.TwoColorGradient msoGradientVertical, 2

End With

End Sub

Commonly used Methods of the Legend object:

 

Method Syntax Description
Delete Method objLegend.Delete deletes the Legend –

Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.Delete

Select Method objLegend.Select selects the Legend
Clear Method objLegend.Clear clears the Legend
LegendEntries Method objLegend.LegendEntries(Index) The LegendEntries Method of the Legend object is used to return a single legend entry (LegendEntry object) or a collection of legend entries (LegendEntries object) for the legend. To return a single LegendEntry object use the Index argument, where the index is the number of the legend entry’s position in the legend starting from top ie. LegendEntries(1), to bottom ie. LegendEntries(LegendEntries.Count). A Legend entry cannot be returned by name. 
 

‘activate worksheet
Sheets(“Sheet1”).Activate
‘use the Select Method of the Legend object to select the legend
ActiveSheet.ChartObjects(1).Chart.Legend.Select

‘using the LegendEntries Method of the Legend object to return a single legend entry
With Selection.LegendEntries(2)

‘using Font property of the LegendEntry object, returns a Font object that represents the font
.Font.Name = “Arial
.Font.Size = 9
.Font.Bold = True
.Font.Italic = True
‘return width, height, top & left distances for the LegendEntry
MsgBox .Width
MsgBox .Height
MsgBox .Top
MsgBox .Left

End With

   

 

Dim objLE As LegendEntry, i As Integer

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend

‘set font of each legend entry to italic
For Each objLE In .LegendEntries

objLE.Font.Italic = True

Next

‘set font of even legend entries to bold
For i = 1 To .LegendEntries.count

If i Mod 2 = 0 Then

.LegendEntries(i).Font.Bold = True

End If

Next i

End With

   

Use the LegendKey Property of the LegendEntry object to return a LegendKey object which represents the legend entry’s associated legend key. The legend key is a graphic visually linking a legend entry with its associated series or trendline so that any formatting change in one automatically changes the other’s formatting. In this manner the LegendKey object contains the formatting properties for the entry marker and its associated series or trendline. This legend key, besides having the the ChartFormat object (returned by its Format property: objLegendKey.Format) which contains the line, fill & effect formatting, has its Interior, Border & ChartFillFormat child objects.

Sub LegendKey_1()
‘format legend key, series markers / points & series line
‘use the legend key’s Interior / Fill / Border / Format properties to return Interior Object / ChartFillFormat Object / Border Object / ChartFormat object respectively

‘for markers / points of legend entry 1

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.LegendEntries(1).LegendKey

With .Interior

‘set the interior pattern for markers / points to automatic – let Excel control it

.Pattern = xlPatternAutomatic

‘set the interior pattern to Checkerboard

.Pattern = xlChecker

‘set the color of the interior pattern as an RGB value, to yellow.

.PatternColor = RGB(255, 255, 0)

‘set the color of the interior pattern as an index into the current color palette, to yellow

‘.PatternColorIndex = 6

End With

End With

‘for markers / points of legend entry 2

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.LegendEntries(2).LegendKey

With .Fill

‘remove fill color from legend key & all points in the associated series

.Visible = msoFalse

‘set a solid fill of uniform color

.Solid

‘set the interior color to Yellow

.ForeColor.SchemeColor = 6

End With

End With

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.LegendEntries(3).LegendKey

‘sets the marker background / fill color, as an index, for the legend entry & all points in the associated series (which must support data markers).

.MarkerBackgroundColorIndex = 5

‘set the marker foreground (border line) color as an RGB value for all points in the associated series

.MarkerForegroundColor = RGB(255, 255, 0)

‘sets the data-marker size, in points

.MarkerSize = 8

‘set the marker style as Triangular markers

.MarkerStyle = xlMarkerStyleTriangle

‘this sets the Marker weight (marker’s border line width) and also sets the series line thickness

.Format.Line.Weight = 2

End With

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.LegendEntries(4).LegendKey

With .Format.Fill

‘remove marker fill color from legend key & all points in the associated series

.Visible = msoFalse

‘sets the marker fill color for the legend entry & all points in the associated series (which must support data markers).

.ForeColor.RGB = RGB(255, 0, 0)

End With

End With

With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend.LegendEntries(5).LegendKey

‘legend key’s border property returns the series line

With .Border

‘set series linestyle as continuous line

.LineStyle = xlContinuous

‘set series linestyle as dotted line

.LineStyle = xlDot

‘set the weight / thickness

.Weight = xlMedium

‘set the line color to red

.Color = vbRed

End With

End With

End Sub

Child Objects for the Legend Object: Above we have discussed: the ChartFormat object – use the Format PropertyobjLegend.Format – to return the ChartFormat object which contains the line, fill & effect formatting for the chart legend. Some other child objects which are often used with the Legend Object include: ChartFillFormat Object – use the Fill PropertyobjLegend.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements; Interior Object – use the Interior propertyobjLegend.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border PropertyobjLegend.Border – to return a Border object, to manipulate a chart element’s border; Font Object – use the Font PropertyobjLegend.Font – to return a Font object, to manipulate the font attributes viz. font name, font size, font color, … The difference between ChartFillFormat object & the Interior object may be somewhat blurred, but seemingly the ChartFillFormat object emphasises gradient & texture fills, whereas the Interior object emphasises solid fills & pattern fills (though ChartFillFormat object also has a Pattern property).

Example: Part 8 of 8 – Manipulate Chart Legend – refer Image 1.8

To download Excel file with live code, click here.

Image 1.8

Sub EmbChart_Legend_8()
‘manipulate Chart Legend – refer Image 1.8

Dim wsChart As Worksheet

‘declare a ChartObject object

Dim oChObj As ChartObject

Set wsChart = Sheets(“Sheet19”)

‘set ChartObject object by name

Set oChObj = wsChart.ChartObjects(“QtrlySalesProfitChart”)

‘using the Chart Property of the ChartObject object returns a Chart object which refers to a chart

With oChObj.Chart

‘use the Legend Property of the Chart object to return the Legend object, which represents the chart Legend

With .Legend

‘use the Font property of the Legend object to return the Font object – set font for the Legend (all legend entries) to bold

.Font.Bold = True

‘set font for the Legend (all legend entries) to italic

.Font.Italic = True

the LegendEntry object represents a legend entry in a chart legend, which is a member of the LegendEntries collection

‘use the LegendEntries method of the Legend object to return the LegendEntries Object (LegendEntries collection) which is a collection of all the LegendEntry objects in a chart legend

‘set font color of the legend entry 1 (index number represents the legend entry’s position in the legend), which is at the top of the legend, to red

.LegendEntries(1).Font.ColorIndex = 3

‘set font color of the legend entry 2

.LegendEntries(2).Font.Color = RGB(149, 55, 53)

‘set font color of the legend entry 3 to blue

.LegendEntries(3).Font.ColorIndex = 5

‘use the LegendKey property (of the LegendEntry object) to return the LegendKey object which refers to the legend key in a Legend which is a graphic visually linking a legend entry with its associated series or trendline so that any

formatting change in one automatically changes the other’s formatting

‘using the MarkerStyle Property of the LegendKey object to set the marker style for a point or marker in the line chart – use the XlMarkerStyle Enumeration of xlMarkerStyleTriangle for Triangular markers

.LegendEntries(2).LegendKey.MarkerStyle = xlMarkerStyleTriangle

‘set the data-marker size, in points, using the MarkerSize Property of the LegendKey object

.LegendEntries(2).LegendKey.MarkerSize = 8

‘set the marker foreground (marker border line) color as an RGB value for all points in the associated series – set color to black

.LegendEntries(2).LegendKey.MarkerForegroundColor = RGB(0, 0, 0)

End With

End With

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top