Chart Elements in Excel VBA (Part 1) – Chart Title, Chart Area, Plot Area, Chart Axes, Chart Series, Data Labels, Chart Legend
Contents:
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 Title, Chart Area, Plot Area & Chart Axes. Chart Elements – Chart Series, Data Labels & Chart Legend – are illustrated in Part 2.
Use the ChartTitle Property of the Chart object to return a ChartTitle object which represents the chart title – Syntax: objChart.ChartTitle. The chart title is accessed & manipulated through the properties & methods of the ChartTitle object, some of which are detailed below. The ChartTitle object exists and can be used only if the HasTitle property (of the Chart object) is True – Set (or return) to True for the chart title to be visible viz. objChart.HasTitle = True.
|
Commonly used Properties of the ChartTitle object:
|
||
| Property | Syntax | Description |
| AutoScaleFont Property | objChartTitle.AutoScaleFont | Set (or returns) True where the chart title’s text font size will change whenever the chart size changes. False setting will keep the chart title font at the same size even when there is a change in chart size. |
| Sheets(“Sheet1”).Activate ActiveSheet.ChartObjects(1).Activate ActiveChart.ChartTitle.Select Selection.AutoScaleFont = True |
||
| Caption Property | objChartTitle.Caption | Sets or returns the text for the chart title, as a string value. |
| Text Property | objChartTitle.Text | Sets or returns the text for the chart title, as a string value. |
| Characters Property | objChartTitle.Characters(Start, Length) | Use the Characters property which returns a Characters object representing a range of characters within the text string of chart title, 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. |
| With Sheets(“Sheet1”).ChartObjects(1).Chart.ChartTitle
‘set the text for chart title ‘format characters within a text string – starting at 4th character & a length of 3 characters – representing the word “Qtr” .Font.Italic = True End With End With |
||
| Format Property | objChartTitle.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. |
| Left Property | objChartTitle.Left | Sets or returns the distance, in points, from the chart title’s left edge to the left edge of the chart area |
| Top Property | objPlotArea.Top | Sets or returns the distance, in points, from the chart title’s top edge to the top edge of the chart area. |
| HorizontalAlignment Property | objChartTitle.HorizontalAlignment | Sets or returns a value (xlCenter, xlDistributed, xlJustify, xlLeft or xlRight) for the horizontal alignment of the chart title. |
| VerticalAlignment Property | objChartTitle.VerticalAlignment | Sets or returns a value (xlBottom, xlCenter, xlDistributed, xlJustify or xlTop) for the vertical alignment of the chart title. |
| Shadow Property | objChartTitle.Shadow | Sets or returns a Boolean value determining if the chart title has a shadow or not . Use True to add a shadow:
Sheets(“Sheet1”).ChartObjects(1).Chart.ChartTitle.Shadow = True |
| Orientation Property | objChartTitle.Orientation | Sets or returns a value [xlDownward (-4170), xlHorizontal (-4128), xlUpward (-4171), xlVertical (-4166), or an integer value from -90 to 90 degrees] that determines the text orientation. The constants indicate whether Text runs downward / Horizontal / Upward / Downward & is Centered in the cell, respectively. |
| Name Property | objChartTitle.Name | returns the name of the ChartTitle object, as a string value. |
| Parent Property | objChartTitle.Parent | returns the parent object for the chart title viz. returns the chart name:
MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.ChartTitle.Parent.Name |
| Position Property | objChartTitle.Position | Sets or returns the chart title’s position on the chart, wherein the constant xlChartElementPositionCustom (-4114) specifies a specific position for the chart title & the constant xlChartElementPositionAutomatic (-4105) automatically sets the chart title’s position. |
|
Commonly used Methods of the ChartTitle object:
|
||
| Method | Syntax | Description |
| Delete Method | objChartTitle.Delete | deletes the ChartTitle object |
| Select Method | objChartTitle.Select | selects the ChartTitle object |
Child Objects for the ChartTitle Object: Above we have discussed: the ChartFormat object – use the Format Property – objChartTitle.Format – to return the ChartFormat object which contains the line, fill & effect formatting for the chart title; the Characters Object – use the Characters property – objChartTitle.Characters(Start, Length) – to return a Characters object representing a range of characters within the text string of chart title, and this will enable formatting these specific characters. Some other child objects which are often used with the ChartTitle Object include: ChartFillFormat Object – use the Fill Property – objChartTitle.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements; Interior Object – use the Interior property – objChartTitle.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border Property – objChartTitle.Border – to return a Border object, to manipulate a chart element’s border; Font Object – use the Font Property – objChartTitle.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 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
The area where chart data is plotted is the Plot Area – the Plot Area is surrounded by the Chart Area. A 2-D chart’s chart area contains: the axes, the chart title, the axis titles & the legend. A 3-D chart’s chart area contains the chart title & the legend, without including the area where the data is plotted within the chart area (ie. the plot area). The ChartArea object is used to format the chart area. You will use the chart area fill separately, which is distinct from the fill for plot area.
Use the ChartArea Property of the Chart object to return a ChartArea object which refers to the chart area in a chart. Syntax: objChart.ChartArea.
| Commonly used Properties of the ChartArea object:
|
||
| Property | Syntax | Description |
| Format Property | objChartArea.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 | objChartArea.Height | Sets or returns the height, in points, of the chart area |
| Width Property | objChartArea.Width | Sets or returns the width, in points, of the chart area |
| Left Property | objChartArea.Left | Returns the distance, in points, from the left edge of the chart area to the left edge of the chart. To set the distance, in points, from the left edge of the embedded chart to the left edge of column A on a worksheet use the Left property of the ChartObject object viz objChartObject.Left. |
| Top Property | objChartArea.Top | Returns the distance, in points, from the top edge of the chart area to the top of the chart. To set the distance, in points, from the top edge of the embedded chart to the top of row 1 on a worksheet, use the Top property of the ChartObject object viz objChartObject.Top. |
| Name Property | objChartArea.Name | returns the name of the ChartArea object, as a string value. |
| Parent Property | objChartArea.Parent | returns the parent object for the chart area |
| RoundedCorners Property | objChartArea.RoundedCorners | sets or returns a Boolean value determining if the chart area has rounded corners or not – True indicates rounded corners. |
| Shadow Property | objChartArea.Shadow | sets or returns a Boolean value determining if the chart area has a shadow or not |
| Commonly used Methods of the ChartArea object:
|
||
| Method | Syntax | Description |
| ClearContents Method | objChartArea.ClearContents | clears all data from the chart area but does not clear the formatting which remains intact. |
| ClearFormats Method | objChartArea.ClearFormats | clears all formatting from the chart area |
| Clear Method | objChartArea.Clear | clears the Chart Area – clears all, including data and the formatting. |
| Select Method | objChartArea.Select | selects the chart area |
| Copy Method | objChartArea.Copy | copies chart area to clipboard |
Child Objects for the ChartArea Object: Above we have discussed: the ChartFormat object – use the Format Property – objChartArea.Format – to return the ChartFormat object which contains the line, fill & effect formatting for the chart area. Some other child objects which are often used with the ChartArea Object include: ChartFillFormat Object – use the Fill Property – objChartArea.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements; Interior Object – use the Interior property – objChartArea.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border Property – objChartArea.Border – to return a Border object, to manipulate a chart element’s border; Font Object – use the Font Property – objChartArea.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 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
The area where chart data is plotted is the Plot Area – the Plot Area is surrounded by the Chart Area. A 2-D chart’s plot area contains: the data markers, gridlines, data labels, trendlines & optional chart items placed in the chart area. A 3-D chart’s plot area additionally contains: the walls, floor, axes, axis titles & tick-mark labels in the chart. You will use the chart area fill separately, which is distinct from the fill for plot area. The PlotArea Object is used to format the plot area.
Use the PlotArea property of the Chart object to return a PlotArea object which refers to the plot area of a chart. Syntax: objChart.PlotArea.
| Commonly used Properties of the PlotArea object: | ||
| Property | Syntax | Description |
| Format Property | objPlotArea.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 | objPlotArea.Height | Sets or returns the height, in points, of the plot area – the axis labels are included within the bounding rectangle for determining the height. |
| InsideHeight Property | objPlotArea.InsideHeight | Sets or returns the inside height, in points, of the plot area without including the axis labels. |
| Width Property | objPlotArea.Width | Sets or returns the width, in points, of the plot area – the axis labels are included within the bounding rectangle for determining the width. |
| InsideWidth Property | objPlotArea.InsideWidth | Sets or returns the inside width, in points, of the plot area without including the axis labels. |
| Left Property | objPlotArea.Left | Sets or returns the distance, in points, from the left edge of the chart area to the left edge of the plot area – the axis labels are included within the bounding rectangle for determining the distance. |
| InsideLeft Property | objPlotArea.InsideLeft | Sets or returns the distance, in points, from the left edge of the chart area to the inside left edge of the plot area without including the axis labels. |
| Top Property | objPlotArea.Top | Sets or returns the distance, in points, from the top edge of the chart area to the top edge of the plot area – the axis labels are included within the bounding rectangle for determining the distance. |
| InsideTop Property | objPlotArea.InsideTop | Sets or returns the distance, in points, from the top edge of the chart area to the inside top edge of the plot area without including the axis labels. |
| Name Property | objPlotArea.Name | returns the name of the PlotArea object, as a string value. |
| Parent Property | objPlotArea.Parent | returns the parent object for the plot area viz. returns the chart name:
MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Parent.Name |
| Position Property | objPlotArea.Position | Sets or returns the plot area’s position on the chart, wherein the constant xlChartElementPositionCustom (-4114) specifies a specific position for the plot area & the constant xlChartElementPositionAutomatic (-4105) automatically sets the plot area’s position. |
| Commonly used Methods of the PlotArea object:
|
||
| Method | Syntax | Description |
| ClearFormats Method | objPlotArea.ClearFormats | clears all formatting from the plot area |
| Select Method | objPlotArea.Select | selects the plot area |
|
Sheets(“Sheet1”).ChartObjects(1).Chart.PlotArea.Select
|
||
Child Objects for the PlotArea Object: Above we have discussed: the ChartFormat object – use the Format Property – objPlotArea.Format – to return the ChartFormat object which contains the line, fill & effect formatting for the plot area. Some other child objects which are often used with the PlotArea Object include: ChartFillFormat Object – use the Fill Property – objPlotArea.Fill – to return a ChartFillFormat object (valid only for charts), to manipulate fill formatting for chart elements; Interior Object – use the Interior property – objPlotArea.Interior – to return the Interior object, to manipulate the chart element’s interior (inside area); Border Object – use the Border Property – objPlotArea.Border – to return a Border object, to manipulate a chart element’s border. 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: Illustrating the chart’s plot area / inside plot area – using Left / InsideLeft, Top / InsideTop, Width / InsideWidth, Height / InsideHeight properties – refer Image 1
Sub BoundingRectangle_PlotArea_InsidePlotArea()
‘illustrating the inside of chart’s plot area / the chart’s plot area, within bounding rectangles – refer Image 1
‘create a text box representing the inside of chart’s plot area without including the axis labels within the bounding rectangle (red lines of round dots)
With Sheets(“Sheet1”).ChartObjects(1).Chart
With .Shapes.AddTextbox(msoTextOrientationDownward, .PlotArea.InsideLeft, .PlotArea.InsideTop, .PlotArea.InsideWidth, .PlotArea.InsideHeight)
.Line.Weight = 4
.Line.DashStyle = msoLineRoundDot
.Line.ForeColor.RGB = RGB(255, 0, 0)
End With
‘create a text box representing the chart’s plot area by including the axis labels within the bounding rectangle (green lines of long dashes)
With .Shapes.AddTextbox(msoTextOrientationDownward, .PlotArea.Left, .PlotArea.Top, .PlotArea.Width, .PlotArea.Height)
.Line.Weight = 2
.Line.DashStyle = msoLineLongDash
.Line.ForeColor.RGB = RGB(0, 255, 0)
End With
End With
End Sub
Example: PlotArea Alignment with VBA – Centering a PlotArea
Sub PlotArea_Center()
‘center Plot Area within the Chart
Dim wsChart As Worksheet
Dim objChartObj As ChartObject
Dim sChartTitleWidth As Single
Set wsChart = Sheets(“Sheet1”)
Set objChartObj = wsChart.ChartObjects(1)
‘center PlotArea within the Chart Area
With objChartObj.Chart
‘center vertically
.PlotArea.Top = (.ChartArea.Height – .PlotArea.Height) / 2
‘center horizontally
.PlotArea.Left = (.ChartArea.Width – .PlotArea.Width) / 2
End With
End Sub
Example: ChartTitle Alignment with VBA – Centering a ChartTitle Horizontally
Sub ChartTitle_HorizontalCenterInChart()
‘horizontally center the chart title in a chart
‘the ChartTitle does not have a Width property so that you cannot determine its width, which would have made horizontal centering easier
Dim wsChart As Worksheet
Dim objChartObj As ChartObject
Dim sChartTitleWidth As Single
Set wsChart = Sheets(“Sheet1”)
Set objChartObj = wsChart.ChartObjects(1)
‘—————————
‘OPTION 1 – default position of ChartTitle aligns it top-center in the chart
With objChartObj.Chart
‘if the ChartTitle is presently not centered because its position has been changed, you can first remove & then show because the default position of ChartTitle aligns it top-center in the chart
.HasTitle = False
.HasTitle = True
End With
‘—————————
‘OPTION 2 – center ChartTitle, within the chart area
With objChartObj.Chart
If .HasTitle Then
‘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
‘center the ChartTitle, within the chart area
.ChartTitle.Left = .ChartTitle.Left / 2
End If
End With
‘—————————
‘OPTION 3 – center ChartTitle, within the chart area
With objChartObj.Chart
If .HasTitle Then
‘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
‘center the ChartTitle, within the chart area
.ChartTitle.Left = (.ChartArea.Width – sChartTitleWidth) / 2
End If
End With
End Sub
Example: ChartTitle Alignment with VBA – Center ChartTitle, both horizontally & vertically, wrt Plot Area.
Sub ChartTitle_CenterInPlotArea()
‘center ChartTitle, both horizontally & vertically, wrt plot area
‘note that the ChartTitle does not have a Width or Height property
Dim wsChart As Worksheet
Dim objChartObj As ChartObject
Dim sChartTitleWidth As Single, sChartTitleHeight As Single
Set wsChart = Sheets(“Sheet1”)
Set objChartObj = wsChart.ChartObjects(1)
‘center ChartTitle, both horizontally & vertically, wrt plot area
With objChartObj.Chart
‘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’s inside
.ChartTitle.Left = .PlotArea.InsideLeft + (.PlotArea.InsideWidth – sChartTitleWidth) / 2
‘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
‘to vertically center the ChartTitle in top area within ‘Plot Area Inside Top’ & ‘Chart Area Top’, use:
‘.ChartTitle.Top = (.PlotArea.InsideTop – sChartTitleHeight) / 2
End With
End Sub
Example: AxisTitle Alignment with VBA – horizontally / vertically center the Axis Title with re to Plot Area
Sub AxisTitle_Center()
‘center the axis title in a chart
Dim wsChart As Worksheet
Dim objChartObj As ChartObject
Dim sChartTitleWidth As Single, sAxisTitleHeight As Single
Set wsChart = Sheets(“Sheet1”)
Set objChartObj = wsChart.ChartObjects(1)
‘horizontally center the primary category axis title with re to plot area
With objChartObj.Chart
‘move the axis title to the extreme right of the chart area
.Axes(xlCategory, xlPrimary).AxisTitle.Left = .ChartArea.Width
‘determine width of axis title
sAxisTitleWidth = .ChartArea.Width – .Axes(xlCategory, xlPrimary).AxisTitle.Left
‘horizontally center axis title between plot area
.Axes(xlCategory, xlPrimary).AxisTitle.Left = .PlotArea.InsideLeft + (.PlotArea.InsideWidth – sAxisTitleWidth) / 2
End With
‘vertically center the primary value axis title with re to plot area
With objChartObj.Chart
‘move the axis title to the bottom edge of the chart area
.Axes(xlValue, xlPrimary).AxisTitle.Top = .ChartArea.Height
‘determine height of axis title
sAxisTitleHeight = .ChartArea.Height – .Axes(xlValue, xlPrimary).AxisTitle.Top
‘vertically center axis title between plot area
.Axes(xlValue, xlPrimary).AxisTitle.Top = .PlotArea.InsideTop + (.PlotArea.InsideHeight – sAxisTitleHeight) / 2
End With
End Sub
Example: Legend Alignment with VBA – position the legend to align in the bottom left corner within the plot area
Sub Legend_Position_BottomLeftCorner()
‘position the legend – align the chart legend in the bottom left corner within the plot area
Dim wsChart As Worksheet
Dim objChartObj As ChartObject
Set wsChart = Sheets(“Sheet1”)
Set objChartObj = wsChart.ChartObjects(1)
‘—————————
‘ignoring border / line thickness for legend placement
‘position the legend – align the chart legend in the bottom left corner within the plot area, without considering borders’ thickness or axis’ line thickness
With objChartObj.Chart
If .HasLegend = True Then
‘aligns the legend’s left edge to the inside left of the plot area
.Legend.Left = .PlotArea.InsideLeft
‘aligns the legend’s top to the inside top of the plot area by ADDING (i) plot area’s inside top & (ii) plot area’s inside height & then DEDUCTING (i) legend’s height
.Legend.Top = .PlotArea.InsideTop + .PlotArea.InsideHeight – .Legend.Height
End If
End With
‘—————————
‘border / line thickness can affect the legend placement, if the thickness is significant
‘position the legend – align the chart legend in the bottom left corner within the plot area, taking into account the legend’s border thickness & plot area border’s thickness & primary value axis’ line thickness & category axis’ line thickness
With objChartObj.Chart
If .HasLegend = True And .Legend.Border.LineStyle <> xlLineStyleNone And .PlotArea.Border.LineStyle <> xlLineStyleNone And .Axes(xlValue, xlPrimary).Border.LineStyle <> xlLineStyleNone Then
‘aligns the legend’s left edge to the inside left of the plot area by ADDING (i) plot area’s inside left & (ii) half of legend’s border thickness & (iii) half of the – maximum of (a) Value Axis line thickness & (b) plot area border’s thickness
.Legend.Left = .PlotArea.InsideLeft + .Legend.Format.Line.Weight / 2 + WorksheetFunction.Max(.PlotArea.Format.Line.Weight, .Axes(xlValue, xlPrimary).Format.Line.Weight) / 2
‘aligns the legend’s top to the inside top of the plot area by ADDING (i) plot area’s inside top & (ii) plot area’s inside height & (iii) half of legend’s border thickness & then DEDUCTING (i) legend’s height & (ii) half of the – maximum of
(a) Category Axis line thickness & (b) plot area border’s thickness
.Legend.Top = .PlotArea.InsideTop + .PlotArea.InsideHeight – .Legend.Format.Line.Weight / 2 – .Legend.Height – WorksheetFunction.Max(.PlotArea.Format.Line.Weight, .Axes(xlCategory, xlPrimary).Format.Line.Weight) / 2
End If
End With
End Sub
Example: Legend Alignment with VBA – position the legend to align in the top right corner within the plot area, taking into account borders / lines thickness
Sub Legend_Position_TopRightPlotArea()
‘align the chart Legend in the top right corner within the plot area
‘this code takes into account the legend’s border thickness & plot area border’s thickness & the secondary value axis’ line thickness, which might affect the legend placement if the thickness is significant
‘this code also takes into account whether, legend’s border or plot area’s border or the secondary value axis, exist or not
Dim wsChart As Worksheet
Dim objChartObj As ChartObject
Set wsChart = Sheets(“Sheet8”)
Set objChartObj = wsChart.ChartObjects(1)
‘position the Legend – align the chart Legend in the top right corner within the plot area – you dont know if chart elements have a border / line or not, & if yes then what is their thickness
With objChartObj.Chart
If .HasLegend = False Then
MsgBox “Legend does not exist, exit sub”
Exit Sub
Else
‘if secondary value axis exists
If .HasAxis(xlValue, xlSecondary) = True Then
‘legend border & plot area border & axis border exist
If .Legend.Border.LineStyle <> xlLineStyleNone And .PlotArea.Border.LineStyle <> xlLineStyleNone And .Axes(xlValue, xlSecondary).Border.LineStyle <> xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .Legend.Format.Line.Weight / 2 – WorksheetFunction.Max(.PlotArea.Format.Line.Weight, .Axes(xlValue, xlSecondary).Format.Line.Weight) / 2
.Legend.Top = .PlotArea.InsideTop + .Legend.Format.Line.Weight / 2 + .PlotArea.Format.Line.Weight / 2
‘only plot area border & axis border exist
ElseIf .Legend.Border.LineStyle = xlLineStyleNone And .PlotArea.Border.LineStyle <> xlLineStyleNone And .Axes(xlValue, xlSecondary).Border.LineStyle <> xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – WorksheetFunction.Max(.PlotArea.Format.Line.Weight, .Axes(xlValue, xlSecondary).Format.Line.Weight) / 2
.Legend.Top = .PlotArea.InsideTop + .PlotArea.Format.Line.Weight / 2
‘only legend border & axis border exist
ElseIf .Legend.Border.LineStyle <> xlLineStyleNone And .PlotArea.Border.LineStyle = xlLineStyleNone And .Axes(xlValue, xlSecondary).Border.LineStyle <> xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .Legend.Format.Line.Weight / 2 – .Axes(xlValue, xlSecondary).Format.Line.Weight / 2
.Legend.Top = .PlotArea.InsideTop + .Legend.Format.Line.Weight / 2
‘only legend border & plot area border exist
ElseIf .Legend.Border.LineStyle <> xlLineStyleNone And .PlotArea.Border.LineStyle <> xlLineStyleNone And .Axes(xlValue, xlSecondary).Border.LineStyle = xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .Legend.Format.Line.Weight / 2 – .PlotArea.Format.Line.Weight / 2
.Legend.Top = .PlotArea.InsideTop + .Legend.Format.Line.Weight / 2 + .PlotArea.Format.Line.Weight / 2
‘only axis border exists
ElseIf .Legend.Border.LineStyle = xlLineStyleNone And .PlotArea.Border.LineStyle = xlLineStyleNone And .Axes(xlValue, xlSecondary).Border.LineStyle <> xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .Axes(xlValue, xlSecondary).Format.Line.Weight / 2
.Legend.Top = .PlotArea.InsideTop
‘only plot area border exists
ElseIf .Legend.Border.LineStyle = xlLineStyleNone And .PlotArea.Border.LineStyle <> xlLineStyleNone And .Axes(xlValue, xlSecondary).Border.LineStyle = xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .PlotArea.Format.Line.Weight / 2
.Legend.Top = .PlotArea.InsideTop + .PlotArea.Format.Line.Weight / 2
‘only legend border exists
ElseIf .Legend.Border.LineStyle <> xlLineStyleNone And .PlotArea.Border.LineStyle = xlLineStyleNone And .Axes(xlValue, xlSecondary).Border.LineStyle = xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .Legend.Format.Line.Weight / 2
.Legend.Top = .PlotArea.InsideTop + .Legend.Format.Line.Weight / 2
‘NO legend border OR plot area border OR axis border – none exist
ElseIf .Legend.Border.LineStyle = xlLineStyleNone And .PlotArea.Border.LineStyle = xlLineStyleNone And .Axes(xlValue, xlSecondary).Border.LineStyle = xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width
.Legend.Top = .PlotArea.InsideTop
End If
‘if secondary value axis does NOT exist
Else
‘only legend border & plot area border exist
If .Legend.Border.LineStyle <> xlLineStyleNone And .PlotArea.Border.LineStyle <> xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .Legend.Format.Line.Weight / 2 – .PlotArea.Format.Line.Weight / 2
.Legend.Top = .PlotArea.InsideTop + .Legend.Format.Line.Weight / 2 + .PlotArea.Format.Line.Weight / 2
‘only plot area border exists
ElseIf .Legend.Border.LineStyle = xlLineStyleNone And .PlotArea.Border.LineStyle <> xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .PlotArea.Format.Line.Weight / 2
.Legend.Top = .PlotArea.InsideTop + .PlotArea.Format.Line.Weight / 2
‘only legend border exists
ElseIf .Legend.Border.LineStyle <> xlLineStyleNone And .PlotArea.Border.LineStyle = xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width – .Legend.Format.Line.Weight / 2
.Legend.Top = .PlotArea.InsideTop + .Legend.Format.Line.Weight / 2
‘NO legend border OR plot area border OR axis border – none exist
ElseIf .Legend.Border.LineStyle = xlLineStyleNone And .PlotArea.Border.LineStyle = xlLineStyleNone Then
.Legend.Left = .PlotArea.InsideLeft + .PlotArea.InsideWidth – .Legend.Width
.Legend.Top = .PlotArea.InsideTop
End If
End If
End If
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
To download Excel file with live code, click here.
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
Axes Object (Axes collection) refers to a collection of all the Axis objects in a chart. Axis Object refers to a single axis & is a member of of the Axes collection.
Chart.Axes Method. Use the Axes Method of the Chart object to return a single Axis or Axes collection (collection of all Axis objects) in a chart. Syntax: objChart.Axes(Type, AxisGroup). Both arguments are optional to specify. Specify the axis to be returned in the Type argument – you can use any of the 3 constants for this argument: xlCategory (value 1) – Axis displays categories, also referred as the x-axis; xlValue (value 2) – Axis displays values, also referred as y-axis; xlSeriesAxis (value 3) – Axis displays data series for 3-D charts, also referred as the z-axis representing depth of the chart. Specify the axis group in the AxisGroup argument – xlPrimary (value 1) – primary axis (default axis); xlSecondary (value 2) – secondary axis. The axis group can either be xlPrimary which is the default, or it can be xlSecondary where the chart has multiple series. 3-D charts cannot have secondary axis.
Axes.Item Method. To refer to a single Axis (ie. Axis object) from Axes collection, use the the Item Method of the Axes object. Syntax: objAxes.Item(Type, AxisGroup), where the Type argument is necessary to specify. To return a primary category axis: ActiveChart.Axes(1,1) or ActiveChart.Axes(1) or ActiveChart.Axes(xlCategory, xlPrimary) or ActiveChart.Axes(xlCategory) or ActiveChart.Axes.Item(xlCategory). To return a secondary value axis: ActiveChart.Axes(xlValue, xlSecondary) or ActiveChart.Axes(2, 2). Use the Count Property of the Axes object to return the number of Axis objects in an Axes collection viz. MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.Axes.Count.
Sub AxesCollectionObj_AxisObj()
‘axes collection & axis object
Dim objAxis As Axis
’embedded chart
With Sheets(“Sheet1”).ChartObjects(1).Chart
‘refer single axis (axis object) – primary category axis – you can alternatively use: With .Axes(xlCategory)
With .Axes.Item(xlCategory)
‘set the axis title to be visible
.HasTitle = True
‘set text for axis title
.AxisTitle.Caption = “Dates“
End With
‘refer axes collection using the Chart.Axes method- each axis object in the axes collection
For Each objAxis In .Axes
‘if axis object has title
If objAxis.HasTitle = True Then
‘return axis title for a an axis object which has a title
MsgBox objAxis.AxisTitle.Caption
End If
Next objAxis
‘returns the number of Axis objects in the Axes Collection
MsgBox .Axes.Count
End With
End Sub
HasAxis Property. Syntax: objChart.HasAxis(Index1, Index2). This property sets (or returns) the axes which will be displayed for a chart. Atleast one argument is required to be specified. The Index1 argument specifies the axis Type as defined in the XlAxisType Enumeration: xlCategory (value 1), xlValue (value 2), or xlSeriesAxis (value 3). The Index2 argument specifies the Axis Group as defined in the XlAxisGroup Enumeration: xlPrimary (value 1), or xlSecondary (value 2). To turn on the primary x-axis for a chart: Sheets(“Sheet1”).ChartObjects(1).Chart.HasAxis(xlCategory) = True.
Tick Marks & Gridlines. Tick marks are small lines of measurement which intersect an axis (equated to divisions on a ruler), and these 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. Using the Gridlines object, you can manipulate / format all gridlines together (not single gridline at a time) and turn on or off either all or none, and these gridlines can exist only for the primary axis group.
| Commonly used Properties of the Axis object:
|
||
| Property | Syntax | Description |
| AxisGroup Property | objAxis.AxisGroup | Returns a value per XlAxisGroup Enumeration (value 1 for xlPrimary, or value 2 for xlSecondary), specifying the AxisGroup for the Axis object. |
| Type Property | objAxis.Type | Returns a value per XlAxisType Enumeration (value 1 for xlCategory, value 2 for xlValue, or value 3 for xlSeriesAxis), specifying the Axis Type for the Axis object. Note that for the horizontal axis of scatter charts, Axis Type returned will be xlCategory. |
| HasTitle Property | objAxis.HasTitle | This property uses a Boolean value – the AxisTitle object exists and can be used only if the HasTitle property (of the Axis object) is True viz. set to True for the axis title to be visible: objAxis.HasTitle = True. To add an axis label to the secondary value axis of a chart:
Sheets(“Sheet1”).ChartObjects(1).Chart.Axes(xlValue, xlSecondary).HasTitle = True. |
| AxisTitle Property | objAxis.AxisTitle | Returns a AxisTitle object which represents the axis title. The title for a specified chart axis is accessed & manipulated through the properties & methods of the AxisTitle object. The AxisTitle object exists and can be used only if the HasTitle property (of the Axis object) is True viz. set to True for the axis title to be visible: objAxis.HasTitle = True. Refer below for summarized Properties & Methods of the AxisTitle object. |
| Format Property | objAxis.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. |
| Border Property | objAxis.Border | Returns a Border object, to manipulate a chart element’s border (ie. Axis border). |
| Left Property | objAxis.Left | Returns the distance (Read-only), in points, from the left edge of the chart area to the left edge of the Axis. |
| Top Property | objAxis.Top | Returns the distance (Read-only), in points, from the top edge of the Axis to the top edge of the chart area. |
| Height Property | objAxis.Height | Returns the height (Read-only), in points, of the Axis object. |
| Width Property | objAxis.Width | Returns the width (Read-only), in points, of the Axis object. |
| Parent Property | objAxis.Parent | returns the parent object for the Axis viz. returns the chart name:
MsgBox Sheets(“Sheet1”).ChartObjects(1).Chart.Axes(xlValue, xlPrimary).Parent.Name |
| CategoryNames Property | objAxis.CategoryNames | Using this property the category names for a category axis, are set or returned as a text array. You can set the category names for a chart to the values contained in a range of cells (ie. Range object) or to a text array containing category names. |
| TickLabels Property | objAxis.TickLabels | 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). Refer below for summarized Properties & Methods of the TickLabels object. |
| TickLabelPosition Property | objAxis.TickLabelPosition | Sets or returns the tick-mark labels position on a chart axis, where the position is specified using XlTickLabelPosition constants – xlTickLabelPositionNone (value -4142) indicates no tick marks; xlTickLabelPositionLow (value -4134) indicates tick marks position at bottom or left side of the chart; xlTickLabelPositionHigh (value -4127) indicates tick marks position at top or right side of the chart; xlTickLabelPositionNextToAxis (value 4) indicates tick marks position at next to axis when axis is not at either side of the chart. |
| TickLabelSpacing Property | objAxis.TickLabelSpacing | For the category & series axes, this property sets or returns the interval between tick-mark lables (ie. the number of categories or series between tick-mark labels), using a long value between 1 & 31999. For the value axis, Excel automatically calculates the tick-mark label spacing. |
| TickLabelSpacingIsAuto Property | objAxis.TickLabelSpacingIsAuto | Sets or returns a Boolean value for this property where a True value (Default) enables Excel to automatically set the tick label spacing (Read-write). To return the current setting for tick label spacing & if it is automatic or not, use the the TickLabelSpacing property. |
 
