Excel VBA - 获取图表数据范围 [英] Excel VBA - Get chart data range

问题描述

我想将数据添加到一堆现有的图表中。假设每个图表具有不同数量的数据序列，并且原始数据的位置位于相同的工作簿中。这是我开始的：

 对于iChart = 1 To iCount 
 ActiveSheet.ChartObjects（Chart& ; iChart）.Activate 
 intSeries = 1 
直到ActiveChart.SeriesCollection（intSeries）.Name =
设置rXVal = ActiveChart.SeriesCollection（intSeries）.XValues'<  - 对象必需错误
设置rXVal =范围（rXVal，rXVal.End（xlDown））
设置rYVal = ActiveChart.SeriesCollection（intSeries）.Values 
设置rYVal = Range（rYVal，rYVal.End（xlDown ））
 ActiveChart.SeriesCollection（intSeries）.XValues = rXVal 
 ActiveChart.SeriesCollection（intSeries）.Values = rYVal 
 intSeries = intSeries + 1 
循环
下一步iChart 
  

我知道 ActiveChart ... XValues = rXVal 的作品，但我得到一个对象必需错误在设置rXVal = ActiveChart .... XValues 行。我假设自从定义数据系列后，我可以再次将该范围重新取出，然后再添加一个。

更新

为了澄清一些事情，我有8个地方的加速度计和FFT软件设置，以记录4个独立频段的峰值振动响应。每个样本产生32个数据点。导出时，软件会发出4张Excel工作簿;每个频段一个。每个工作表都有加速度计名称，样本号码下降。

解决方案

我已经成功使用了这种语法：

  Dim rXVal（）As Variant 
 rXVal = ActiveChart.SeriesCollection（intSeries）.XValues 
  

更新

在这种情况下，你会得到一个数组，因为你给定的语句（ ActiveChart.SeriesCollection（intSeries）.XValues ）是一个数组而不是一个范围。如果您浏览到 ActiveChart.SeriesCollection（intSeries）

的系列对象，您在本地窗口中看到的内容

（在我的虚拟数据我有行r1，r2，r3，r4。）

我想说的是， XValues 不有任何财产，将表明其占用范围。

如果您确实需要一个范围，我建议从公式获得属性。而我建议的方式是用以下代码来替换你的错误：

 设置rXVal = Range（Split（ActiveChart.SeriesCollection （intSeries）.Formula，，）（1））
  

接下来，我看到你试图获取值的范围。同样，使用这个：

 设置rYVal = Range（Split（ActiveChart.SeriesCollection（intSeries）.Formula，，）（ 2））
  

另一件事。

这些行会最终导致一个错误：

  intSeries = 1 
直到ActiveChart .SeriesCollection（intSeries）.Name =
 ...一些代码... 
 intSeries = intSeries + 1 
循环
  

使用以下方式更改它们：

 对于intSeries = 1到ActiveChart .SeriesCollection.Count 
 ...一些代码... 
下一个
  

考虑使用与和结束使用，因为您重复了很多 ActiveChart.SeriesCollection（intSeries）。那么你的代码会更加可读，因为你只要跳过这个长行！不会那么棒吗？

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 = 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.

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.

解决方案

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 array and not a range. This is what you see in Locals window if you dig into Series object of ActiveChart.SeriesCollection(intSeries):

(in my dummy data I have rows named r1, r2, r3, r4.)

What I want to say, XValues does not have any property which would indicate its occupied range.

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))

Next, I see you trying to get the range for Values. Similarly, use this:

Set rYVal = Range(Split(ActiveChart.SeriesCollection(intSeries).Formula, ",")(2))

Another thing.

These 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 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屋！