Setting Series data in VBA

  • Thread starter Thread starter foondawgy
  • Start date Start date
F

foondawgy

I have the following snippet of code where I am trying to update a
series in my chart:

****************************************************
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = MySeriesString
****************************************************

In a large amount of previous code I have set MySeriesString to the
following in order to pick the exact cells I want in the plot:

"Results!D6,Results!D10,Results!D14,Results!D18,Results!D22,Results!D26,Results!D30"

But when I get to the line where I set the series values to
MySeriesString I get an error:

"Unable to set the Values property of the Series class"

I assume I just don't understand how to format the MySeriesString. I
have tried many combinations of putting an "=" sign and brackets around
the data, but I can't seem to figure it out. Can anyone help me with
that?

Thanks,
Matt
 
Matt,

It sounds like maybe your MySeriesString array is not loading correctly.
Here's an example of code that loads a discontiguous range of X and Y axis
data into arrays. It then updates the chart with that data. The example
assumes that your X-axis data is in cell A1, A3, A5, A7, and A9. The Y-axis
data is in cell B1, B3, B5, B7, and B9. To see what's happening with your
MySeriesString array, look how I've set up the array called XArray and
YArray.

The ReDim Preserve statements allow the XArray and YArray to expand with
additional data as the cells are looped through. I think this is where you
might need to update your code that generates the MySeriesString array.

Sub UpdateChart()

Dim XArray()
Dim YArray()
Dim Rng As Range
Dim Ctr As Integer

Ctr = 0

For Each Rng In Range("A1,A3,A5,A7,A9")

ReDim Preserve XArray(Ctr)
ReDim Preserve YArray(Ctr)

XArray(Ctr) = Rng.Value
YArray(Ctr) = Rng.Offset(0, 1).Value

Ctr = Ctr + 1

Next Rng

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).XValues = XArray
ActiveChart.SeriesCollection(1).Values = YArray

End Sub
 
John,

Thanks for the response. You code has actually give me some good
ideas. The only difference between your code and what I need to do is
that your code actually puts the actual values into the
SeriesCollection(1).XValues/SeriesCollection(1).Values. I need to put
the cell references in there instead. So instead of putting the values
of A1, A3, A5, A7, A9...I need to put the cells A1, A3, A5, A7, A9 in
the graph so that if the values of these cells change the tables will
automatically be updated.

I think I am just having a formatting issue with "MySeriesString".

I have tried different formatting things like:

MySeriesString = "=D6,D10,D14"
MySeriesString = "=Results!D6,Results!D10,Results!D14"
MySeriesString = "Results!D6,Results!D10,Results!D14"
MySeriesString = "=(Results!D6,Results!D10,Results!D14)"

and setting that to the chart like this:

ActiveChart.SeriesCollection(1).Values = MySeriesString

But I always seem to get "Unable to set the Values property of the
Series class".

Thanks,
Matt
 
Matt -

Sometimes the chart doesn't seem as smart as the worksheeet.

Both of these (from the Immediate window) work as expected, selecting
the indicated range:

range("F7:F9").Select
range("F7,F9,F11").Select

The following successfully apply the values to the chart (selection is a
series):

selection.values = "{1,2,3}"
selection.values = range("F7:F9")

The second one applies the cell references, not just the values.

This does not work:

selection.values = range("F7,F9,F11")

I also went through the whole range of variations you did, to no avail.

But then I remembered an old trick. I set a VBA range variable to this
range:

Set MyRange = Range("F7,F9,F11")

defined a name based on it:

MyRange.Name = "MY_RANGE"

and finally used the defined name for the chart data:

Selection.Values = "=Sheet1!MY_RANGE"

The entire procedure:

Sub DiscontiguousSourceData()
Dim MyRange As Range
Dim MyChart As Chart

Set MyRange = ActiveSheet.Range("F7,F9,F11")
MyRange.Name = "MY_RANGE"
Set MyChart = ActiveSheet.ChartObjects(1)
MyChart.SeriesCollection(1).Values = "=Sheet1!MY_RANGE"
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top