Sub AxisTickLabels() With Sheets(“Sheet1”).ChartObjects(1).Chart.Axes(xlCategory) ‘REFER IMAGE 1a ‘REFER IMAGE 1b End With End Sub
|
||
| HasDisplayUnitLabel Property | objAxis.HasDisplayUnitLabel | This property uses a Boolean value – True (Default) indicates that a unit label for the axis (a DisplayUnitLabel object) is displayed. For a value axis whose DisplayUnit property has been set, & unless the HasDisplayUnitLabel property is set to False, the DisplayUnitLabel (or DisplayUnitCustom) property returns the DisplayUnitLabel object with whose properties & methods you can manipulate the unit label. |
| DisplayUnit Property | objAxis.DisplayUnit | For the value axis, this property sets or returns a numeric scale used for axis values, as per constants defined in XlDisplayUnit Enumeration – xlNone (Default); xlHundreds (value -2); xlThousands (value -3); xlTenThousands (value -4); xlHundredThousands (value -5); xlMillions (value -6); xlTenMillions (value -7); xlHundredMillions (value -8); xlThousandMillions (value -9); xlMillionMillions (value -10); xlCustom. When you set this property, a DisplayUnitLabel object is added which represents a unit label for that axis. Using numeric scaling makes tick mark labels for value axis more readable because smaller numeric values will be used instead of large numbers at all tick marks on the axis. Example. objAxis.DisplayUnit = xlThousands sets the units displayed on a value axis to thousands – this numerically scales axis values to thousands so that a value of 50,000 will display as 50 (Refer Image 2b). |
| DisplayUnitLabel Property | objAxis.DisplayUnitLabel | For a value axis whose DisplayUnit property has been set, & unless the HasDisplayUnitLabel property is set to False, this property returns the DisplayUnitLabel object which represents a unit label on the specified axis, & using the object’s properties & methods you can manipulate the unit label. |
 
