Excel chart - how to read the XVALUES property 2

[RobBroekhuis]

I'm writing a procedure to add an additional trend to an existing x-y plot. I need to read the XVALUES range on a pre-existing series, so I can match the correct row range for the new series' VALUES range. But I can't seem to get the XVALUES property to do anything useful. According to the online help, XVALUES is a read/write variant. Any attempt at displaying or manipulating the property fails. I'm fairly new to VBA, and not as familiar with variants as I should be.
How do I extract the range information from an existing chart series?
Rob

 

[logius]

Well, the reason that you're not able to pull a range from the

XValues

value is because it only returns an array of type

Variant

. I'm looking into it, but the only thing you can do with

XValues

is see/manipulate the data in the chart. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[RobBroekhuis]

Surely there must be a way to access in some usable way the source ranges of a chart series? Most convenient would be to receive a range object, but I'll parse a string with the information if necessary. The variant returned by "xvalues" is neither, so I haven't a clue how to proceed. The help examples only show how to write to the property, not how to read it.
Still confused
Rob

 

[logius]

If you use this code:

Dim DataArray() As Variant

DataArray = ActiveChart.SeriesCollection(1).XValues

Then you can refer to the individual values in the Series by using a regular index value. For example:

MsgBox "Value for 1 is: " & DataArray(1)

will give you the XValue for the first data element in the series. I'm trying to play with the

Select

feature in the

SeriesCollection

object, but I'm not having very positive results.
Just out of curiosity, are you generating the chart through code, or are you taking a pre-generated chart and manipulating it? ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[logius]

If all you want to do is add a tendline, then use this code:

    ActiveChart.SeriesCollection(index).Trendlines.Add( _
                       Type:=xlLinear, Forward:=0, _
                       Backward:=0, _
                       DisplayEquation:=False, _
                       DisplayRSquared:=False)

All you have to do is make sure

index

is an integer referring to the proper series. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[RobBroekhuis]

I tried the array approach and it works - thanks!
I'm not actually adding trendlines, but an additional series (which I call a "trend&quot. The series is being added to a chart that was originally (in some previous user-selected menu choice) created by code. By the time the "add trend" choice is made, no record exists of the previous chart-building operation.
Rob