VBA Excel - changing series source.

[Nick Bennett]

I have a problem with changing the data source for a chart series.
I'm using a VBA program to input data to a database (really just a
worksheet) then filter, select and manipulate the data, and graph the
end results.

I currently have a scatter chart which I've only got a single series
on. There is also a 3-point moving average trendline and Y error bars
(linked to a range in the worksheet). Changing the data source for
this has been easy with the following:

Charts(1).SetSourceData Source:=Range("Dataset!AH3:AI28")

Fine. Great. Now I've added a second series to the chart, and I
can't figure out how to tell the computer to change the data source
for series 1 to X:Y and change the data source to series 2 to A:B.

I've tried:

Charts(1).SeriesCollection(1).SetSourceData
Source:=Range("Dataset!AH3:AI28")

But that crashes with "object doesn't support this property or
method".

Charts(1).SeriesCollection(1).Source = Range("Dataset!AH3:AI28")

has the same problem. Intuitively it should work, but obviously I've
got the syntax horribly wrong! I then recorded a macro of me changing
the values manually and got the code:

ActiveChart.SeriesCollection(1).XValues = "=Dataset!AH3C33:AH28C33"

But for some reason when I run that in the program that says: "Error
1004: unable to set the Xvalues property of the series class". I
cannot see where this "C33" bit came from - makes no sense to me.
Having it or not makes no difference.

That implies that I actually have the syntax correct but something
else is stopping the code from executing. I have the code
"Charts(1).Activate" prior to all of this to ensure it's looking at
the right place. For a little while I had the same problem changing
the title of the chart, but fixed that. I can't for the life of me
remember how... I would suspect a data type mismatch, but that
wouldn't make sense if I could change the data source using the other
bit of code!

The program is horribly complicated and the actual graphing is the
easy bit, or so I thought. I don't, repeat DON'T, want to add a new
series to the chart, because otherwise I'll have a dozen or more lines
running across it. I just want to change the data source for an
EXISTING series. I can do it for a single series using the
Charts(1).SetSourceDate code but don't see a way to access individual
series in the same way. The program is designed to be very dynamic -
graphing multiple interpretations of the dataset within a few
seconds/minutes instead of taking a few days by hand: hence adding new
series with every iteration isn't going to be helpful. When the chart
is created I load it into the actual interface Form as the background
picture of a label and save/print it together with a description of
the filtered dataset and all the filters/modifications I've applied.
The graph really needs to be as tidy as possible.

If it helps, my X values are always the same, for both series. I
really only need to change the Y values.

I guess if it comes to the worst I can set up the ranges manually and
just move numbers in and out of them using the program, but the fact
that I can't do this has begun to bug me ;o) Every other problem I've
had I've managed to solve myself or looking online, but not this one.
Help would be appreciated.

I'm using Win2000, VB 6.3.8863, Excel 2002 SP2. Also WinXP, Excel
2002 SP3, VBA 6.4.8869 for some of the time (i.e. working from home!).

Cheers

Bennett

 

[Jon Peltier]

Nick -

SetSourceData tells the chart what its entire range should be. This is fine if the ranges are
all neatly aligned, and all series use the same range for their X values.

What you need is to set the X and Y ranges for each series separately:

With Chart(1).SeriesCollection(1)
.Values = Worksheets("DataSet").Range("AI3:AI28")
.XValues = Worksheets("DataSet").Range("AH3:AH28")
.Name = Worksheets("DataSet").Range("AI2")
End With

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

 

[Nick Bennett]

Nick -

SetSourceData tells the chart what its entire range should be. This is fine if the ranges are
all neatly aligned, and all series use the same range for their X values.

Thanks for that: it's what I suspected, hence the need for some kind
of series-specific code!

What you need is to set the X and Y ranges for each series separately:

With Chart(1).SeriesCollection(1)
.Values = Worksheets("DataSet").Range("AI3:AI28")
.XValues = Worksheets("DataSet").Range("AH3:AH28")
.Name = Worksheets("DataSet").Range("AI2")
End With

With a little tweaking I think I've got it to work: minor hiccup
though, it should read

With ChartS(1)...

which had me head-scratching for a bit ;-)

I was under the impression that the With commands were only for
setting up a chart at the start of a program or something, but it
seems reasonably happy with changing it on the fly.

Note for any readers with the same/similar problems that I expect it
only works if you've already established a series there: the sort of
errors I was getting imply that unless you've previously established a
NewSeries with a range etc you can't then go in and change it - since
my chart and series pre-exists that criteria is filled. This is
probably obvious to anyone with much experience with VBA but I'm
learning as I go!

I'm pretty sure I should be okay from here on. Thanks very much for
the assistance.

Cheers

Bennett

 

[Jon Peltier]

No, With/End With is a very handy construction in VB, and can be used anywhere a
block of code would otherwise have lots of repetition of the same object references.

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