Simple Procedures to format charts in MS Excel

ChartFormatting.cls

Option Explicit
Private objChart As ChartObject
Enum AxisType
    Primary = 1
    Secondary = 2
End Enum
Enum BorderType
    Hairline = 1
    Thin = 2
    Medium = -4138
    Thick = 4
End Enum
Enum BorderWeight
    Continuous = 1
    DashDot = 4
    DashDotDot = 5
    Dot = -4118
    Dash = -4115
    DoubleStyle = -4119
    LineStyleNone = -4142
    SlantDashDot = 13
End Enum
Enum AxesType
    Category = 1
    Value = 2
End Enum
Sub SetChartObject(strSheetName As String, strChartName As String)
 
If strSheetName = "" Or strChartName = "" Then

    MsgBox "Sheet name and chart name were not provided."
    Exit Sub
    
Else
    
    On Error Resume Next
    Set objChart = Sheets(strSheetName).ChartObjects(strChartName)
    
    If objChart Is Nothing Then
    
        MsgBox "Error. Chart object was not created!", vbCritical
        Exit Sub
    
    End If
    
    Err.Clear
    
    On Error GoTo 0
    
End If
 
End Sub
Sub FormatDataSeriesLabels(varSeriesNo As Variant, strFontName As String, intFontSize As Integer, blnBold As Boolean, varBGColor As Variant, varBorderColor As Variant)
 
If objChart Is Nothing Then

    MsgBox "Error. Chart object was not created!", vbCritical
    Exit Sub
    
End If
 
With objChart.Chart.SeriesCollection(varSeriesNo)
 
    .DataLabels.Font.Name = strFontName
    .DataLabels.Font.Size = intFontSize
    .DataLabels.Font.Bold = blnBold
    .DataLabels.Interior.Color = RGB(varBGColor(0), varBGColor(1), varBGColor(2))
    .DataLabels.Border.Color = RGB(varBorderColor(0), varBorderColor(1), varBorderColor(2))
 
End With
 
End Sub
Sub SeriesAxisGroup(strSeriesNo As String, GroupType As XlAxisGroup)
 
With objChart.Chart.SeriesCollection(strSeriesNo)
 
    .AxisGroup = GroupType
 
End With
 
End Sub
Sub FormatValueAxis(ePrimarySecondary As AxesType, strFontName As String, intFontSize As Integer, varColorArr As Variant, blnBold As Boolean)
  
With objChart.Chart
 
    .Axes(xlValue, ePrimarySecondary).TickLabels.Font.Size = intFontSize
    .Axes(xlValue, ePrimarySecondary).TickLabels.Font.Name = strFontName
    .Axes(xlValue, ePrimarySecondary).TickLabels.Font.Bold = blnBold
    .Axes(xlValue, ePrimarySecondary).TickLabels.Font.Color = RGB(varColorArr(0), varColorArr(1), varColorArr(2))
    
End With
 
End Sub
Sub FormatCategoryAxis(strFontName As String, intFontSize As Integer, varColorArr As Variant, blnBold As Boolean)
 
With objChart.Chart
 
    .Axes(xlCategory).TickLabels.Font.Size = intFontSize
    .Axes(xlCategory).TickLabels.Font.Name = strFontName
    .Axes(xlCategory).TickLabels.Font.Bold = blnBold
    .Axes(xlCategory).TickLabels.Font.Color = RGB(varColorArr(0), varColorArr(1), varColorArr(2))
    
End With
 
End Sub
Sub DisplayPrimaryAxis()
 
On Error Resume Next
Err.Clear
objChart.Chart.SetElement (msoElementPrimaryValueAxisShow)
On Error GoTo 0
 
End Sub
Sub DisplaySecondaryAxis()
 
On Error Resume Next
Err.Clear
objChart.Chart.SetElement (msoElementSecondaryValueAxisShow)
On Error GoTo 0
 
End Sub
Sub DisplayAllAxis()
 
With objChart
 
    On Error Resume Next
    .HasAxis(xlCategory, xlPrimary) = True
    .HasAxis(xlCategory, xlSecondary) = True
    .HasAxis(xlValue, xlPrimary) = True
    .HasAxis(xlValue, xlSecondary) = True
    On Error GoTo 0
     
End With
 
End Sub
Sub DataLabelDeleteIf(varValue As Variant)
 
Dim s As Series
Dim p As Point
 
On Error Resume Next
For Each s In objChart.Chart.SeriesCollection
    For Each p In s.Points
        If CStr(p.DataLabel.Text) = CStr(varValue) Then
            p.DataLabel.Delete
        End If
    Next p
