chart point value

[Claude]

Hi all

How can I address a specific data point on a chart to give
back the specific (y)value?

the following does not work:
MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value)

 

[TroyW]

Claude,

Does the code below do what you want? The referencing of the chart depends
upon whether it is a standalone chart sheet or an embedded chart on a
worksheet.

Troy

Sub WorksheetEmbeddedChart()
Dim ws As Worksheet
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")
'Note: the name of the chart is: "Chart(space)1".
Set cht = ws.ChartObjects("Chart 1").Chart
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


Sub ChartSheet()
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

'Note: the name of the chart is: "Chart1".
Set cht = ThisWorkbook.Charts("Chart1")
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub

 

[Vic Eldridge]

I think it's easiest if you first assign all the values to an array,
then use the array to access the individual values.

eg.

Dim i As Integer
Dim v As Variant

v = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values

For i = 1 To UBound(v)
MsgBox v(i)
Next i


Regards,
Vic Eldridge

 

[Vic Eldridge]

Well now I'm confused.
Check out this little macro. The first MsgBox works but the second one fails.
She's a fussy old thing hey ?

Sub Huh()
Dim srs As Series
Set srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
MsgBox srs.Values(1)
MsgBox ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values(1)
End Sub

Regards,
Vic Eldridge

 

[Claude]

Hi Vic

Sorry for the late reply. The idea with first adding the
values to an array is a simple solution that works, thanks!

Thanks also to Troy for pointing out the XValues(i) syntax
(also I was struggling to implement it: maybe it's because
I'm still using excel97...)

 

[Jon Peltier]

But this line works in place of your second:

MsgBox WorksheetFunction.Index(ActiveSheet.ChartObjects(1) _
.Chart.SeriesCollection(1).Values, 1)

Go figure.

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