vba 如何纯粹通过excel vba编程创建堆积柱形图？

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

提问by James

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.

我正在尝试在 VBA 中创建一个堆积柱形图，其中有一个条形图，每个条形图都显示了图表上子类别的细分（参见图片右侧）。对于普通条形图，我所要做的就是创建一个新系列并设置 Values 和 XValues 属性。我不确定如何告诉它子类别的值。

_{(source: msecnd.net)}

_{（来源：msecnd.net）}

采纳答案by James

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.

要创建堆叠列效果，您需要为列中的每个细分创建一个系列。例如，假设我们有 4 个动物园。假设他们位于迈阿密、亚特兰大、纽约和拉斯维加斯。此外，我们有 4 种动物，狮子、老虎、熊和海豹。我们想要一个堆叠的列，显示每只动物的数量以及动物的总数。

First, set the chart type to xl3DColumnStacked like this.

首先，像这样将图表类型设置为 xl3DColumnStacked。

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.

然后将 Values 属性设置为动物的值数组，将 XValues 属性设置为城市名称的数组。因此，如果第一个系列是为 Lions 制作的，请使用迈阿密、亚特兰大、纽约、拉斯维加斯的狮子数量创建一个数组，以及一个包含字符串“Miami”、“Atlanta”等的数组。 cityNames 数组将被重复用于每个系列，但 lionCounts 显然每次都会被适当的数组替换。即 TigerCounts、bearCounts 等。

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.

这应该是直接使用程序中的值而不是单元格引用创建堆叠列所需的全部内容。

回答by Mike Woodhouse

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.

启动此类任务的最简单方法是查看手动执行任务时会发生什么，使用宏记录器捕获 VBA 等效项。

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

比如，我刚刚记录了这个（在Excel XP中，但以后的版本应该是类似的）。代码与记录完全一致：

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").Selectisn't needed, since it's repeated further down.

看看上面的内容，我会说第一行Range("B4:D6").Select不需要，因为它在下面重复了。

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 Addin the "Members..." list gives this in the lower pane:

现在清理一下。在这里，对象浏览器非常有用。如果您不熟悉，请尝试在 VBA 编辑器中按 F2。通过在上部下拉列表中选择“Excel”并查看类列表中的图表来稍微缩小上下文。选择Add在“成员......”列表给出了这样的在下面的窗格：

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

So Charts.Addreturns a Chart, which must be the ActiveChartthat'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 Selectionwhen the macro runs. Looking at the members of Chart in the Object Browser, it's apparent that Sourcein SetSourceDatashould be a Range. Let's try this:

所以Charts.Add返回一个图表，它必须是ActiveChart在代码的其余部分重复引用的那个。像这样的重复很乏味（并且可能会影响性能），所以我会稍微返工。为了练习，让我们假设我们将在运行宏之前选择我们的目标数据，所以它会在Selection宏运行时进入。查看对象浏览器中 Chart 的成员，很明显SourceinSetSourceData应该是一个Range. 让我们试试这个：

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 Selectionprobably changes when a chart is activated. So we should capture the range before starting to work with the chart:

它在我的机器上不起作用 - 我认为Selection激活图表时可能会发生变化。所以我们应该在开始使用图表之前捕获范围：

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

从那里，您应该能够找到各种自定义（记住对象浏览器！）以获取您想要的图表。（请记住，根据您的 Excel 版本，可能有一些次要功能无法通过 VBA 访问。）

回答by PluckyBob

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.

手动录制宏时，ChartType 提供了两种可能性；一个按行绘制，另一个按列绘制。如果稍后由其他某个进程更改，则宏不知道将其切换回来。

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

为确保每次都能正确显示，请指定ActiveChart.SetSourceData值，例如

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

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

or

或者

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

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