Changing XValues and Values in a chart
 

I have a subroutine that makes a scatterplot for ranges (.XValues & .Values)
that can't be predicted.

I want to add my own ranges to the scatterplot, for example by:

Dim XValuesToAddTo as variant
With ActiveChart.SeriesCollection(1)
XValuesToAddTo = .XValues
XValuesToAddTo = XValuesToAddTo & some new ranges
.XValues = XValuesToAddTo
etc

There are several posts on the web that say that this sort of thing doesn't
work, but there's no explanation and nobody has proposed another way of
doing it.

It's a bit like assigning a range to a variable: you need to know that this
only works for the .Cells property, but nobody seems to know why. Perhaps
XValues has some property we don't know about.

Can anybody explain what's going on in this kind of situation, or is Excel
VBA just flaky in this area?

Regards

Changing XValues and Values in a chart
 

In your code, XValuesToAddTo is an array, even if the chart points to a range.

What you can do is parse the SERIES formula to get the range (use John Walkenbach's
Chart Series Formula Class Module, http://j-walk.com/ss/excel/tips/tip83.htm), then
use Union to combine the existing range with whatever additional ranges you need.

I dunno what you mean about the .Cells and variables.

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

lee.christopher wrote:

I have a subroutine that makes a scatterplot for ranges (.XValues & .Values)
that can't be predicted.

I want to add my own ranges to the scatterplot, for example by:

Dim XValuesToAddTo as variant
With ActiveChart.SeriesCollection(1)
XValuesToAddTo = .XValues
XValuesToAddTo = XValuesToAddTo & some new ranges
.XValues = XValuesToAddTo
etc

There are several posts on the web that say that this sort of thing doesn't
work, but there's no explanation and nobody has proposed another way of
doing it.

It's a bit like assigning a range to a variable: you need to know that this
only works for the .Cells property, but nobody seems to know why. Perhaps
XValues has some property we don't know about.

Can anybody explain what's going on in this kind of situation, or is Excel
VBA just flaky in this area?

Regards
.