How do I create a stacked column chart purely through programming with excel vba?

Question

I am trying to create a stacked column chart in VBA where there is a bar graph and each bar graph shows the breakdown of subcategories on the graph (See right side of picture). For the normal bar graph all I had to do was create a new series and set the Values and XValues properties. I'm not sure how to tell it about the values for the subcategories.

Answer 1

The simplest way to start a task like this is to see what happens when you perform the task by hand, using the Macro Recorder to capture the VBA equivalent.

For example, I just recorded this (in Excel XP, but later versions should be similar). The code is exactly as recorded:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 09/07/2010 by MW
'

'
    Range("B4:D6").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B4:D6"), PlotBy:= _
        xlRows
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

Looking at the above, I'd say the first line, Range("B4:D6").Select isn't needed, since it's repeated further down.

Now to clean up a bit. Here the Object Browser can be hugely useful. If you're not familiar, try hitting F2 in the VBA editor. Narrow down the context a little by selecting "Excel" in the upper dropdown and look at Charts in the Classes list. Selecting Add in the "Members..." list gives this in the lower pane:

Function Add([Before], [After], [Count]) As Chart
    Member of Excel.Charts

So Charts.Add returns a Chart, which must be the ActiveChart that's repeatedly referenced in the remainder of the code. Duplication like that is tedious (and can be performance-affecting) so I'd rework a little. Let's assume for the sake of the exercise that we're going to select our target data before running the macro, so it'll be in Selection when the macro runs. Looking at the members of Chart in the Object Browser, it's apparent that Source in SetSourceData should be a Range. Let's try this:

Sub Macro1()
    With Charts.Add
        .ChartType = xlColumnStacked
        .SetSourceData Source:=Selection, PlotBy:=xlRows
        .Location Where:=xlLocationAsObject, Name:="Sheet1"
    End With
End Sub

It doesn't work on my machine - I think Selection probably changes when a chart is activated. So we should capture the range before starting to work with the chart:

Sub CreateStackedColumnChartFromSelection()
Dim src As Range
    Set src = Selection
    With Charts.Add
        .ChartType = xlColumnStacked
        .SetSourceData Source:=src, PlotBy:=xlRows
        .Location Where:=xlLocationAsObject, Name:="Sheet1"
    End With
End Sub

From there, you should be able to find all kinds of customizations (remember Object Browser!) to get the chart how you want it. (Bear in mind that, depending on your Excel version, there may be a few minor features not accessible via VBA.)

Answer 2

To create the stacked column effect, you need to create a series for each subdivision in the column. For example, say we had 4 zoos. Say they are located in Miami, Atlanta, New York, and Las Vegas. Also, we have 4 animals, Lions, Tigers, Bears, and Seals. And we want a stacked column showing the number of each animal and also the total number of animals.

First, set the chart type to xl3DColumnStacked like this.

ActiveChart.ChartType = xl3DColumnStacked

We would then create a series for each animal.

ActiveChart.SeriesCollection.NewSeries

Then set the Values property to the array of values for the animal and the XValues property to the array of names for the cities. So if the first series was for Lions, make an array with the # of lions at Miami, Atlanta, NY, Las Vegas and an array containing the strings "Miami","Atlanta", etc. The cityNames array will be reused for each Series but the lionCounts will obviously be replaced each time with the appropriate array. i.e. tigerCounts, bearCounts, etc.

ActiveChart.SeriesCollection(1).Values = lionCounts
ActiveChart.SeriesCollection(1).XValues = cityNames

That should be all it takes to create a stacked column using values straight from your program instead of cell references.