Is there a property to get the values for a point in a chart series?

[Bruce Cooley]

I want to loop through data points in an x-y chart series and use the X and
Y values to set the Left and Top positions of some arrows I have placed on
the chart. I've got the regression formulas for converting the coordinates
of each data point into Left and Top values. I just can't figure out how to
access the X-value and Y-value of each point during the loop so that I can
plug them into my conversion formulas. Is there no property to get the
actual values of a point in a chart series?

Bruce

 

[Ivan Raiminius]

Hi Bruce,

try something similar to this (with appropriate changes to suit your
needs):
Worksheets(1).ChartObjects(1).Chart.SeriesCollection(1).Points(1)

or see help for points collection object.

Regards,
Ivan

 

[Bruce Cooley]

: Hi Bruce,
:
: try something similar to this (with appropriate changes to suit your
: needs):
: Worksheets(1).ChartObjects(1).Chart.SeriesCollection(1).Points(1)
:
: or see help for points collection object.
:
: Regards,
: Ivan


Hi Ivan,

I've been through all the help pages I can think of and poked around in the
Object Browser, and even hunted Google for a while. I've tried various
things but can't get at the values themselves through the Points. They must
have just left that property off the list, since it's not too hard for
someone to go get the values in the underlying data. I thought I might be
missing something easy and obvious.

Thanks for your suggestion anyway,
Bruce

 

[Andy Pope]

Here is some code to get at the values.
You can either dump all the values to a variant array or use the
worksheet INDEX() Function.

Sub X()

Dim vntXValues As Variant
Dim vntYValues As Variant
Dim lngItem As Long

' load values into array
With ActiveChart
With .SeriesCollection(1)
vntXValues = .XValues
vntYValues = .Values
End With
End With
For lngItem = LBound(vntXValues) To UBound(vntXValues)
Debug.Print "Point "; lngItem, "X="; _
vntXValues(lngItem), "Y="; vntYValues(lngItem)
Next

Debug.Print

' Index reference values
With ActiveChart
With .SeriesCollection(1)
For lngItem = 1 To .Points.Count
Debug.Print "Point "; lngItem, _
"X="; _
Application.WorksheetFunction.Index(.XValues, lngItem), _
"Y="; _
Application.WorksheetFunction.Index(.Values, lngItem)
Next
End With
End With

End Sub

Cheers
Andy

 

[Ivan Raiminius]

Hi Bruce,

maybe this is the solution:

http://www.j-walk.com/ss/excel/tips/tip83.htm

Regards,
Ivan

 

[Bruce Cooley]

: Here is some code to get at the values.
: You can either dump all the values to a variant array or use the
: worksheet INDEX() Function.


Andy,

Good ideas. I'll see if I can work one of these approaches into my code.
Thanks!

Bruce

 

[Bruce Cooley]

: Hi Bruce,
:
: maybe this is the solution:
:
: http://www.j-walk.com/ss/excel/tips/tip83.htm
:
: Regards,
: Ivan
:

That article certainly validates my frustration. John's solution looks
interesting and elaborate, and if I can't get one of Andy's methods to work
for me I will try to work this into my code. I have had other things to
work on, so I haven't been able to deal with this particular problem since
my last post.

Bruce