‘refer Image 2b ‘using the DisplayUnit Property of the Axis object, to set the unit label for the primary value axis ‘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 “‘000” for the primary value axis End With End With
|
||
| DisplayUnitCustom Property | objAxis.DisplayUnitCustom | Use the DisplayUnitCustom property to set or return the unit for the numeric scale wherein DisplayUnit property of a value axis is set to xlCustom. You can set the unit value as Double data type, between 0 to 1e+308. Example. to set the units displayed on the value axis to a scale of 5000 & in this case a value of 50,000 will be scaled & displayed as 10 (refer Image 2c). |
‘refer Image 2c .DisplayUnit = xlCustom .Caption = “‘5000“ End With End With
|
||
| AxisBetweenCategories Property | objAxis.AxisBetweenCategories | Uses a Boolean value – True setting will have the value axis to cross the category axis between categories. This property is valid only for category axes, & does not apply to value axis or to 3-D charts. |
| Crosses Property | objAxis.Crosses | Sets or returns the point on an axis (category or value axes) where the other axis crosses, as per specified XlAxisCrosses constants – xlMinimum (value 4: other axis crosses at the minimum value, & for category axis the value axis – which is the other axis – crosses at the first category); xlMaximum (value 2: other axis crosses at the maximum value, & for category axis the other value axis crosses at the last category); xlAxisCrossesCustom (value -4114: the axis crossing point is specified by CrossesAt property); xlAxisCrossesAutomatic (value -4105: the axis crossing point is set by Excel). We have noticed that using xlMinimum for value axis may not always be reliable in determining the crossing point at the minimum axis value – refer below example. This property is not valid for for radar charts, & is applicable only to a value axis in 3-D charts, wherein it refers to where the plane (defined by the category axes) crosses the value axis. |
| CrossesAt Property | objAxis.CrossesAt | For a value axis, this property sets or returns the point (Double value) where the category axis crosses. When this property is set, the Crosses property automatically gets set to xlAxisCrossesCustom. This property is not valid for for radar charts, & is applicable only to a value axis in 3-D charts, wherein it refers to where the plane (defined by the category axes) crosses the value axis. |
  
