1

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

ZygD
  • 22,092
  • 39
  • 79
  • 102
Kes Perron
  • 455
  • 5
  • 12
  • 24
  • 3
    `Values` and `XValues` (although you can Set them to a range, *return* a variant array) Try here for help http://j-walk.com/ss/excel/tips/tip83.htm – Tim Williams Feb 05 '15 at 23:57
  • You could try declaring a Series-typed variable, then parsing out range addresses from a property such as .ForumlaR1C1. For example: Dim s As Series: Set s = ActiveChart.SeriesCollection(1): Debug.Print s.FormulaR1C1 – Mike Feb 06 '15 at 04:56

2 Answers2

9

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

enter image description here

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

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

ZygD
  • 22,092
  • 39
  • 79
  • 102
  • Sorry for the delay; I had to put this project down and tackle a higher priority task. The problem with this solution is that it returns an array of values rather than the range where the values exist. See question update for why that's important. – Kes Perron Mar 17 '15 at 13:43
  • Thanks! I really appreciate the suggestions, especially where to replace the do loop with a for loop (which I prefer). I hope to get a chance to work on this project more soon. I'll let you know how it goes. – Kes Perron Mar 18 '15 at 22:50
0

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)
Bendaua
  • 331
  • 1
  • 2
  • 11