VBA add a battery chart


0. Quick guide - use VBA to add a battery chart to WS


In this module:

  1. VBA code - to add a battery chart to a worksheet (WS) as a customised 3D stacked column chart

1. Battery chart


The demonstration chart is shown in figure 1.


battery chart demo
Fig 1: - a WS battery chart with a demonstration Scroll Bar control

2. VBA code to add battery chart


Code 1 uses the Shapes.AddChart2 method with parameters by position (code 1 line 7).



Code 1: Sub xlfAddBatteryChart procedure adds a chart element to the WS (see figure 1) 
Sub xlfAddBatteryChart()
Dim ac As String

ac = ActiveCell.Address

Range("L2:L6").Select
ActiveSheet.Shapes.AddChart2(286, xl3DColumnStacked).Select

    With ActiveChart
        .PlotBy = xlRows

        .HasTitle = False
        .HasAxis(xlValue) = False
        .HasAxis(xlCategory) = False
        .Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse

        .Parent.Width = 100
        .GapDepth = 0
        .ChartGroups(1).GapWidth = 0

        .BarShape = xlCylinder
        .ChartArea.Format.ThreeD.RotationX = 0
        .ChartArea.Format.ThreeD.RotationY = 100

    ' Base ==================
            With .FullSeriesCollection(1).Format.Fill.ForeColor
                .ObjectThemeColor = msoThemeColorBackground1
                .Brightness = -0.15
            End With

    ' Value =================
            With .FullSeriesCollection(2)
                .Format.Fill.ForeColor.RGB = RGB(55, 95, 145)
                .Format.Fill.Transparency = 0.05
                .ApplyDataLabels
                With .DataLabels.Format.TextFrame2.TextRange.Font.Fill
                    .Visible = msoTrue
                    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                    .ForeColor.TintAndShade = 0
                    .ForeColor.Brightness = 0
                    .Transparency = 0
                    .Solid
                End With
                .DataLabels.Format.TextFrame2.TextRange.Font.Size = 10.5
                .Points(1).DataLabel.Width = 30
            End With

    ' Remainder =============
            With .FullSeriesCollection(3).Format.Fill
                .ForeColor.RGB = RGB(55, 95, 145)
                .Transparency = 0.7
            End With

    ' Cap ===================
            With .FullSeriesCollection(4).Format.Fill.ForeColor
                .ObjectThemeColor = msoThemeColorBackground1
                .Brightness = -0.15
            End With

    End With
        Debug.Print ActiveChart.Parent.Height
        Range(ac).Select

End Sub

Code 1 requires that the Base … Cap values be in column format as used by the PlotBy = xlRows statement in line 10.