Chart Elements in Excel VBA (Part 2) – Chart Title, Chart Area, Plot Area, Chart Axes, Chart Series, Data Labels, Chart Legend
Contents:
DataLabels Object / DataLabel Object
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.
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 series – Syntax: 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 With ws.ChartObjects(1).Chart With .SeriesCollection(1) .Name = ws.Range(“B1”) End With 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 Enumeration – xlMarkerStyleSquare (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. |
‘chart series without markers where Smooth is False (default) – refer Image 1a
 ‘chart series without markers where Smooth is True – refer Image 1b
|
||
| 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). |
Sub Series_Line_Markers() ’embedded chart – refer series 1 ‘using the Line Property of the ChartFormat object, returns a LineFormat object that contains line formatting properties End With ‘refer series 2 ‘sets the fill color for all markers in the series using RGB values (MarkerBackgroundColor property of Series object), to red ‘use the MarkerStyle Property of the Point object set the marker style for a point Next ‘use the Points Method of the Series object to return a single point (Point object) – refer point no 3 ‘use the MarkerStyle Property of the Point object set the marker style for a point 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 Enumeration – xlStretch (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. |
Sub Series_Picture() ‘for the Plot Area Fill, ie. FillFormat object ‘use the UserPicture Method of the FillFormat object to set an image to the specified fill ‘use the PictureType Property to set the picture display format on a column chart ‘set PictureType Property for point 2 to xlStack (picture is sized to repeat a maximum of 15 times) ‘set point 3 to xlStackScale – picture is sized to a specified no. of units & repeated the length of the bar 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. |
Sub Series_Formulas_1() Dim ws As Worksheet, srs As Series With oChObj.Chart .ChartType = xlLineMarkers End With End Sub |
||
Sub Series_Formulas_2() Dim oChObj As ChartObject, srs As Series, strOld As String, strNew As String With oChObj.Chart Set srs = .SeriesCollection(1) ‘using the vba Replace function to replace part formula – refer Image 4b: ‘using the worksheet Substitute function to change or replace the whole series formula – refer Image 4b: 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) ‘use the MarkerStyle Property of the Point object set the marker style for a point Next ‘use the Points Method of the Series object to return a single point (Point object) |
||
| 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 ‘set the HasDataLabels Property to True to turn on data labels for a series ‘using the DataLabels Method & the Index argument to return a single data label in a series ‘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 ‘using the DataLabels Method to return all data labels in a series 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
Sub Series_Trendlines_1() ‘add 3 trendlines for series 1 (Profit) ‘delete existing trendlines – deleting a single trendline will reindex the collection, hence trendlines should be deleted in reverse order .Trendlines(i).Delete Next i ‘the Type argument specifies the type of trendline to add With .Trendlines(1) .Format.Line.Weight = 2 End With With .Trendlines(2) .Type = xlMovingAvg End With With .Trendlines(3) .Order = 4 End With End With ‘add 1 trendline for series 2 (Advt Exp) ‘delete existing trendlines – deleting a single trendline will reindex the collection, hence trendlines should be deleted in reverse order .Trendlines(i).Delete Next i ‘set trendline color to dark red 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)” 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 Property – objSeries.Format – to return the ChartFormat object which contains the line, fill & effect formatting for the chart series; ErrorBars object – use the ErrorBars Property – objSeries.ErrorBars – to turn on error bars for a series which indicate the degree of uncertainty for chart data; LeaderLines object – use the LeaderLines property – objSeries.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 Property – objSeries.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements; Interior Object – use the Interior property – objSeries.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border Property – objSeries.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.
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
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
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
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 Property – objDataLabels.Count – returns the number of data labels in a collection / series. The DataLabels.Item Method – objDataLabels.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 Property – objDataLabels.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 Property – objDataLabels.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 Property– objDataLabels.Border – to return a Border object, to manipulate a chart element’s border; Font Object – use the Font Property – objDataLabels.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 property – objDataLabel.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.
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
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
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 ‘using the LegendEntries Method of the Legend object to return a single legend entry ‘using Font property of the LegendEntry object, returns a Font object that represents the font End With
|
||
|
Dim objLE As LegendEntry, i As Integer With Sheets(“Sheet1”).ChartObjects(1).Chart.Legend ‘set font of each legend entry to italic objLE.Font.Italic = True Next ‘set font of even legend entries to bold 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 Property – objLegend.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 Property – objLegend.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements; Interior Object – use the Interior property – objLegend.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border Property – objLegend.Border – to return a Border object, to manipulate a chart element’s border; Font Object – use the Font Property – objLegend.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.
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