vba 在excel vba中更改系列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16309044/
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
Changing the series in excel vba
提问by user2337549
I have a chart on one of my sheets and I need to change a series in it in code. The problem is that I keep getting the error 1004 message. I've looked around and can't find a reason for it. Here's the code:
我的一张纸上有一张图表，我需要在代码中更改其中的一个系列。问题是我不断收到错误 1004 消息。我环顾四周，找不到原因。这是代码：
Sheets("Charts").ChartObjects(1).Chart.SeriesCollection(1).Formula = "=G49:I" & dblResult & ")"
Sheets("Charts").ChartObjects(1).Chart.SeriesCollection(1).Formula = "=G49:I" & dblResult & ")"
回答by Siddharth Rout
Are you trying this?
你在尝试这个吗？
Sheets("Charts").ChartObjects(1).Chart.SeriesCollection(1).Formula = _
"=SERIES(,," & "Charts!G49:I" & dblResult & ",1)"
回答by David Zemens
An alternative that I prefer to manipulating the Series Formula is to just work with the individual properties of the SeriesCollection.
我更喜欢操作系列公式的另一种方法是仅使用SeriesCollection.
NoteI'm writing this from memory/untested so let me know if there's any problems with it!
注意我是从记忆中/未经测试写的，所以如果有任何问题，请告诉我！
Inside the Withblock, you would need to determine the Ranges to use for the Values, XValues, Name, and Order, of course you can omit the parts that you don't need (e.g., I rarely need to manipulate the series .Order)
在With块内，您需要确定用于Values、XValues、Name、的范围Order，当然您可以省略不需要的部分（例如，我很少需要操作系列.Order）
Dim cht as Chart
Dim srs as Series '# Series variable'
Dim s as Long '# Series iterator'
Dim ws as Worksheet
Set ws = ActiveSheet
Set cht = ws.ChartObjects(1).Chart '## Modify as needed.'
For each srs in cht.SeriesCollection
With srs
s = s+1
.Values = ws.Range("Some_Range_For_Values")
.XValues = ws.Range("Range_For_XValues")
.Name = ws.Range("Range_For_SeriesName")
.Order = s
End With
Next
Practically speaking, here is a pretty simple example. I often build or update a chart dynamically using an approach like this. Assuming that XValuesare in column A, while series data is in columns B:F, you could do something like:
实际上，这是一个非常简单的例子。我经常使用这样的方法动态构建或更新图表。假设XValues在 A 列中，而系列数据在 B:F 列中，您可以执行以下操作：
Dim rngData as Range '# A range containing all of the series values & xValues'
Dim s as Long
Dim cht as Chart
Dim srs as Series
Dim ws as Worksheet
Set ws = ActiveSheet
Set cht = ws.ChartObjects(1).Chart
Set rngData = Range("A2:F10")
'## I like to remove existing series, and then add in the new data. '
For each srs in cht.SeriesCollection
srs.Delete
Next
'## Iterate over our range and add series back in to the chart.'
For s = 2 to rngData.Columns.Count
Set srs = cht.NewSeries
With srs
.XValues = rngData.Columns(1).Address
.Values = rngData.Columns(s).Address
'Name = rngData.Cells(1,s).Offset(-1,0).Value
'Order = s-1
End With
Next
