VBA add a battery chart
0. Quick guide - use VBA to add a battery chart to WS
In this module:
- 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.
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.
- Download the Excel file for this module: xlf-battery-chart.xlsm [29 KB]
- Development platform: Office 365 ProPlus Excel 64 bit.
- Published: 9th February 2019
- Revised: Friday 24th of February 2023 - 03:12 PM, Pacific Time (PT)