vba 如何为堆积条形图选择 .SetSourceData 的范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16971038/
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
How to select ranges for .SetSourceData for stacked bar chart?
提问by Logan Klenner
I want to select ranges for .SetSourceData for my stacked bar chart.
我想为我的堆积条形图选择 .SetSourceData 的范围。
My ranges are noncontiguous. The start date is in column G, and the rest of the numbers are calculated in columns V through AI.
我的范围是不连续的。开始日期在 G 列中，其余数字在 V 到 AI 列中计算。
Currently this is only charting columns V through AI, and it's using column G as the horizontal axis label.
目前这只是通过 AI 绘制 V 列，并且它使用 G 列作为水平轴标签。
I tried adding the horizontal axis label range (column E) as the first .range argument to Union, but it then displays columns E & F (not sure where F came from) as the horizontal axis label. Column G was nowhere to be found.
我尝试将水平轴标签范围（E 列）添加为 Union 的第一个 .range 参数，但随后将 E 和 F 列（不确定 F 来自何处）显示为水平轴标签。G列无处可寻。
Is there a way to do this with SetSourceData, or do I have to individually set SeriesCollection for all 15 series?
有没有办法用 SetSourceData 做到这一点，还是我必须为所有 15 个系列单独设置 SeriesCollection？
Sub Create_Chart()
'
' Create_Chart Macro
'
Dim ganttChart As ChartObject
Dim sourceRange As Range
With Sheets("Projects")
Set sourceRange = Union(.Range("G1:G70"), .Range("V1:AI70"))
End With
Set ganttChart = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)
With ganttChart
With .Chart
.ChartType = xlBarStacked
.Legend.Delete
.Parent.Width = 1224
.Parent.Height = 828
.SetSourceData Source:=sourceRange, PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet
With .Axes(xlValue)
'.MinimumScale = 41428
'.MaximumScale = 41607
.MajorUnit = 7
.TickLabels.NumberFormat = "m/d"
.TickLabels.Font.Size = 6
.TickLabels.Font.Name = "Calibri"
End With
With .Axes(xlCategory)
.ReversePlotOrder = True
.TickLabelSpacing = 1
.TickLabels.NumberFormat = "@"
.TickLabels.Font.Size = 6
.TickLabels.Font.Name = "Calibri"
End With
End With
End With
End Sub
回答by Jon Peltier
You might try selecting the source range right before inserting the chart. Excel might start out using the correct range, so you don't need to define the source data.
您可以尝试在插入图表之前选择源范围。Excel 开始时可能会使用正确的范围，因此您无需定义源数据。
An alternative approach is to add one series at a time:
另一种方法是一次添加一个系列：
Set ganttChart = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)
' clean up first
' i declared as long
For i = 1 to ganttChart.SeriesCollection.Count
ganttChart.SeriesCollection(1).Delete
Next
' rColumn declared as range
For Each rColumn In Sheets("Projects").Range("V1:AI70").Columns
With ganttChart.NewSeries
.Values = rColumn
.XValues = Sheets("Projects").Range("G1:G70")
End With
Next
