excelvbachartsbar-chart

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


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.

alt text
(source: msecnd.net)


Solution

  • 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.