Next s
On Error GoTo 0
 
End Sub
Sub ChartTitle(blnDisplayTitle As Boolean, dblLeft As Double, dblTop As Double, dblWidth As Double, dblHeight As Double, varBackgroundRGB As Variant, varFontRGB As Variant, intFontSize As Integer, blnBold As Boolean)
 
With objChart.Chart
 
    If blnDisplayTitle Then
    
        .HasTitle = True
        .ChartTitle.Left = dblLeft
        .ChartTitle.Top = dblTop
        '.ChartTitle.Width = dblWidth
        '.ChartTitle.Height = dblHeight
        .ChartTitle.Font.Color = RGB(varFontRGB(0), varFontRGB(1), varFontRGB(2))
        .ChartTitle.Font.Size = intFontSize
        .ChartTitle.Font.Bold = blnBold
        '.ChartTitle.Fill.ForeColor = RGB(varBackgroundRGB(0), varBackgroundRGB(1), varBackgroundRGB(2))
    
    Else
    
        .HasTitle = False
    
    End If
 
End With
 
End Sub
Sub ChartBorder(varRGB As Variant, eBorderType As BorderType, eBorderWeight As BorderWeight)
 
With objChart.Chart.ChartArea
 
    .Border.Color = RGB(varRGB(0), varRGB(1), varRGB(2))
    .Border.LineStyle = eBorderType
    .Border.Weight = eBorderWeight
 
End With
 
End Sub
Sub SetChartObjectDimensions(dblLeft As Double, dblTop As Double, dblWidth As Double, dblHeight As Double)
 
On Error Resume Next
With objChart
 
    .Left = dblLeft
    .Top = dblTop
    .Width = dblWidth
    .Height = dblHeight
     
End With
On Error GoTo 0
 
End Sub
Sub SetPlotAreaDimensions(dblLeft As Double, dblTop As Double, dblWidth As Double, dblHeight As Double)
 
On Error Resume Next
With objChart.Chart.PlotArea
    
    .Left = dblLeft
    .Top = dblTop
    .Width = dblWidth
    .Height = dblHeight
     
End With
On Error GoTo 0
 
End Sub
Sub DisplayAxisTitles(ePrimarySecondary As AxesType, eType As AxisType, blnDisplay As Boolean, strFontName As String, intAngle As Integer, intFontSize As Integer, varFontRGB As Variant, blnBold As Boolean, Optional strTitle As String)
 
With objChart.Chart.Axes(ePrimarySecondary, eType)

    .HasTitle = blnDisplay
    If strTitle <> "" Then .AxisTitle.Characters.Text = strTitle
    .AxisTitle.Font.Color = RGB(varFontRGB(0), varFontRGB(1), varFontRGB(2))
    .AxisTitle.Font.Size = intFontSize
    .AxisTitle.Font.Name = strFontName
    .AxisTitle.Font.Bold = blnBold
    .AxisTitle.Orientation = intAngle

End With
 
End Sub
Sub FormatAxis(ePrimarySecondary As AxesType, eType As AxisType, strFontName As String, intFontSize As Integer, varFontRGB As Variant, blnBold As Boolean)

With objChart.Chart.Axes(ePrimarySecondary, eType).TickLabels

    .Font.Color = RGB(varFontRGB(0), varFontRGB(1), varFontRGB(2))
    .Font.Size = intFontSize
    .Font.Name = strFontName
    .Font.Bold = blnBold
    .Parent.Border.Color = RGB(varFontRGB(0), varFontRGB(1), varFontRGB(2))

End With

End Sub
Sub AxisSceleValue(ePrimarySecondary As AxesType, eType As AxisType, dblMin As Double, dblMax As Double, dlbUnit As Double)

With objChart.Chart.Axes(eType, ePrimarySecondary)

    .MaximumScale = dblMax
    .MinimumScale = dblMin
    .MajorUnit = dlbUnit

End With

End Sub
Sub DisplayValueAxisTitles()
 
On Error Resume Next    'if secondary not required
objChart.Chart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
objChart.SetElement (msoElementSecondaryValueAxisTitleAdjacentToAxis)
On Error GoTo 0
 
End Sub
Sub DataLabelFormat(varSeriesNo As Variant, intPoint As Integer, strFormat As String, blnBold As Boolean, strFontName As String, intFontSize As Integer, varRGB As Variant)
 
