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
– PeterT, Commented Oct 25, 2018 at 11:55
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
– Jon Peltier, Commented Nov 2, 2018 at 2:16

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

2

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

answered Oct 25, 2018 at 12:04

Ahmed AU

2,7772 gold badges9 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DDV Over a year ago

Thank you Ahmed. It seems that the problem does not lie with the .FullSeriesCollection but rather with the .ChartStyle. I am not sure why but when I comment out that line it loads the data correctly. Any ideas why that would be the case?

Ahmed AU Over a year ago

About chartstyle please refer links 1 learn.microsoft.com/en-us/office/vba/api/Excel.Chart.ChartStyle 2 stackoverflow.com/questions/38813522/…

Collectives™ on Stack Overflow

Creating Stacked Column Chart with Arrays in VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related