Setting Series data in VBA

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

----
Regards,
John Mansfield
http://www.pdbook.com


"[email protected]" wrote:

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

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

John Mansfield wrote:
> 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
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
> "[email protected]" wrote:
>
> > 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 -

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/
_______

[email protected] wrote:

> 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
>
> John Mansfield wrote:
>
>>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
>>
>>----
>>Regards,
>>John Mansfield
>>http://www.pdbook.com
>>
>>
>>"[email protected]" wrote:
>>
>>
>>>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
>>>
>>>
>
>

Jon,

That works great.

Thanks alot,
Matt