Creating Stacked Column Chart with Arrays in VBA

Question

I am currently trying to created a stacked column chart in Excel, using arrays that I have pre-built. The arrays do not reference any ranges on a sheet, but rather represent calculations of that data.

The issue I am having is when creating the stacked column chart, the data is not vertically stacked on the same column, but rather the second data set is stacked vertically adjacent to the first data set. I will attach an image further down but for now let me show you my code, please not that in this sub routine I actually create 4 different charts, but only one of them needs to be a stacked column, so I will reference the stacked column code below:

Sub buildCharts()

    Dim myChart as Shape

    Set myChart = wsRawData.Shapes.AddChart2(Left:=792, Top:=0, Width:=264, Height:=192)

    With myChart.Chart

        'load the data
        .SeriesCollection.NewSeries
        .SeriesCollection(1).Values = myArray1
        .SeriesCollection.NewSeries
        .SeriesCollection(2).Values = myArray2
        .SeriesCollection.NewSeries
        .SeriesCollection(3).Values = myArray3

        'x-axis
        .SeriesCollection(1).XValues = Array("J", "F", "M", "A", "M", "J", "J", "A", "S", "O", "N", "D")

        'set the chart type
        .FullSeriesCollection(1).ChartType = xlColumnStacked
        .FullSeriesCollection(1).AxisGroup = 1
        .FullSeriesCollection(2).ChartType = xlColumnStacked
        .FullSeriesCollection(2).AxisGroup = 1
        .FullSeriesCollection(3).ChartType = xlLine
        .FullSeriesCollection(3).AxisGroup = 1
        .FullSeriesCollection(3).Format.Line.Weight = 1.25

        'edit
        .ChartStyle = 209
        .HasTitle = True
        .chartTitle.Text = "My Chart"
        .ChartArea.Font.Color = vbWhite
        .ChartArea.Interior.ColorIndex = 1
        .HasLegend = True
        .Legend.Position = xlLegendPositionBottom
        .Axes(xlCategory).MajorGridlines.Delete
    End With

End Sub

Here is an image of the output of the above code:

As you can see how the columns have been stacked incorrectly.

Now, when I use the "Record Macro" function under the developer tab I get the below code:

ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(1).AxisGroup = 1
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).AxisGroup = 1
ActiveChart.FullSeriesCollection(3).ChartType = xlLine
ActiveChart.FullSeriesCollection(3).AxisGroup = 1
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnStacked

And when creating a chart manually with data it creates the chart perfectly stacked.

So I am not sure what I am missing with this one. I have done some digging on the web but have been unable to find anything and am hoping someone here can give me a better understanding!

Thanks in advance.

Your first and best resource for working with charts in VBA should always be Jon Peltier's site. See if you can find help there. — PeterT
The problem is your Overlap is zero instead of 100, which is probably built into the ugly chart style you've used. This will fix it, when applied after the style is applied: ActiveChart.ChartGroups(1).Overlap = 100. — Jon Peltier

Ahmed AU Ahmed AU · Accepted Answer · 2018-10-25T12:04:06

I slightly changed and your code (I am working in Excel 2007)

   Dim myChart As Shape
    'changed addchart2 to addchart and sheet name, left etc
    Set myChart = Sheet1.Shapes.AddChart(Left:=92, Top:=0, Width:=264, Height:=192) 
    With myChart.Chart

        'load the data
        .SeriesCollection.NewSeries
        .SeriesCollection(1).Values = Array(100, 70, 120, 80, 40, 150, 200, 140, 150, 90, 110, 50)
        .SeriesCollection.NewSeries
        .SeriesCollection(2).Values = Array(100, 70, 120, 80, 40, 150, 200, 140, 150, 90, 110, 50)
        .SeriesCollection.NewSeries
        .SeriesCollection(3).Values = Array(150, 120, 150, 120, 80, 180, 280, 180, 195, 130, 160, 150)

        'x-axis
        .SeriesCollection(1).XValues = Array("J", "F", "M", "A", "M", "J", "J", "A", "S", "O", "N", "D")



        'set the chart type
        ' used .SeriesCollection instead of .FullSeriesCollection
        .SeriesCollection(1).ChartType = xlColumnStacked
        .SeriesCollection(1).AxisGroup = 1
        .SeriesCollection(2).ChartType = xlColumnStacked
        .SeriesCollection(2).AxisGroup = 1
        .SeriesCollection(3).ChartType = xlLine
        .SeriesCollection(3).AxisGroup = 1
        .SeriesCollection(3).Format.Line.Weight = 2.25

        'edit
        '.ChartStyle =209  ' commented out chart style
        .HasTitle = True
        .ChartTitle.Text = "My Chart"
        .ChartArea.Font.Color = vbWhite
        .ChartArea.Interior.ColorIndex = 1
        .HasLegend = True
        .Legend.Position = xlLegendPositionBottom
        .Axes(xlCategory).MajorGridlines.Delete
    End With

End Sub

and it is producing this chart

will be pleased if it helps you

Creating Stacked Column Chart with Arrays in VBA

1 Answers