Create Charts in Excel VBA: Embedded Charts – Line with Markers; Clustered Stacked Column Chart; Clustered Stacked Bar Chart displaying Variance; Pie chart; XY Scatter chart & Bubble chart
Contents:
Create an Embedded Chart – Line with Markers
Creating a Clustered Stacked Column Chart
Create a Clustered Stacked Bar Chart displaying Variance between Series
XY Scatter Chart & Bubble Chart
Excel is a great tool to create charts quickly & easily, to display worksheet data in a meaningful manner for users. This section illustrates creating & manipulating charts with vba code, in excel. You have a variety of chart types in Excel, such as Column, Bar, Line, Stacked (column, bar, line, …), Pie, XYScatter & Bubble charts which are the most common. Using VBA, you can manipulate embedded charts (which are embedded or placed within a worksheet & and can be displayed alongwith their source data or other information) or a chart sheet (a separate sheet in the workbook which contains only a chart and does not display the source data or any other information). Our emphasis is more on embedded charts in this section.
Column & Bar charts (clustered or otherwise) are useful for comparison between items & to display data changes over a period of time at specified time intervals. Line charts are useful for comparison between items & to display continuous data over time & display data trends at equal time intervals. Stacked charts (column, bar, line, etc) compare an individual item’s contribution to the whole, across categories or time intervals. A Pie chart is particularly useful where relative percentages are required to be displayed. An XYScatter chart is particularly useful for comparing numerical data wherein values are not spaced evenly at regular intervals and you want to compare and show relationships by grouping sets of values by disregarding the time factor or interval. Bubble charts are similar to XY Scatter charts, except that bubble charts compare 3 sets of values instead of two sets of values compared in scatter charts, where the third value determines the size of the marker where the 2 values intersect in scatter charts.
Create an Embedded Chart – Line with Markers
Below is an illustration for creating an embedded chart, of type line with markers, and manipulating various chart elements – it is divided into 8 parts / subs, for clear step by step explanations. Sub 1: add an embedded chart; Sub 2: manipulate Chart Title; Sub 3: manipulate Chart Area; Sub 4: manipulate Chart Axis & Axis Title; Sub 5: manipulate Chart Series; Sub 6: manipulate Plot Area; Sub 7: align chart elements; Sub 8: manipulate Chart Legend.
To download Excel file with live code, click here.
Example: Part 1 of 8 – Add an embedded chart – refer Image 1.1
Sub EmbChart_ChartObject_1()
‘Add an embedded chart – refer Image 1.1
Dim rngSourceData As Range, wsData As Worksheet, wsChart As Worksheet
Set wsData = Sheets(“Sheet18”)
Set wsChart = Sheets(“Sheet19”)
Set rngSourceData = wsData.Range(“C1:D9”)
‘declare a ChartObject
Dim oChObj As ChartObject
‘delete existing embedded charts in the worksheet
For Each oChObj In wsChart.ChartObjects
oChObj.Delete
Next
‘the Add method (of the ChartObjects object) is used to create a new empty embedded chart and add it to the collection (ChartObjects object) in the specified sheet – left edge align to the left edge of column B, top edge align to the top of row 2
Set oChObj = wsChart.ChartObjects.Add(Left:=wsChart.Columns(“B“).Left, Width:=450, Top:=wsChart.Rows(2).Top, Height:=255)
‘using the Chart Property of the ChartObject object returns a Chart object which refers to a chart (contained in the ChartObject object)
With oChObj.Chart
‘use ChartType Property of the Chart object to set type of chart – Line with Markers
.ChartType = xlLineMarkers
‘use SetSourceData Method of the Chart object to set the range of source data for the chart
.SetSourceData Source:=rngSourceData, PlotBy:=xlColumns
‘the ChartTitle object exists and can be used only if the HasTitle property (of the Chart object) is True
.HasTitle = True
‘using the Chart.SeriesCollection Method to return a single series (Series object) by its name or Index number
‘the AxisGroup Property of the Series object sets the axis group for series – using the XlAxisGroup Enumeration to specify the type of axis group as xlPrimary (Primary axis group)
.SeriesCollection(wsData.Range(“C1”).Value).AxisGroup = xlPrimary
‘specfy the type of axis group as xlSecondary (Secondary axis group) for series
.SeriesCollection(wsData.Range(“D1”).Value).AxisGroup = xlSecondary
‘refer ChartObject object (oChObj)
With .Parent
‘set the embedded chart to be free-floating so that it does not move or size with its underlying cells
.Placement = xlFreeFloating
‘set rounded corners for the embedded chart
.RoundedCorners = True
‘Change the name of the embedded chart to “QtrlySalesProfitChart” using the Name Property
.Name = “QtrlySalesProfitChart”
End With
End With
End Sub
Example: Part 2 of 8 – Manipulate Chart Title – refer Image 1.2
Sub EmbChart_ChartTitle_2()
‘manipulate Chart Title – refer Image 1.2
Dim wsChart As Worksheet
‘declare a ChartObject object
Dim oChObj As ChartObject
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
‘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 = “Sales-Profit-Mktg Exp“
‘set the font to Bold Arial 12 point
.Font.Name = “Arial“
.Font.Size = 12
.Font.Bold = True
‘dark blue
.Font.Color = RGB(0, 30, 90)
‘Format property of the ChartTitle returns returns the ChartFormat object – using the Fill Property of the ChartFormat object to return a FillFormat object (which contains fill formatting properties), to set the foreground fill color &
gradient for the ChartTitle
.Format.Fill.ForeColor.RGB = RGB(228, 109, 10)
‘the OneColorGradient Method of the FillFormat object, sets a one-color gradient for the specified fill – specifying the style for a gradient fill with the MsoGradientStyle Enumeration ‘msoGradientHorizontal’ for a Gradient running
horizontally across the shape.
‘gradient variant of 2 & gradient degree of 1 (lightest)
.Format.Fill.OneColorGradient msoGradientHorizontal, 2, 1
‘use the Border property to return a Border object – use the LineStyle Property of the Border object to set a line style for the border – use the XlLineStyle Enumeration of xlDot to set a dotted line
.Border.LineStyle = xlDot
‘use the Weight Property of the Border object to set the border weight – use the XlBorderWeight Enumeration of xlThin for specifying the weight of the border as Thin
.Border.Weight = xlThin
‘set border color with Color Property of the Border object
.Border.Color = RGB(0, 30, 90)
End With
End With
End Sub
Example: Part 3 of 8 – Manipulate Chart Area – refer Image 1.3
Sub EmbChart_ChartArea_3()
‘manipulate Chart Area – refer Image 1.3
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 returns a Chart object which refers to a chart
With oChObj.Chart
‘using the ChartArea Property of the Chart object returns the ChartArea object which represents the chart area
With .ChartArea
‘the Format Property of the ChartArea object returns the ChartFormat object, and the Line Property of the ChartFormat object returns a LineFormat object that contains line formatting properties for the specified Chart Area – for a
chart with a border, the LineFormat object represents the border
‘turn on the border for the chart area
.Format.Line.Visible = True
‘using the Weight Property of the LineFormat object, to set the weight for the border
.Format.Line.Weight = 2
‘using the Fill Property of the ChartFormat object returns a FillFormat object, that contains fill formatting properties for the for the ChartArea object. Then use the ForeColor Property to return a ColorFormat object to specify the
foreground fill or solid color to green
.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
‘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
‘use the LineStyle Property of the Border object to set a line style for the Border – continuous line
.Border.LineStyle = xlContinuous
End With
End With
End Sub
Example: Part 4 of 8 – Manipulate Chart Axis & Axis Title – refer Image 1.4
Sub EmbChart_ChartAxis_AxisTitle_4()
‘manipulate Chart Axis & Axis Title – refer Image 1.4
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
‘————————————–
‘VERTICAL Y-AXIS, PRIMARY:
‘refer the Axis object – Value axis in the Primary axis group – Axis object is a member of the Axes collection
With .Axes(xlValue, xlPrimary)
‘set the minimum and maximum values for the value axis
.MaximumScale = WorksheetFunction.RoundUp(WorksheetFunction.Max(wsData.Range(“C2:C9”)) * 1, -5)
.MinimumScale = WorksheetFunction.RoundDown(WorksheetFunction.Min(wsData.Range(“E2:E9”)) * 1, -5)
‘set the major units, which is the interval between tick marks, for the value axis
.MajorUnit = 100000
‘set a numeric scale used for axis values – using unit labels for charting large values will make tick mark labels more readable by using a single unit label smaller numeric values will be used instead of large numbers at the tick marks
‘using the DisplayUnit Property of the Axis object, to set the unit label for the primary value axis – set the units displayed on the primary value axis to thousands
.DisplayUnit = xlThousands
.HasDisplayUnitLabel = True
‘using the DisplayUnitLabel property of the Axis object, to return the DisplayUnitLabel object which represents a unit label on the specified axis
‘set the label caption to “‘000s” for the primary value axis, & turn off automatic font scaling
With .DisplayUnitLabel
.Caption = “‘000s“
.AutoScaleFont = False
‘set font color to red
.Font.Color = RGB(255, 0, 0)
End With
‘the AxisTitle object exists and can be used only if the HasTitle property (of the Axis object) is True
.HasTitle = True
‘the AxisTitle Object represents an axis title for the chart
With .AxisTitle
‘set the text for axis title, set the font to Bold Arial 10 Point
.Caption = “Sales-Mktg“
.Font.Name = “Arial“
.Font.Size = 10
.Font.Bold = True
‘Format property of the ChartTitle returns returns the ChartFormat object – using the Fill Property of the ChartFormat object to return a FillFormat object (which contains fill formatting properties), to set the foreground fill color &
gradient for the axis title
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
‘the OneColorGradient Method of the FillFormat object, sets a one-color gradient for the specified fill – specifying the style for a gradient fill with the MsoGradientStyle Enumeration ‘msoGradientFromCenter’ for a Gradient running
from the center out to the corners, gradient variant of 2 & gradient degree of 1 (lightest)
.Format.Fill.OneColorGradient msoGradientFromCenter, 2, 1
‘set Shadow Property to True which determines a shadow for the AxisTitle object
.Shadow = True
End With
‘set the HasMajorGridlines property (of the Axis object) to True, to display the major gridlines for an axis (primary axis group)
.HasMajorGridlines = True
‘using the MajorGridlines Property of the Axis object returns the Gridlines object which represents the major gridlines for that axis – either all gridlines for an axis can be turned on or all of them be turned off, but not a single gridline
at a time
With .MajorGridlines
‘set gridlines color to dark red
.Border.ColorIndex = 9
‘set gridlines line style – XlLineStyle Enumeration: xlContinuous (Continuos Line), xlDash (Dashed line), …
.Border.LineStyle = xlContinuous
End With
‘the TickLabels property returns a TickLabels Object representing the tick-mark labels associated with tick marks on an axis, and with this object you can manipulate / format all tick-mark labels together as a unit (not single tick-mark
label at a time).
With .TickLabels
.Font.Bold = True
.Font.Color = RGB(255, 0, 0)
End With
End With
‘————————————–
‘VERTICAL Y-AXIS, SECONDARY:
‘refer the Axis object – Value axis in the Secondary axis group – Axis object is a member of the Axes collection
With .Axes(xlValue, xlSecondary)
‘set the minimum and maximum values for the value axis
.MaximumScale = 0.095
.MinimumScale = 0.065
‘set the major units, which is the interval between tick marks, for the value axis
.MajorUnit = 0.005
‘set the minor units, which is the interval between minor tick marks, for the value axis
.MinorUnit = 0.001
.HasTitle = True
With .AxisTitle
.Caption = “Profit“
.Font.Name = “Arial“
.Font.Size = 10
.Font.Bold = True
.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
.Format.Fill.OneColorGradient msoGradientFromCenter, 2, 1
.Shadow = True
End With
With .TickLabels
.Font.Bold = True
.Font.Color = RGB(0, 0, 255)
‘set number format for tick-mark labels
.NumberFormat = “0.0%“
End With
End With
‘————————————–
‘HORIZONTAL X-AXIS, PRIMARY:
‘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 = wsData.Range(“A2:B9”)
‘using the Offset Property of the TickLabels object to set the distance between the levels of axis labels, and the distance between the axis line & the first level – the default distance between the axis labels & the axis line is 100
percent
.TickLabels.Offset = 10
.TickLabels.Font.Bold = True
.HasTitle = True
With .AxisTitle
.Caption = “Yr-Qtr“
.Font.Name = “Arial“
.Font.Size = 10
.Font.Bold = True
‘format the word “Qtr” as italic – format characters within a text string, starting at 4th character & a length of 3 characters, representing the word “Qtr”
.Characters(4, 3).Font.Italic = True
.Format.Fill.OneColorGradient msoGradientHorizontal, 1, 1
.Shadow = False
End With
‘set the HasMinorGridlines property (of the Axis object) to True, to display the minor gridlines for an axis (primary axis group)
.HasMinorGridlines = True
‘using the MinorGridlines Property of the Axis object returns the Gridlines object which represents the minor gridlines for that axis – either all gridlines for an axis can be turned on or all of them be turned off, but not a single gridline
at a time
With .MinorGridlines
‘set gridlines color to dark red
.Border.ColorIndex = 9
‘set gridlines line style – XlLineStyle Enumeration: xlContinuous (Continuos Line), xlDash (Dashed line), …
.Border.LineStyle = xlContinuous
End With
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
‘———————————————-
‘New Series
‘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 color 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
Example: Part 6 of 8 – Manipulate Plot Area – refer Image 1.6
Sub EmbChart_PlotArea_6()
‘manipulate Plot Area – refer Image 1.6
Dim wsChart As Worksheet
‘declare a ChartObject object
Dim oChObj As ChartObject
Set wsChart = Sheets(“Sheet19”)
‘an embedded chart is active if selected or if the ChartObject object (in which it is contained) is activated using the Activate method
wsChart.ChartObjects(1).activate
‘use the ActiveChart property to refer to a chart which is the active object
With ActiveChart
‘using the PlotArea Property of the Chart object returns the PlotArea object which represents the plot area
With .PlotArea
‘for InsideLeft (or InsideWidth / InsideHeight) property, the plot area excludes the axis labels, whereas using the Left (or Width / Height) property uses the bounding rectangle which includes the axis labels.
‘set the distance from the chart edge to the left edge of the plot area, in points
.Left = 30
‘set plot area width
.Width = ActiveChart.ChartArea.Width – .Left – ActiveChart.Legend.Width – 10
‘set plot area height
.Height = ActiveChart.ChartArea.Height – 55
‘remove fill color from Plot Area
.Fill.Visible = False
‘add fill color to Plot Area – set the interior color for the plot area to Yellow
.Fill.Visible = True
.Interior.Color = vbYellow
‘alternatively:
‘.Interior.ColorIndex = 6
‘Format property of the PlotArea returns returns the ChartFormat object – using the Fill Property of the ChartFormat object to return a FillFormat object (which contains fill formatting properties), to set the foreground fill color &
gradient for the plot area
‘the OneColorGradient Method of the FillFormat object, sets a one-color gradient for the specified fill – specifying the style for a gradient fill with the MsoGradientStyle Enumeration ‘msoGradientDiagonalUp’ for a Diagonal gradient
moving from a bottom corner up to the opposite corner, gradient variant of 4 & gradient degree of 1 (lightest)
.Format.Fill.OneColorGradient msoGradientDiagonalUp, 4, 1
‘use the Border property to return a Border object – 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 = 5
‘set border width to 2 points
.Border.Weight = xlMedium
End With
End With
End Sub
Example: Part 7 of 8 – Align chart elements – display unit label, value axis title, secondary value axis title, category axis title, chart title & chart legend – refer Image 1.7
Sub EmbChart_Align_7()
‘align chart elements – display unit label, value axis title, secondary value axis title, category axis title, chart title & chart legend – refer Image 1.7
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
‘top align display unit label, of primary value axis, with the axis top
.Axes(xlValue, xlPrimary).DisplayUnitLabel.Top = .PlotArea.Top
‘left alignment of display unit label (of primary value axis)
Dim sDisplayUnitLabelWidth As Single
‘move the display unit label to the extreme right of the chart area
.Axes(xlValue, xlPrimary).DisplayUnitLabel.Left = .ChartArea.Width
‘calculate width of display unit label
sDisplayUnitLabelWidth = .ChartArea.Width – .Axes(xlValue, xlPrimary).DisplayUnitLabel.Left
‘left align display unit label next to the primary value axis
.Axes(xlValue, xlPrimary).DisplayUnitLabel.Left = .PlotArea.Left – sDisplayUnitLabelWidth
‘horizontal / left alignment of axis title, of primary value axis
Dim sPrimaryAxisTitleWidth As Single
‘move the axis title to the extreme right of the chart area
.Axes(xlValue, xlPrimary).AxisTitle.Left = .ChartArea.Width
‘calculate width of axis title
sPrimaryAxisTitleWidth = .ChartArea.Width – .Axes(xlValue, xlPrimary).AxisTitle.Left
‘align axis title – horizontally center between plot area left & chart area left edge
.Axes(xlValue, xlPrimary).AxisTitle.Left = (.PlotArea.Left – sPrimaryAxisTitleWidth) / 2
‘distance between secondary axis title left edge & plot area right edge equated with distance between primary axis title left edge & chart area left edge
.Axes(xlValue, xlSecondary).AxisTitle.Left = .PlotArea.Left + .PlotArea.Width + .Axes(xlValue, xlPrimary).AxisTitle.Left
‘vertical alignment of primary value axis title
Dim sPrimaryAxisTitleHeight As Single
‘move the axis title to the bottom edge of the chart area
.Axes(xlValue, xlPrimary).AxisTitle.Top = .ChartArea.Height
‘calculate height of axis title
sPrimaryAxisTitleHeight = .ChartArea.Height – .Axes(xlValue, xlPrimary).AxisTitle.Top
‘vertically center axis title between plot area
.Axes(xlValue, xlPrimary).AxisTitle.Top = .PlotArea.InsideTop + (.PlotArea.InsideHeight – sPrimaryAxisTitleHeight) / 2
‘vertical alignment of secondary value axis title
Dim sSecAxisTitleHeight As Single
‘move the axis title to the bottom edge of the chart area
.Axes(xlValue, xlSecondary).AxisTitle.Top = .ChartArea.Height
‘calculate height of axis title
sSecAxisTitleHeight = .ChartArea.Height – .Axes(xlValue, xlSecondary).AxisTitle.Top
‘vertically center axis title between plot area
.Axes(xlValue, xlSecondary).AxisTitle.Top = .PlotArea.InsideTop + (.PlotArea.InsideHeight – sSecAxisTitleHeight) / 2
‘vertical alignment of category primary axis title
Dim sCategoryAxisTitleHeight As Single
‘move the axis title to the bottom edge of the chart area
.Axes(xlCategory, xlPrimary).AxisTitle.Top = .ChartArea.Height
‘calculate height of axis title
sCategoryAxisTitleHeight = .ChartArea.Height – .Axes(xlCategory, xlPrimary).AxisTitle.Top
‘vertically center axis title between plot area bottom & chart area bottom
.Axes(xlCategory, xlPrimary).AxisTitle.Top = .PlotArea.Top + .PlotArea.Height + (.ChartArea.Height – .PlotArea.Top – .PlotArea.Height – sCategoryAxisTitleHeight) / 2
‘horizontally center the category primary axis title
Dim sCatAxisTitleWidth As Single
‘move the axis title to the extreme right of the chart area
.Axes(xlCategory, xlPrimary).AxisTitle.Left = .ChartArea.Width
‘calculate width of axis title
sCatAxisTitleWidth = .ChartArea.Width – .Axes(xlCategory, xlPrimary).AxisTitle.Left
‘horizontally center axis title between plot area
.Axes(xlCategory, xlPrimary).AxisTitle.Left = .PlotArea.InsideLeft + (.PlotArea.InsideWidth – sCatAxisTitleWidth) / 2
‘horizontally center the chart title, within the plot area
Dim sChartTitleWidth As Single
‘move the chart title to the extreme right of the chart area – the title will touch the right edge of the chart
.ChartTitle.Left = .ChartArea.Width
‘calculate the ChartTitle width
sChartTitleWidth = .ChartArea.Width – .ChartTitle.Left
‘horizontally center the ChartTitle, within the plot area
.ChartTitle.Left = .PlotArea.InsideLeft + (.PlotArea.InsideWidth – sChartTitleWidth) / 2
‘vertically center the chart title, in the top area
Dim sChartTitleHeight As Single
‘move the chart title to the extreme bottom of the chart area – the title will touch the bottom edge of the chart
.ChartTitle.Top = .ChartArea.Height
‘calculate the ChartTitle height
sChartTitleHeight = .ChartArea.Height – .ChartTitle.Top
‘vertically center the ChartTitle, in the top area ‘within Plot Area Top’ & ‘Chart Area Top’
.ChartTitle.Top = (.PlotArea.Top – sChartTitleHeight) / 2
‘vertical alignment of chart legend
.Legend.Position = xlLegendPositionRight
‘move the legend to the bottom edge of the chart area
.Legend.Top = .ChartArea.Height
‘raise legend 50 points from the bottom
.Legend.Top = .Legend.Top – 50
End With
End Sub
Example: Part 8 of 8 – Manipulate Chart Legend – refer 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
Creating a Clustered Stacked Column Chart
You might often feel the need to create a stacked column chart, which is also clustered where the columns are side-by-side. Excel chart types have default “Clustered column” or “Stacked column” but not both types combined in one. A Clustered Stacked Column Chart type of chart is required wherein for a common category you have multiple sets of data in adjacent columns, with each column consisting of multiple stacks.
We give an example below, where we compare “Actual Sales / Marketing Costs / Profit” with “Estimated Sales / Marketing Costs / Profit” for each year. The common category is each year for which data is compared. For each year, we have 2 sets of data – Actual & Estimated, for which we have separate columns whcih are clustered together. Each column has 3 stacks – Sales, Marketing Costs & Profit.
Original data is in the worksheet range of A1:G7. We have rearranged it in the range A9:G28, to display appropriate stacked columns spacing. Refer Image 2 & below code how this is done.
Logic for rearranging data in the range A9:G28, to display appropriate stacked columns spacing:
Each row with data (row nos 11, 12, 14, 15, 17,18, 20, 21, 23, 24, 26, 27) creates & displays a stacked column as a separate category – these are the ‘visible columns containing data’.
Each blank row (row nos 10, 13, 16, 19, 22, 25, 28) creates a separate category, with a blank column, so as to display spaces between ‘visible columns containing data’
The code – objChart.ChartGroups(1).GapWidth = 0 – deletes the space between columns / categories so that the chart displays the ‘visible columns containing data’ side-by-side (as clusters) with a space (blank column) between each cluster.
Example: Creating a Clustered Stacked Column Chart – refer Image 2
To download Excel file with live code, click here.
Sub Chart_ClusteredStackedColumn()
‘Creating a Clustered Stacked Column Chart – refer Image 2
Dim rngSourceData As Range, ws As Worksheet, oChObj As ChartObject
Set ws = Sheets(“Sheet1”)
Set rngSourceData = ws.Range(“B9:G28”)
‘delete existing embedded charts in the worksheet
For Each oChObj In ws.ChartObjects
oChObj.Delete
Next
‘align chart’s left edge to the left edge of column I, align chart’s top edge to the top of row 2
Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“I”).Left, Width:=335, Top:=ws.Rows(2).Top, Height:=210)
With oChObj.Chart
‘chart type of stacked column
.ChartType = xlColumnStacked
‘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
‘ChartGroup object refers to one or more series plotted in a chart with the same format ex. a line chart group, or a bar chart group …
‘use the GapWidth property of a ChartGroup object to set the space between column clusters, as a percentage of the column width
.ChartGroups(1).GapWidth = 0
With .Parent
‘set the embedded chart to be free-floating so that it does not move or size with its underlying cells
.Placement = xlFreeFloating
.Name = “SalesProfitMktg-ActualVsEstimate“
End With
With .SeriesCollection(1)
‘set an array of X values ie. values for the category axis or x-axis
.XValues = ws.Range(“A10:A28”)
End With
‘set fill color for each series
.SeriesCollection(ws.Range(“B9”).Value).Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.SeriesCollection(ws.Range(“C9”).Value).Format.Fill.ForeColor.RGB = RGB(14, 216, 24)
.SeriesCollection(ws.Range(“D9”).Value).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
.SeriesCollection(ws.Range(“E9”).Value).Format.Fill.ForeColor.RGB = RGB(0, 112, 192)
.SeriesCollection(ws.Range(“F9”).Value).Format.Fill.ForeColor.RGB = RGB(0, 176, 240)
.SeriesCollection(ws.Range(“G9”).Value).Format.Fill.ForeColor.RGB = RGB(102, 204, 255)
‘refer the Axis object – category axis
With .Axes(xlCategory, xlPrimary)
‘specify the text orientation for tick-mark labels
.TickLabels.Orientation = xlTickLabelOrientationHorizontal
‘set the interval between tick-mark lables ie. the number of categories between tick-mark labels
.TickLabelSpacing = 1
‘using the Offset Property of the TickLabels object to set the distance between the levels of axis labels, and the distance between the axis line & the first level – the default distance between the axis labels & the axis line is 100
percent
.TickLabels.Offset = 10
‘set bold font for tick labels
.TickLabels.Font.Bold = True
‘the value axis does not cross the category axis between categories
.AxisBetweenCategories = False
‘no tickmarks for the category axis
.MajorTickMark = xlTickMarkNone
End With
‘refer the Axis object – primary value axis
With .Axes(xlValue, xlPrimary)
‘set the HasMinorGridlines property (of the Axis object) to True, to display the minor gridlines for an axis
.HasMajorGridlines = True
With .MajorGridlines
‘set gridlines color to dark red
.Border.Color = RGB(192, 0, 0)
.Border.LineStyle = xlContinuous
End With
End With
‘the ChartTitle object represents the chart title
With .ChartTitle
.Text = “Actual vs Estimate: Clustered stacked column chart“
.Font.Name = “Arial”
.Font.Size = 10
.Font.Bold = True
.Font.Color = vbRed
End With
With .PlotArea
‘remove fill color from Plot Area
.Fill.Visible = False
‘add fill color to Plot Area – set the interior color for the plot area to Yellow
.Fill.Visible = True
‘set the interior color for the plot area
.Format.Fill.ForeColor.RGB = RGB(253, 234, 218)
‘set plot area’s border color to red
.Border.ColorIndex = 3
‘set plot area’s border thickness
.Border.Weight = xlThin
End With
With .ChartArea
‘set interior color for chart area
.Format.Fill.ForeColor.RGB = RGB(252, 213, 181)
‘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.5
‘set chart area’s border color to red
.Border.ColorIndex = 3
.Border.LineStyle = xlContinuous
End With
Dim objLE As LegendEntry
With .Legend
‘set legend font to bold & italics
.Font.Bold = True
.Font.Italic = True
‘set font color for text of all legend entries the same as the corresponding series fill color
For Each objLE In .LegendEntries
objLE.Font.Color = objLE.LegendKey.Interior.Color
Next
End With
End With
End Sub
Create a Clustered Stacked Bar Chart displaying Variance between Series
To compare 2 series across multiple categories and display variance, you can create a ‘clustered stacked bar’ (or ‘clustered stacked column’ chart), as explained below. This is similar to the above “Creating a Clustered Stacked Column Chart”.
We give an example below, where we compare “Actual Sales” with “Estimated Sales” for each year. The common category is each year for which data is compared. For each year, we have 2 sets of data – Actual & Estimated, for which we have separate bars which are clustered together. Each bar has 2 stacks – Sales & Variance – where the actual sales is less than estimated the actual sales bar has an additional stack of the variance, and similarly the estimated sales bar is stacked with the variance where these are less than actual sales. The default Excel chart type of clustered bar will display the sales (actual & estimated) for each category, but they will not display the variance, hence we combine a Clustered Bar chart with a Stacked Bar chart into one.
Original data is in the worksheet range of A1:E5. We have rearranged it in the range A7:E20, to display appropriate stacked columns spacing. Refer Image 3 & below code how this is done.
Example: Creating a Clustered Stacked Bar Chart displaying Variance between Series- refer Image 3
To download Excel file with live code, click here.
Sub Chart_ClusteredStackedBar_ForVariance()
‘Creating a Clustered Stacked Bar Chart displaying Variance between Series- refer Image 3
Dim rngSourceData As Range, ws As Worksheet, oChObj As ChartObject
Set ws = Sheets(“Sheet1”)
Set rngSourceData = ws.Range(“B7:E20”)
‘delete existing embedded charts in the worksheet
For Each oChObj In ws.ChartObjects
oChObj.Delete
Next
‘align chart’s left edge to the left edge of column G, align chart’s top edge to the top of row 2
Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“G”).Left, Width:=335, Top:=ws.Rows(2).Top, Height:=225)
With oChObj.Chart
‘chart type of stacked bar
.ChartType = xlBarStacked
‘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
‘ChartGroup object refers to one or more series plotted in a chart with the same format ex. a line chart group, or a bar chart group …
‘use the GapWidth property of a ChartGroup object to set the space between column clusters, as a percentage of the column width
.ChartGroups(1).GapWidth = 0
‘refer ChartObject object (oChObj)
With .Parent
‘set the embedded chart to be free-floating so that it does not move or size with its underlying cells
.Placement = xlFreeFloating
.Name = “SalesVariance-ActualVsEstimate“
End With
With .SeriesCollection(1)
‘set an array of X values ie. values for the category axis or x-axis
.XValues = ws.Range(“A8:A20”)
End With
‘set fill color for series
.SeriesCollection(ws.Range(“B7”).Value).Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.SeriesCollection(ws.Range(“D7”).Value).Format.Fill.ForeColor.RGB = RGB(146, 208, 80)
‘series displaying negative variance (actual sales are less than estimated sales)
‘using the Chart.SeriesCollection Method to return a single series (Series object) by its name
With .SeriesCollection(ws.Range(“C7”).Value).Format
‘set fill color for series
.Fill.ForeColor.RGB = RGB(228, 109, 10)
.Line.Visible = msoFalse
‘set series border – this differentiates the variance series
.Line.Visible = msoTrue
‘set the dash style for Border as a solid line
.Line.DashStyle = msoLineSolid
‘set border line thickness
.Line.Weight = 1.25
‘set border color to yellow
.Line.ForeColor.RGB = RGB(255, 255, 0)
End With
‘series displaying positive variance (actual sales are more than estimated sales)
With .SeriesCollection(ws.Range(“E7”).Value).Format
.Fill.ForeColor.RGB = RGB(255, 0, 0)
.Line.Visible = msoFalse
.Line.Visible = msoTrue
.Line.DashStyle = msoLineSolid
.Line.Weight = 1.25
.Line.ForeColor.RGB = RGB(255, 255, 0)
End With
‘refer the Axis object – category axis
With .Axes(xlCategory, xlPrimary)
‘specify the text orientation for tick-mark labels
.TickLabels.Orientation = xlTickLabelOrientationHorizontal
‘set the interval between tick-mark lables ie. the number of categories between tick-mark labels
.TickLabelSpacing = 1
‘using the Offset Property of the TickLabels object to set the distance between the levels of axis labels, and the distance between the axis line & the first level – the default distance between the axis labels & the axis line is 100
percent
.TickLabels.Offset = 10
‘set bold font for tick labels
.TickLabels.Font.Bold = True
‘the value axis does not cross the category axis between categories
.AxisBetweenCategories = False
‘no tickmarks for the category axis
.MajorTickMark = xlTickMarkNone
End With
‘refer the Axis object – primary value axis
With .Axes(xlValue, xlPrimary)
‘set the minimum and maximum values for the value axis
.MinimumScale = 0
.MaximumScale = WorksheetFunction.Round(WorksheetFunction.Max(ws.Range(“B8:B20”)) * 1.1, -2)
.MajorUnit = 200
.TickLabels.Font.Size = 9
‘set number format for tick-mark labels
.TickLabels.NumberFormat = “$#,##0“
‘set text orientation angle for tick labels
.TickLabels.Orientation = 25
‘set the HasMinorGridlines property (of the Axis object) to True, to display the minor gridlines for an axis
.HasMajorGridlines = True
With .MajorGridlines
‘set gridlines color to dark red
.Border.Color = RGB(192, 0, 0)
.Border.LineStyle = xlContinuous
End With
End With
‘the ChartTitle object represents the chart title
With .ChartTitle
.Text = “Sales Variance: Actual-Estimate“
.Font.Name = “Arial“
.Font.Size = 10
.Font.Bold = True
.Font.Color = vbRed
End With
With .PlotArea
‘remove fill color from Plot Area
.Fill.Visible = False
‘add fill color to Plot Area
.Fill.Visible = True
‘set the interior color for the plot area to Yellow
.Format.Fill.ForeColor.RGB = RGB(253, 234, 218)
‘set plot area’s border color to red
.Border.ColorIndex = 3
‘set plot area’s border thickness
.Border.Weight = xlThin
End With
With .ChartArea
‘set interior color for chart area
.Format.Fill.ForeColor.RGB = RGB(252, 213, 181)
‘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.5
‘set chart area’s border color to red
.Border.ColorIndex = 3
.Border.LineStyle = xlContinuous
End With
Dim objLE As LegendEntry
With .Legend
‘set legend font to bold & italics
.Font.Bold = True
.Font.Italic = True
‘set font color for text of all legend entries the same as the corresponding series fill color
For Each objLE In .LegendEntries
objLE.Font.Color = objLE.LegendKey.Interior.Color
Next
End With
End With
End Sub
A pie chart is particularly useful where relative percentages are required to be displayed. Create a pie chart where you have not more than seven categories, and they represent parts of a whole pie, and where you have only one data series to plot with data values which are not negative or zero values.
To download Excel file with live code, click here.
Example: Create a pie chart with leader lines & custom color the slices of the pie – refer Image 4
Sub PieChart_1()
‘create a pie chart with leader lines & custom color the slices of the pie – refer Image 4
Dim ws As Worksheet, rngSourceData As Range, i As Integer
Set ws = Sheets(“Sheet1”)
Dim oChObj As ChartObject
Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“A”).Left, Width:=311, Top:=ws.Rows(7).Top, Height:=204)
Set rngSourceData = ws.Range(“A2:B5”)
With oChObj.Chart
‘chart type is 3D Pie Exploded
.ChartType = xl3DPieExploded
.SetSourceData Source:=rngSourceData, PlotBy:=xlColumns
‘display chart title
.HasTitle = True
With .ChartTitle
.Text = ws.Range(“A1”) & “-” & ws.Range(“B1”)
.Font.Size = 12
‘set font color to blue
.Font.ColorIndex = 5
‘continuous line for border
.Border.LineStyle = xlContinuous
‘set border color to blue
.Border.ColorIndex = 5
End With
‘set the elevation to 25 degrees of the 3-D chart view (tilt the chart up), which is the height at which the chart is viewed – default elevation of a new chart is 0 degrees.
.Elevation = 25
‘turn the chart around – set the rotation of the 3-D chart view, in degrees ie. the rotation of the plot area around the z-axis (the depth axis)
.Rotation = 60
With .SeriesCollection(1)
‘applies data labels to a series in a chart – enable (to display) the category name, value & percentage for data labels
.ApplyDataLabels ShowCategoryName:=True, ShowValue:=True, ShowPercentage:=True
.DataLabels.Position = xlLabelPositionBestFit
.DataLabels.Font.Size = 10
‘dark red font color
.DataLabels.Font.ColorIndex = 9
.DataLabels.Font.Bold = True
‘display leader lines
.HasLeaderLines = True
‘use the LeaderLines property of the Series object to get a reference to the LeaderLines object which manipulates the appearance of leader lines
‘set leader lines color to blue
.LeaderLines.Border.ColorIndex = 5
‘color the slices of the pie (color index 3 for red, 4 for green, 5 for blue, 6 for yellow)
For i = 1 To .Points.count
.Points(i).Interior.ColorIndex = i + 2
Next i
End With
‘clear the plot area fill & border
With .PlotArea
.Fill.Visible = False
.Border.LineStyle = xlLineStyleNone
End With
‘chart area fill
With .ChartArea.Format.Fill
‘gradient fill style of msoGradientHorizontal and gradient variant of 1
.TwoColorGradient msoGradientHorizontal, 1
‘set the interior color for stop #1 (ForeColor) to Green
.ForeColor.RGB = RGB(0, 255, 0)
‘set the color for stop #2 (BackColor) to Orange
.BackColor.RGB = RGB(255, 192, 0)
End With
With .Legend
‘XlLineStyle Enumeration: xlContinuous (Continuos Line), xlDash (Dashed line), …
.Border.LineStyle = xlDash
.Format.Line.Weight = 0.25
.Shadow = False
End With
End With
End Sub
XY Scatter Chart & Bubble Chart
An XY Scatter chart can be plotted from data arranged in columns or rows on a worksheet – the chart has 2 value axis with one set of data plotted on vertical or y-axis and another set of data plotted on horizontal or x-axis. Values on both axis are clustered together into single data points and displayed at intervals which might not be spaced evenly and may be irregular. A scatter chart will: (i) plot two groups of numbers and display their relationship in a single series of xy coordinates; or (ii) display relationships between numerical data in multiple series where all series may – either share the same X values with distinct Y values, or share the same Y values with distinct X values, or else each series may have its own distinct X & Y values. When data is selcted from over 2 columns or rows, Excel by default assumes same X values in the first column or row with other columns or rows representing Y data for multiple series. Scatter charts are particularly useful for comparing numerical data (viz. scientific, statistical or engineering)), where horizontal x-axis has a large number of data points wherein values are not spaced evenly at regular intervals and you want to compare and show relationships by grouping sets of values by disregarding the time factor or interval.
Bubble charts are similar to XY Scatter charts, except that bubble charts compare 3 sets of values instead of two sets of values compared in scatter charts, where the third value determines the size of the marker where the 2 values intersect in scatter charts. The x & y values in a scatter chart intersect at points, and a bubble chart marks these points as circles or bubbles, and the size of this bubble marker is determined and is proportionate to the third set of values. You can also create a bubble chart with a 3-D effect.
To download Excel file with live code, click here.
Example: Create XY Scatter chart having single series with X & Y values – refer Image 5a
Sub Chart_XYScatter_1()
‘create XY Scatter chart having single series with X & Y values – refer Image 5a
Dim ws As Worksheet, srs As Series
Set ws = Sheets(“Sheet1”)
‘declare a ChartObject
Dim oChObj As ChartObject
‘align the left edge of the embedded chart with the left edge of the worksheet’s column A, align top edge of the chart to the top of row 9
Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“A”).Left, Width:=350, Top:=ws.Rows(9).Top, Height:=210)
‘set the embedded chart to be free-floating so that it does not move or size with its underlying cells
oChObj.Placement = xlFreeFloating
With oChObj.Chart
‘set chart title
.HasTitle = True
.ChartTitle.Text = “ScatterChart: ” & ws.Range(“A1”) & “–” & ws.Range(“B1”)
‘create new series with Y & X values
Set srs = .SeriesCollection.NewSeries
With srs
.Name = ws.Range(“B1”)
.Values = ws.Range(“A2:A7”)
.XValues = ws.Range(“B2:B7”)
End With
‘chart type – xlXYScatter, scatter with only markers (no line)
.ChartType = xlXYScatter
End With
End Sub
Example: Create XY Scatter chart with multiple series where all series share the same Y values with distinct X values – refer Image 5b
Sub Chart_XYScatter_2()
‘create XY Scatter chart with multiple series where all series share the same Y values with distinct X values – refer Image 5b
Dim ws As Worksheet, iSeries As Integer, i As Integer, iColOffset As Integer
Set ws = Sheets(“Sheet1”)
‘number of series in chart
iSeries = ws.Range(“B1:C1”).Columns.count
‘offset column for XValues
iColOffset = ws.Range(“B1”).Column – ws.Range(“A1”).Column
‘declare a ChartObject
Dim oChObj As ChartObject
‘align the left edge of the embedded chart with the left edge of the worksheet’s column A, align top edge of the chart to the top of row 9
Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“A“).Left, Width:=350, Top:=ws.Rows(9).Top, Height:=210)
‘set the embedded chart to be free-floating so that it does not move or size with its underlying cells
oChObj.Placement = xlFreeFloating
With oChObj.Chart
‘set chart title
.HasTitle = True
.ChartTitle.Text = “ScatterChart – ” & ws.Range(“A1“) & ” vs ” & ws.Range(“B1“) & “/” & Left(ws.Range(“C1”), 5)
.ChartTitle.Font.Size = 12
‘create multiple series where all series share the same Y values with distinct X values
For i = 1 To iSeries
With .SeriesCollection.NewSeries
‘series name
.Name = ws.Cells(1, iColOffset + i)
‘values plotted on x-axis
.XValues = ws.Range(ws.Cells(2, iColOffset + i), ws.Cells(7, iColOffset + i))
‘values plotted on y-axis
.Values = ws.Range(“A2:A7”)
End With
Next i
‘chart type – xlXYScatter, scatter with only markers (no line)
.ChartType = xlXYScatter
‘Axis Type of xlCategory represents the horizontal x-axis of scatter charts
With .Axes(xlCategory)
‘set the minimum and maximum values for the x-axis
.MaximumScale = 12000
.MinimumScale = 0
.MajorUnit = 2000
End With
‘set title for value axis
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Caption = ws.Range(“A1”)
End With
End Sub
Example: Create Bubble chart multiple series where all series share the same Y values with distinct X values – refer Image 6
Sub Chart_Bubble()
‘create Bubble chart multiple series where all series share the same Y values with distinct X values – refer Image 6
Dim ws As Worksheet, srs As Series, iSeries As Integer, i As Integer, iColOffsetXValues As Integer, iColOffsetBubbleSizes As Integer
Set ws = Sheets(“Sheet1”)
‘number of series in chart
iSeries = ws.Range(“B1:C1”).Columns.count
‘offset column for XValues
iColOffsetXValues = ws.Range(“B1”).Column – ws.Range(“A1”).Column
‘offset column for Bubble Sizes
iColOffsetBubbleSizes = ws.Range(“D1”).Column – ws.Range(“A1”).Column
Dim oChObj As ChartObject
‘align the left edge of the embedded chart with the left edge of the worksheet’s column A, align top edge of the chart to the top of row 9
Set oChObj = ws.ChartObjects.Add(Left:=ws.Columns(“A”).Left, Width:=350, Top:=ws.Rows(9).Top, Height:=210)
‘set the embedded chart to be free-floating so that it does not move or size with its underlying cells
oChObj.Placement = xlFreeFloating
With oChObj.Chart
‘chart type – Bubble
.ChartType = xlBubble
‘set chart title
.HasTitle = True
.ChartTitle.Text = “BubbleChart – ” & ws.Range(“A1“) & ” vs ” & ws.Range(“B1”) & “/” & Left(ws.Range(“C1”), 5)
.ChartTitle.Font.Size = 12
‘a ChartGroup object represents all (could be one or more) series plotted in a chart with the same format (line, bar, bubble, …)
‘the BubbleScale property (applicable only to Bubble charts) sets a scale factor for the bubbles
‘scale the bubbles to 40% of their original size
.ChartGroups(1).BubbleScale = 40
‘create multiple series where all series share the same Y values with distinct X values
For i = 1 To iSeries
With .SeriesCollection.NewSeries
.Name = ws.Cells(1, iColOffsetXValues + i)
.XValues = ws.Range(ws.Cells(2, iColOffsetXValues + i), ws.Cells(7, iColOffsetXValues + i))
.Values = ws.Range(“A2:A7”)
‘set the BubbleSizes property property using R1C1-style notation, for example: .BubbleSizes = “=Sheet1!r2c4:r7c4”
‘use the BubbleSizes property of the series object to set the size of marker points (ie. bubbles size), describing the cells in A1-style notation
.BubbleSizes = “=” & ws.Range(ws.Cells(2, iColOffsetBubbleSizes + i), ws.Cells(7, iColOffsetBubbleSizes + i)).Address(ReferenceStyle:=xlR1C1, External:=True)
End With
Next i
‘Axis Type of xlCategory represents the horizontal x-axis of scatter / bubble charts
With .Axes(xlCategory)
‘set the minimum and maximum values for the x-axis
.MaximumScale = 12500
.MinimumScale = 0
.MajorUnit = 2500
End With
‘set a three-dimensional appearance for the series
.SeriesCollection(“Profit”).Has3DEffect = True
‘set title for value axis
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Caption = ws.Range(“A1“)
End With
End Sub
Example: Create & manipulate both X & Y error bars, types xlErrorBarTypePercent & xlErrorBarTypeCustom, for a bubble chart – refer Image 7
Sub ErrorBars_2()
‘create & manipulate both X & Y error bars, types xlErrorBarTypePercent & xlErrorBarTypeCustom, for a bubble chart – refer Image 7
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(8).Top, Height:=190)
Dim strErrorAmt As String, strErrorMinusVal As String
‘set cell values for custom positive error bar amounts
strErrorAmt = “=” & ws.Name & “!” & Range(“D2:D7“).Address(ReferenceStyle:=xlR1C1)
‘set cell values for custom negative error bar amounts
strErrorMinusVal = “=” & ws.Name & “!” & Range(“E2:E7“).Address(ReferenceStyle:=xlR1C1)
With oChObj.Chart
‘chart type – Bubble
.ChartType = xlBubble
.HasTitle = True
.ChartTitle.Text = “BubbleChart: ” & ws.Range(“A1”) & “–” & ws.Range(“B1“)
.ChartTitle.Font.Size = 12
‘scale the bubbles to 20% of their original size
.ChartGroups(1).BubbleScale = 20
‘turn on minor gridlines for both y-axis & x-axis – increase readability for error values
.Axes(xlValue).HasMinorGridlines = True
.Axes(xlCategory).HasMinorGridlines = True
‘create new series with Y & X values
Set srs = .SeriesCollection.NewSeries
With srs
.Name = ws.Range(“B1”)
.Values = ws.Range(“A2:A7”)
.XValues = ws.Range(“B2:B7”)
‘set the size of marker points (ie. bubbles size), using R1C1-style notation for the cell reference
‘.BubbleSizes = “=Sheet1!r2c3:r7c3“
.BubbleSizes = ws.Name & “!r2c3:r7c3“
‘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 20%;
.ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypePercent, Amount:=20
‘not possible to programatically create different color/style formatting individually for X & Y error bars, unless you use the SendKeys method – in vba X & Y error bars are formatted together, because using the ErrorBars property
represents all error bars for the series.
‘set color/style formatting for the vertical(Y) Error Bars
With .ErrorBars
.Format.Line.Visible = msoFalse
.Format.Line.Visible = msoTrue
.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
‘includes both positive & negative error range; specifies error bars for x-axis values, as xlErrorBarTypeCustom which indicates range is set by fixed values or cell values; set range to be covered by the error bars – custom
positive error bar amounts are set using the Amount argument & custom negative error bar amounts are set using the MinusValues argument.
.ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeCustom, Amount:=strErrorAmt, MinusValues:=strErrorMinusVal
End With
End With
End Sub