4

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)

Improve this question
1
  • This is not something I have done in excel, but from my experience anything that can be done manually can be done with vba. Break down what you need to do step by step then look through the API and figure out how to do it. msdn.microsoft.com/en-us/library/aa213725(office.11).aspx Commented Jul 9, 2010 at 4:21

4 Answers 4

Reset to default
4

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

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

Comments

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.

Improve this answer

Comments

1

When recording the macro manually, two possibilities are offered for the ChartType; one plots by rows, the other by columns. If this is changed later by some other process, the macro doesn't know to switch it back.

To ensure that it is displayed properly each time specify the ActiveChart.SetSourceData value e.g.

ActiveChart.SetSourceData Range("A1:B2"), xlRows

or

ActiveChart.SetSourceData Range("A1:B2"), xlColumns

Improve this answer

Comments

0

Following change worked for me

ActiveChart.SetSourceData Range("A1:B2"), xlRows

Here is the code snippet that worked for me. macro7 has data having headers in the first column. Macro8 has data having headers in the first row

    Sub Macro7()

   
    Range("U15").Select
    Set Rng = Range("o8:p12")
    Set cht = ActiveSheet.Shapes.AddChart2(-1, xlColumnStacked)
   
    ActiveSheet.Shapes(cht.name).Select
    ActiveSheet.Shapes(cht.name).IncrementLeft 426.75
    ActiveSheet.Shapes(cht.name).IncrementTop 56.25
    Application.CommandBars("Format Object").Visible = False
    Application.CutCopyMode = False
    cht.Chart.SetSourceData Rng, xlRows
   
    
    
    ActiveSheet.ChartObjects(cht.name).Activate
    ActiveSheet.ChartObjects(cht.name).Activate
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.SetElement (msoElementChartTitleNone)
    ActiveChart.SetElement (msoElementLegendRight)
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.SetElement (msoElementChartTitleNone)
    ActiveSheet.ChartObjects(cht.name).Activate
    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlColumnStacked
   
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Application.CutCopyMode = False
    'ActiveChart.SetSourceData Rng, xlRows
   
    
    ActiveChart.Refresh
   
End Sub
 
Sub Macro8()
   
    Range("U15").Select
    Set Rng = Range("M4:N4,M6:N6")
    Set cht = ActiveSheet.Shapes.AddChart2(-1, xlColumnStacked)
   
    ActiveSheet.Shapes(cht.name).Select
    ActiveSheet.Shapes(cht.name).IncrementLeft 426.75
    ActiveSheet.Shapes(cht.name).IncrementTop 56.25
    Application.CommandBars("Format Object").Visible = False
    Application.CutCopyMode = False
    cht.Chart.SetSourceData Rng, xlColumns
   
    
    
    ActiveSheet.ChartObjects(cht.name).Activate
    ActiveSheet.ChartObjects(cht.name).Activate
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.SetElement (msoElementChartTitleNone)
    ActiveChart.SetElement (msoElementLegendRight)
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.SetElement (msoElementChartTitleNone)
    ActiveSheet.ChartObjects(cht.name).Activate
    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlColumnStacked
   
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Application.CutCopyMode = False
   
    
    ActiveChart.Refresh
   
End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.