On Error Resume Next
With objChart.Chart.SeriesCollection(varSeriesNo).Points(intPoint).DataLabel
 
    .NumberFormat = strFormat
    .Font.Bold = blnBold
    .Font.Name = strFontName
    .Font.Size = intFontSize
    .Font.Color = RGB(varRGB(0), varRGB(1), varRGB(2))
    
End With
    
On Error GoTo 0
 
End Sub
Sub DataLabelsDisplay(varSeriesNo As Variant, bolDisplay As Boolean)
 
On Error Resume Next
objChart.Chart.SeriesCollection(varSeriesNo).HasDataLabels = bolDisplay
On Error GoTo 0
 
End Sub
Sub DataLabelPointPosition(varSeriesNo As Variant, intPoint As Integer, intPosition As Integer)
 
'xlLabelPositionCenter      Data label centered on data point or inside bar or pie.
'xlLabelPositionAbove       Data label above point.
'xlLabelPositionBelow       Data label below point.
'xlLabelPositionLeft        Data label positioned at bottom of bar or pie.
'xlLabelPositionRight       Data label positioned at top of bar or pie.
'xlLabelPositionOutsideEnd  Data label positioned at top of bar or pie.
'xlLabelPositionInsideEnd   Data label positioned arbitrarily.
'xlLabelPositionInsideBase  Data label positioned arbitrarily.
'xlLabelPositionBestFit     Excel controls position of data label.
'xlLabelPositionMixed       Data label positioned at bottom of bar or pie.
'xlLabelPositionCustom      Data label centered on data point or inside bar or pie.
 
On Error Resume Next
objChart.Chart.SeriesCollection(varSeriesNo).Points(intPoint).DataLabel.Position = intPosition
On Error GoTo 0
 
End Sub
Sub DataLabelPosition(varSeriesNo As Variant, intPosition As Integer)
 
On Error Resume Next
objChart.Chart.SeriesCollection(varSeriesNo).DataLabels.Position = intPosition
On Error GoTo 0
 
End Sub
Sub MajorGridlinesVisibility(blnVisibility As Boolean, eType As AxisType)
  
On Error Resume Next
objChart.Chart.Axes(eType, xlPrimary).HasMajorGridlines = blnVisibility
On Error GoTo 0
 
End Sub
Sub ChangeGapWidth(intGap As Integer)
 
objChart.Chart.ChartGroups(1).GapWidth = intGap
 
End Sub
Sub VerticalValueAxisVisibility(blnVisible As Boolean)
 
On Error Resume Next
objChart.Chart.HasAxis(xlValue) = blnVisible
On Error GoTo 0
 
End Sub
Sub HorizontalCategoryAxisVisibility(blnVisible As Boolean)
 
On Error Resume Next
objChart.Chart.HasAxis(xlCategory) = blnVisible
On Error GoTo 0
 
End Sub
Sub SeriesForeColor(varSeriesNo As Variant, blnVisible As Boolean, varRGB As Variant)
 
With objChart.Chart.SeriesCollection(varSeriesNo).Format.Fill

    .Visible = blnVisible
    .ForeColor.RGB = RGB(varRGB(0), varRGB(1), varRGB(2))
    .Solid
    
End With
 
End Sub
Sub PointForeColor(varSeriesNo As Variant, varPointNo As Variant, blnVisible As Boolean, varRGB As Variant)
 
With objChart.Chart.SeriesCollection(varSeriesNo).Points(varPointNo).Format.Fill

    .Visible = blnVisible
    .ForeColor.RGB = RGB(varRGB(0), varRGB(1), varRGB(2))
    .Solid
    
End With
 
End Sub
Sub SeriesBorderColor(varSeriesNo As Variant, varRGB As Variant)
 
objChart.Chart.SeriesCollection(varSeriesNo).Format.Line.ForeColor.RGB = RGB(varRGB(0), varRGB(1), varRGB(2))
 
End Sub
Property Get CountSeries() As Integer
 
CountSeries = objChart.Chart.SeriesCollection.Count
 
End Property
Property Get CountPoints(intSeriesNo As Integer) As Integer
 
CountPoints = objChart.Chart.SeriesCollection(intSeriesNo).Points.Count
 
End Property
Sub DisplayLabelsFromRange(strRangeAddress As String, intSeriesNo As Integer)

With objChart.Chart.FullSeriesCollection(intSeriesNo)

    .DataLabels.Delete
    .ApplyDataLabels
    .DataLabels.ShowValue = False
    .DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, "=Chart!" & strRangeAddress, 0
    .DataLabels.ShowRange = True
    
End With

End Sub