Excel VBA - 获取图表数据范围 [英] Excel VBA - Get chart data range
问题描述
我想将数据添加到一堆现有图表中.假设每个图表具有不同数量的数据系列,并且原始数据的位置在同一工作簿中的某个位置.这是我的开始:
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
我知道 ActiveChart...XValues = rXVal
有效,但我收到了一个Object Required"Set rXVal = ActiveChart....XValues
行上的错误.我假设由于一个范围进入定义数据系列,我可以再次取出该范围然后添加到它.
I know that ActiveChart...XValues = rXVal
works, but I'm getting an "Object Required" error on the Set rXVal = ActiveChart....XValues
line. 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.
更新
为了澄清一些事情,我在 8 个地方有加速度计和 FFT 软件设置来记录 4 个独立频段的峰值振动响应.这会为每个样本产生 32 个数据点.导出时,软件吐出一个4张的Excel工作簿;每个频段一个.每张纸上都有加速度计名称和样本编号.
UPDATE
To clarify things a little, I have accelerometers in 8 places and FFT software setup to record peak vibration response in 4 separate 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.
推荐答案
我已经成功地使用了这个语法:
I have succeeded using this syntax:
Dim rXVal() As Variant
rXVal = ActiveChart.SeriesCollection(intSeries).XValues
更新
在这种情况下,您会得到一个数组,因为您的给定语句 (ActiveChart.SeriesCollection(intSeries).XValues
) 是一个数组,而不是一个范围.如果您深入研究 ActiveChart.SeriesCollection(intSeries)
的 Series 对象,这就是您在 Locals 窗口中看到的内容:
In this case you get an array, because your given statement (ActiveChart.SeriesCollection(intSeries).XValues
) is an array and not a range. This is what you see in Locals window if you dig into Series object of ActiveChart.SeriesCollection(intSeries)
:
(在我的虚拟数据中,我有名为 r1、r2、r3、r4 的行.)
(in my dummy data I have rows named r1, r2, r3, r4.)
我想说的是,XValues
没有任何表示其占用范围的属性.
What I want to say, XValues
does not have any property which would indicate its occupied range.
如果您确实需要一个范围,我建议您从 formula
属性中获取它.我建议的方法是用这个替换导致错误的行:
If you actually need a range, I would suggest getting it from the formula
property. And the way I would suggest is replacing your error causing line with this one:
Set rXVal = Range(Split(ActiveChart.SeriesCollection(intSeries).Formula, ",")(1))
接下来,我看到您试图获取 Values
的范围.类似地,使用这个:
Next, I see you trying to get the range for Values
. Similarly, use this:
Set rYVal = Range(Split(ActiveChart.SeriesCollection(intSeries).Formula, ",")(2))
另一件事.
以下几行最终会导致您出错:
The following lines will cause you an error finally:
intSeries = 1
Do Until ActiveChart.SeriesCollection(intSeries).Name = ""
...some code...
intSeries = intSeries + 1
Loop
一定要改变它们:
For intSeries = 1 To ActiveChart.SeriesCollection.Count
...some code...
Next
还有一件事.
考虑使用With
和End With
,因为您重复了很多ActiveChart.SeriesCollection(intSeries)
.那么您的代码将更具可读性,因为您只需跳过这一长行!那不是很棒吗???
Consider using With
and 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???
这篇关于Excel VBA - 获取图表数据范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!