Excel VBA - 获取图表数据范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28355176/
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
Excel VBA - Get chart data range
提问by Kes Perron
I want to add data to a bunch of existing charts. Assume that each chart has a different number of data series and that the location of the raw data is somewhere in the same workbook. Here's what I'm starting with:
我想将数据添加到一堆现有图表中。假设每个图表具有不同数量的数据系列，并且原始数据的位置在同一工作簿中的某个位置。这是我的开始：
For iChart = 1 To iCount
ActiveSheet.ChartObjects("Chart " & iChart).Activate
intSeries = 1
Do Until ActiveChart.SeriesCollection(intSeries).Name = ""
Set rXVal = ActiveChart.SeriesCollection(intSeries).XValues '<- Object Required error
Set rXVal = Range(rXVal, rXVal.End(xlDown))
Set rYVal = ActiveChart.SeriesCollection(intSeries).Values
Set rYVal = Range(rYVal, rYVal.End(xlDown))
ActiveChart.SeriesCollection(intSeries).XValues = rXVal
ActiveChart.SeriesCollection(intSeries).Values = rYVal
intSeries = intSeries + 1
Loop
Next iChart
I know that ActiveChart...XValues = rXValworks, but I'm getting an "Object Required" error on the Set rXVal = ActiveChart....XValuesline. I'm assuming that since a range went in to define the data series, I can get that range back out again and then add to it.
我知道这ActiveChart...XValues = rXVal行得通，但我在线上收到“需要对象”错误Set rXVal = ActiveChart....XValues。我假设由于一个范围进入定义数据系列，我可以再次取回该范围然后添加到它。
UPDATE
To clarify things a little, I have accelerometers in 8 places and FFT software setup to record peak vibration response in 4 seperate frequency bands. This yields 32 data points per sample. When exporting, the software spits out an Excel workbook with 4 sheets; one for each frequency band. Each sheet has the accelerometer names going across and sample numbers going down.
更新
为了澄清一些事情，我在 8 个地方有加速度计和 FFT 软件设置来记录 4 个单独频段的峰值振动响应。这会为每个样本产生 32 个数据点。导出时，软件吐出一个4页的Excel工作簿；每个频段一个。每张纸上都有加速度计名称和样本编号。
回答by ZygD
I have succeeded using this syntax:
我已成功使用此语法：
Dim rXVal() As Variant
rXVal = ActiveChart.SeriesCollection(intSeries).XValues
UPDATE
更新
In this case you get an array, because your given statement (ActiveChart.SeriesCollection(intSeries).XValues) is an arrayand not a range. This is what you see in Locals window if you dig into Series object of ActiveChart.SeriesCollection(intSeries):
在这种情况下，您会得到一个数组，因为您给定的语句 ( ActiveChart.SeriesCollection(intSeries).XValues)是一个数组而不是一个范围。如果您深入研究以下的 Series 对象，这就是您在 Locals 窗口中看到的内容ActiveChart.SeriesCollection(intSeries)：
(in my dummy data I have rows named r1, r2, r3, r4.)
（在我的虚拟数据中，我有名为 r1、r2、r3、r4 的行。）
What I want to say, XValuesdoes not have any property which would indicate its occupied range.
我想说的是，XValues没有任何属性可以表明其占用范围。
If you actually need a range, I would suggest getting it from the formulaproperty. And the way I would suggest is replacing your error causing line with this one:
如果您确实需要一个 range，我建议您从该formula属性中获取它。我建议的方法是用这个替换导致错误的行：
Set rXVal = Range(Split(ActiveChart.SeriesCollection(intSeries).Formula, ",")(1))
Next, I see you trying to get the range for Values. Similarly, use this:
接下来，我看到您试图获取Values. 同样，使用这个：
Set rYVal = Range(Split(ActiveChart.SeriesCollection(intSeries).Formula, ",")(2))
Another thing.
另一件事。
The following lines will cause you an error finally:
以下几行最终会导致您出错：
intSeries = 1
Do Until ActiveChart.SeriesCollection(intSeries).Name = ""
...some code...
intSeries = intSeries + 1
Loop
Do change them with:
请使用以下命令更改它们：
For intSeries = 1 To ActiveChart.SeriesCollection.Count
...some code...
Next
Yet another thing.
还有一件事。
Consider using Withand End With, as you repeat a lot ActiveChart.SeriesCollection(intSeries). Then your code will be much more readable, as you would just skip this long line! Wouldn't that be awesome???
考虑使用With和End With，因为你重复了很多ActiveChart.SeriesCollection(intSeries)。那么您的代码将更具可读性，因为您只需跳过这一长行！会不会很厉害？？？
回答by Bendaua
This works fine for me:
这对我来说很好用：
Dim rXVal() As Variant
Dim rXValMin, rXValMax As Double
rXVal = ActiveChart.SeriesCollection(intSeries).XValues
rXValMin = WorksheetFunction.Min(rXVal)
rXValMax = WorksheetFunction.Max(rXVal)