Sub AxisCrosses() With Sheets(“Sheet1”).ChartObjects(1).Chart ‘sets the value axis to cross the category axis where the axis crossing point is set by Excel – refer Image 3a ‘format value axis border – medium thickness & green color ‘sets the value axis to cross the category axis at the last category – refer Image 3b ‘sets the value axis to cross the category axis at the first category – refer Image 3a ‘sets the category axis to cross where the value axis’ value is minimum – using xlMinimum for value axis may not always be reliable, & here the category axis crosses at 0 even though value axis has negative numbers – refer Image 3a ‘when “Crosses At” property is set, the Crosses property automatically gets set to xlAxisCrossesCustom End With End Sub
|
||
| MajorTickMark Property / MinorTickMark Property | objAxis.MajorTickMark / objAxis.MinorTickMark | Sets or returns the type of major / minor tick mark used for an axis, per defined XlTickMark constants – xlTickMarkNone (value -4142: no tickmark), xlTickMarkInside (value 2: tickmark is inside the axis), xlTickMarkOutside (value 3: tickmark is outside the axis), xlTickMarkCross (value 4: tickmark crosses the axis). |
| MajorGridlines Property / MinorGridlines Property | objAxis.MajorGridlines / objAxis.MinorGridlines | Use the MajorGridlines Property to return a Gridlines object representing the major gridlines for an axis, whereas the MinorGridlines property returns a GridLines object representing the minor gridlines. Using the Gridlines object, you can manipulate / format all gridlines together (not a single gridline at a time) and turn on or off either all or none, and these gridlines can exist only for the primary axis group. |
| HasMajorGridlines Property | objAxis.HasMajorGridlines | This property uses a Boolean value – True displays the major gridlines for an axis (Read-write). Note that gridlines can exist only for the primary axis group. |
| HasMinorGridlines Property | objAxis.HasMinorGridlines | This property uses a Boolean value – True displays the minor gridlines for an axis (Read-write). Note that gridlines can exist only for the primary axis group. |
Sub Axis_TickMarks_Gridlines() ‘Embedded Chart ‘Value Axis ‘display MajorGridlines for value axis ‘turn off MinorGridlines ‘major tickmark is outside the value axis End With ‘Category Axis ‘display MajorGridlines for category axis ‘display MinorGridlines for category axis ‘major tickmark crosses the category axis End With End With End Sub
|
||
| CategoryType Property | objAxis.CategoryType | Sets or returns the type of category axis (not valid for value axis), per XlCategoryType Enumeration: xlCategoryScale (value 2) – data is grouped in Axis by an arbitrary set of categories; xlTimeScale (value 3) – data is grouped in Axis on a time scale; xlAutomaticScale (value -4105) – axis type is controlled by Excel. |
| BaseUnit Property | objAxis.BaseUnit | Use this property to set or return a category axis’ base unit of time, except when category axis type is specified as xlCategoryScale. Use the XlTimeUnit Enumeration to specify base unit of time: xlDays (value 0) to set unit of time to Days, xlMonths (value 1) to set unit of time to Months, xlYears (value 2) to set Years as the base unit of time. |
| BaseUnitIsAuto Property | objAxis.BaseUnitIsAuto | This property uses a Boolean value – a True value (Default) enables Excel to automatically choose a base unit of time for a category axis. |
| TickMarkSpacing Property | objAxis.TickMarkSpacing | For the category & series axes, this property sets or returns the interval between tick marks (ie. the number of categories or series between tick marks), using a long value between 1 & 31999. To set the interval between tick marks for the value axis, use the MajorUnit Property & MinorUnit Property. |
| MajorUnit Property | objAxis.MajorUnit | Sets or returns the major units, which is the interval between tick marks, for the value axis. The MajorUnitIsAuto property is automatically set to False when you set the MajorUnit property. Use the TickMarkSpacing Property to set the interval (ie. the number of categories) between tick marks on the category axis. |
| MajorUnitScale Property | objAxis.MajorUnitScale | When the CategoryType property is set to xlTimeScale, use this property to set or return the category axis’ major unit scale value (ie. the interval between tick marks), as per the XlTimeUnit constants – xlDays, xlMonths, xlYears. |
| MajorUnitIsAuto Property | objAxis.MajorUnitIsAuto | A True value (Default) enables Excel to automatically calculate the major units for the value axis. This property is automatically set to False when you set the MajorUnit property. |
| MinorUnit Property | objAxis.MinorUnit | Sets or returns the minor units, which is the interval between minor tick marks, for the value axis. The MinorUnitIsAuto property is automatically set to False when you set the MinorUnit property. Use the TickMarkSpacing Property to set the interval (ie. the number of categories) between tick marks on the category axis. |
| MinorUnitScale Property | objAxis.MinorUnitScale | When the CategoryType property is set to xlTimeScale, use this property to set or return the category axis’ minor unit scale value (ie. the interval between tick marks), as per the XlTimeUnit constants – xlDays, xlMonths, xlYears. |
| MinorUnitIsAuto Property | objAxis.MinorUnitIsAuto | This property uses a Boolean value – a True value (Default) enables Excel to automatically calculate the minor units for the value axis. This property is automatically set to False when you set the MinorUnit property. |
| MaximumScale Property / MinimumScale Property | objAxis.MaximumScale / objAxis.MinimumScale | Sets or returns the maximum / minimum value (Double data type), for the value axis. The MaximumScaleIsAuto Property / MinimumScaleIsAuto Property, is automatically set to False when you set the maximum / minimum value. |
| MaximumScaleIsAuto Property / MinimumScaleIsAuto Property | objAxis.MaximumScaleIsAuto / objAxis.MinimumScaleIsAuto | A True value (Default) enables Excel to automatically calculate the maximum / minimum value for the value axis. This property is automatically set to False when you set the MaximumScale / MinimumScale Property. |
Sub CategoryAxis_TimeScale() ‘Embedded Chart ‘sets the category axes to display ‘primary category axis ‘set the axis title to be visible End With ‘secondary value axis ‘set the minimum and maximum values for the value axis End With End With End Sub
|
||
Sub CategoryAxis_AutomaticScale_ValueAxis() ‘Embedded Chart ‘sets the category axes to display ‘set the category names for a category axis, to a text array ‘use the TickLabels property to return a TickLabels Object representing the tick-mark labels, and with this object you can format all tick-mark labels together as a unit ‘set the text orientation for tick lables End With ‘set the interval between tick marks ie. the number of categories or series between tick marks End With ‘secondary value axis ‘set the minimum and maximum values for the secondary value axis End With End With End Sub
|
||
| Commonly used Methods of the Axis object:
|
||
| Method | Syntax | Description |
| Delete Method | objAxis.Delete | deletes the axis object |
| Select Method | objAxis.Select | selects the axis object |
Properties & Methods of the AxisTitle object, summarized: Caption Property – objAxisTitle.Caption – sets or returns the axis title text; Text Property – objAxisTitle.Text – sets or returns the axis title text; Characters Property – objAxisTitle.Characters(Start, Length) – represents a range of characters within the text string of axis title & enables formatting these specific characters by specifying the first (Start) character & the number of characters (Length); Format Property – objAxisTitle.Format – returns a ChartFormat object which contains the line, fill & effect formatting for a chart element; HorizontalAlignment Property – objAxisTitle.HorizontalAlignment – sets or returns the horizontal alignment of axis title using constants xlLeft, xlRight, xlCenter, xlJustify, xlDistributed; VerticalAlignment Property – objAxisTitle.VerticalAlignment – sets or returns the vertical alignment of axis title using constants xlTop, xlBottom, xlCenter, xlJustify, xlDistributed; ReadingOrder Property – objAxisTitle.ReadingOrder – sets or returns the reading order per the constants – xlRTL (right-to-left), xlLTR (left-to-right), or xlContext; IncludeInLayout Property – objAxisTitle.IncludeInLayout – use a Boolean value to determine whether a layout space is occupied by axis title during a chart layout wherein True is the default value; Left Property – objAxisTitle.Left – sets or returns the distance, in points, from the left edge of the chart area to the left edge of the Axis Title; Top Property – objAxisTitle.Top – sets or returns the distance, in points, from the top edge of the chart area to the top edge of the Axis Title; Orientation Property – objAxisTitle.Orientation – sets or returns the axis title’s text orientation as an integer value from -90 to 90 degrees or per the constants xlDownward, xlUpward, xlHorizontal, xlVertical. Position Property – objAxisTitle.Position – sets or returns the position of the axis title on the chart as per constants xlChartElementPositionAutomatic (-4105), xlChartElementPositionCustom (-4114). Shadow Property – objAxisTitle.Shadow – sets or returns a Boolean value determining a shadow for the axis title. The AxisTitle has 2 methods: Delete Method – objAxisTitle.Delete – deletes the axis title; Select Method – objAxisTitle.Select – selects the axis title.
Properties & Methods of the TickLabels object, summarized: Alignment Property – objTickLabels.Alignment – sets or returns (long value) the alignment for tick-mark labels; Depth Property – objTickLabels.Depth – returns the number of levels of category tick labels; MultiLevel Property – objTickLabels.MultiLevel – use a boolean value to set an axis as multilevel; Font Property – objTickLabels.Font – returns a Font object, to manipulate tick labels’ font attributes viz. font name, font size, font color, …; Format Property – objTickLabels.Format – returns a ChartFormat object which contains the line, fill & effect formatting for a chart element; NumberFormat Property – objTickLabels.NumberFormat – sets or returns the format code (similar to the Format Codes option in the Format Cells dialog box) using a String value; NumberFormatLinked Property – objTickLabels.NumberFormatLinked – Boolean, read-write, use the True setting to link the number format to the cells so that the number format in the labels changes when changes are made in the cells; NumberFormatLocal Property – objTickLabels.NumberFormatLocal – sets or returns the format code as a String in the language of the user; Offset Property – objTickLabels.Offset – sets or returns the distance as a long value (between 0 to 1000 percent, relative to the axis label’s font size) 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; ReadingOrder Property – objTickLabels.ReadingOrder – sets or returns the reading order per the constants – xlRTL (right-to-left), xlLTR (left-to-right), or xlContext; Orientation Property – objTickLabels.Orientation – sets or returns the tick-mark labels’ text orientation as an integer value from -90 to 90 degrees or per the constants xlTickLabelOrientationAutomatic, xlTickLabelOrientationDownward, xlTickLabelOrientationUpward, xlTickLabelOrientationHorizontal, xlTickLabelOrientationVertical. The TickLabels object has 2 methods: Delete Method – objTickLabels.Delete – deletes the tick-mark labels; Select Method – objTickLabels.Select – selects the tick-mark labels.
Child Objects for the Axis Object: Above we have discussed: the ChartFormat object – use the Format Property – objAxis.Format – to return the ChartFormat object which contains the line, fill & effect formatting for the chart axis. Some other child objects discussed above are: AxisTitle Object; DisplayUnitLabel Object; Gridlines Object; TickLabels Object. Other child objects often used with chart axis include the Border Object – use the Border Property – objAxis.Border – to return a Border object, to manipulate a chart element’s border.
Example: refer to & manipulate chart axis, create new series & axis – refer Images 6a (source data range), 6b (existing chart before running code) & 6c (chart after running this code)
Sub ChartAxis_1()
‘refer to & manipulate chart axis, create new series & axis
‘refer Images 6a (source data range), 6b (existing chart before running code) & 6c (chart after running this code)
With Sheets(“Sheet2”).ChartObjects(1).Chart
‘refers to primary value axis
If .HasAxis(2) Then
MsgBox .Axes(2).AxisTitle.Text
End If
‘use the NewSeries Method of the SeriesCollection object, to create a new series and add to the chart as a member of its SeriesCollection
With .SeriesCollection.NewSeries
‘set the axis group (Secondary axis group) for the specified series using the Series.AxisGroup Property
‘the Secondary Value axis is displayed without using the HasAxis property of the Chart object
.AxisGroup = xlSecondary
‘name the series
.Name = Sheets(“Sheet1”).Range(“E1”)
‘using the Values property of the Series object, set Y values for new series
.Values = Sheets(“Sheet1”).Range(“E2:E9”)
End With
‘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
.MinimumScale = 40000
.MaximumScale = 80000
.MajorUnit = 5000
‘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 – set the text for axis title
.AxisTitle.Caption = “Profit“
End With
‘refers to primary category axis
If .HasAxis(1) Then
MsgBox .Axes(1).AxisTitle.Text
Else
MsgBox “primary category axis does not exist”
‘turn on / display category axis
.HasAxis(xlCategory, xlPrimary) = True
With .Axes(xlCategory, xlPrimary)
.CategoryNames = Sheets(“Sheet1”).Range(“A2:B9”)
.TickLabels.Font.Bold = True
‘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 – set the text for axis title, set the font to Bold Arial 10 point & red color
With .AxisTitle
.Caption = “Yr-Qtr“
.Font.Name = “Arial“
.Font.Size = 10
.Font.Bold = True
.Font.Color = RGB(255, 0, 0)
End With
End With
End If
‘refers to secondary category axis
If .HasAxis(1, 2) Then
MsgBox .Axes(1, 2).AxisTitle.Text
‘turn off the secondary category axis ie. the category axis will no exist / display
.HasAxis(xlCategory, xlSecondary) = False
MsgBox “secondary category axis turned off”
Else
MsgBox “secondary category axis does not exist”
End If
End With
End Sub
Example: Custom NumberFormat for Chart Axis Tick Lables, Data Labels – refer Image 7
Sub ChartAxis_CustomNumberFormat()
‘Custom NumberFormat for Chart Axis Tick Lables, Data Labels – refer Image 7
With Sheets(“Sheet1”).ChartObjects(1).Chart
‘primary value axis
With .Axes(xlValue)
.TickLabels.NumberFormat = “[Cyan][<=60000]\L\o\w #,##0;[Magenta][>100000]\H\i\g\h #,##0;\A\v\g #,##0;“
.TickLabels.Font.Bold = True
End With
‘secondary value axis
With .Axes(xlValue, xlSecondary)
.TickLabels.Font.Bold = True
.TickLabels.NumberFormat = “[Red][<2000]$#,##0;[Green][>=2000]$#,##0“
End With
‘category axis
With .Axes(xlCategory)
.TickLabels.Font.Color = vbBlue
.TickLabels.Font.Bold = True
.TickLabels.Font.Italic = True
End With
‘data labels for series no 2
With .SeriesCollection(2)
.DataLabels.Font.Bold = True
.DataLabels.NumberFormat = “[Red][<2000]$#,##0;[Green][>=2000]$#,##0“
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