title: Creating Stacked Column Chart with Arrays in VBA
body: <p>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. </p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is an image of the output of the above code:</p>
  
  <p><a href="https://i.stack.imgur.com/qF4ki.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qF4ki.jpg" alt="enter image description here"></a></p>
  
  <p>As you can see how the columns have been stacked incorrectly.</p>
  
  <p>Now, when I use the "Record Macro" function under the developer tab I get the below code:</p>
  
  <pre><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
  </code></pre>
  
  <p>And when creating a chart manually with data it creates the chart perfectly stacked. </p>
  
  <p>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!</p>
  
  <p>Thanks in advance.</p>