使用 VBA 宏设置 Excel 图表数据范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17105395/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting Excel Chart data range with VBA macro
提问by baarkerlounger
I'm trying to set the data set for a chart object using a macro. The macro selects the correct range that I want (I check by using rng.Select and debugging) but when I right click the chart object after the macro has finished running and select Select Data it says the data range is too complex to display and does not correctly populate my chart. My macro is below. Any pointers appreciated.
我正在尝试使用宏为图表对象设置数据集。宏选择了我想要的正确范围（我通过使用 rng.Select 和调试进行检查）但是当我在宏完成运行后右键单击图表对象并选择选择数据时，它说数据范围太复杂而无法显示没有正确填充我的图表。我的宏在下面。任何指针表示赞赏。
EDIT. I have a copy of the sheet where the graph was created from the populated data manually and clicking Select Data on this chart shows the non-contiguous range just fine - it's only when trying to set it by macro for a pre-existing chart that it doesn't work.
编辑。我有一份工作表的副本，其中图表是手动从填充数据创建的，然后单击此图表上的选择数据显示非连续范围就好了 - 只有在尝试通过宏为预先存在的图表设置它时不起作用。
Sub test()
UpdateChart 27, 64
End Sub
Sub UpdateChart(ByVal row As Long, ByVal col As Long)
Dim sht As Worksheet
Set sht = Worksheets("Report4_Chart")
Dim data As Worksheet
Set data = Worksheets("Report4")
Dim rng As Range
Dim exclude As Range
data.Activate
Set exclude = data.Rows(25)
Set rng = data.Range("A24", Intersect(data.Rows(row), data.Columns(col)))
Set rng = SetDifference(rng, exclude)
rng.Select
sht.Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=rng
End Sub
采纳答案by Shawn V. Wilson
For anyone else who's stumbled across this old question: I just discovered that basing a chart on non-contiguous ranges seemsto work only if the ranges form a rectangle.
对于偶然发现这个老问题的任何其他人：我刚刚发现基于非连续范围的图表似乎只有在范围形成矩形时才有效。
For instance, I was trying to group cells A1, A7, and C1, and couldn't get it to work. However, when I used A1, C1, A7, and C7, it did work.
例如，我试图对单元格 A1、A7 和 C1 进行分组，但无法使其正常工作。但是，当我使用 A1、C1、A7 和 C7 时，它确实有效。
(Actually, my ranges were all larger than a single cell. But the "missing" range was the same width as the one above it, and the same height as the one to the side of it. So the combined ranges formed a rectangle.)
（实际上，我的范围都比单个单元格大。但“缺失”范围与其上方的宽度相同，高度与其侧面相同。因此组合的范围形成了一个矩形。 )
