如何使用 VBA 为 Excel 图表分配 XValues
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27821680/
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 assign XValues for excel chart using VBA
提问by vladiz
I have this VBA function for drawing charts in Excel 2013:
我有这个用于在 Excel 2013 中绘制图表的 VBA 函数：
Sub DrawChart2(obj_worksheetTgt As Worksheet, ByVal XLabels As Range, ByVal DataValues As Range, ByVal chartTitle As String, a As Integer, b As Integer)
'
'obj_worksheetTgt - Object worksheet on which to be placed the chart
'XLabels - Data range for X labels
'DataValues - Data range for Y values
'chartTitle - Chart title
'a - left border position of chart in pixels
'b - top border position of chart in pixels
With obj_worksheetTgt.ChartObjects.Add(a, b, 900, 300) ' Left, Top, Width, Height
With .Chart
.ChartType = xlBarClustered
Set .SeriesCollection(1).XValues = XLabels ' Here is the error
Set .SeriesCollection(1).Values = DataValues
.Legend.Position = -4107
.HasTitle = True
.chartTitle.Text = chartTitle
.chartTitle.Font.Size = 12
With .Axes(1).TickLabels
.Font.Size = 8
.Orientation = 90
End With
End With
End With
End Sub
I call the function this way:
我这样调用函数：
ChartsWorksheet = "Summary"
Queryname = "query1"
chartTitle = "Values"
With .Worksheets("LastDayData").ListObjects(Queryname)
Set chart_labels = .ListColumns(2).DataBodyRange
Set chart_values = .ListColumns(6).DataBodyRange
End With
Call DrawChart2(.Worksheets(ChartsWorksheet), chart_labels, chart_values, chartTitle, 10, 10)
And I receive an error:
我收到一个错误：
Runtime Error '1004':
Invalid Parameter
运行时错误“1004”：
无效的参数
When I click debug it marks the row "Set .SeriesCollection(1).XValues = XLabels" in the function above.
当我单击调试时，它会在上面的函数中标记“Set .SeriesCollection(1).XValues = XLabels”行。
In the documentation is written:
在文档中写道：
The XValues property can be set to a range on a worksheet or to an array of values, but it cannot be a combination of both
XValues 属性可以设置为工作表上的范围或值的数组，但不能是两者的组合
So it should be able to take the given range as values for XValues, but I can't understand why this error appears.
所以它应该能够将给定的范围作为 XValues 的值，但我不明白为什么会出现这个错误。
回答by laylarenee
Before you can set the Values and XValues of a series, you will need to add the series first. This is simple to do using the SeriesCollection.NewSeriesmethod as show below:
在设置系列的 Values 和 XValues 之前，您需要先添加系列。使用SeriesCollection.NewSeries如下所示的方法很容易做到这一点：
With ActiveSheet.ChartObjects.Add(a, b, 900, 300) ' Left, Top, Width, Height
With .Chart
.ChartType = xlBarClustered
' need to add the series before you can assign the values/xvalues
' calling the "NewSeries" method add one series each time you call it.
.SeriesCollection.NewSeries
' now that the series is added, you may assign (not set) the values/xvalues
.SeriesCollection(1).XValues = XLabels
.SeriesCollection(1).Values = DataValues
End With
End With
回答by Thomas Hartl
You cannot use named ranges for .XValues or .Values, but you can change the .Formula property by replacing the series formula
您不能为 .XValues 或 .Values 使用命名范围，但您可以通过替换系列公式来更改 .Formula 属性
For more information on editing the series formula see http://peltiertech.com/change-series-formula-improved-routines/
有关编辑系列公式的更多信息，请参阅http://peltiertech.com/change-series-formula-improved-routines/
Note of caution, there is a bug in Excel 2013 that prevents you from using named ranges starting with "R" or "C" when using VBA to change the series formula; see http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/named-range-use-in-chart-series-formulas-causes/c5a40317-c33f-4a83-84db-0eeee5c8827f/?auth=1&rtAction=1466703182593
请注意，Excel 2013 中存在一个错误，该错误会阻止您在使用 VBA 更改系列公式时使用以“R”或“C”开头的命名范围；请参阅http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/named-range-use-in-chart-series-formulas-causes/c5a40317-c33f-4a83-84db-0eeee5c8827f/?身份验证=1&rtAction=1466703182593
