Obtaining the value of an ActiveChart.SeriesCollection(1).Points(2)

[Greg Glynn]

Hi,

I can't seem to nut this one out. How do I enquire as to the value of
a SeriesCollection Point? I'm trying to vary the color of a datapoint
based on its value, which I'll be able to do if I can get the value.
I'd rather not look at the Source Data array for the chart if I don't
have to.

Anyone know?

This doesn't work:
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Points(2).Select
MsgBox ActiveChart.SeriesCollection(1).XValues

Regards

Greg

 

[Peter T]

Hi Greg,

Try something like this -

Sub test()
Dim cht As Chart
Dim sr As Series
Dim arrXvalues As Variant
Dim arrYvalues As Variant

Set cht = ActiveSheet.ChartObjects("Chart 3").Chart
Set sr = cht.SeriesCollection(1)

MsgBox sr.Values(2), , sr.XValues(2)

arrYvalues = sr.Values
arrXvalues = sr.XValues

MsgBox arrYvalues(2), , arrXvalues(2)

End Sub

Regards,
Peter T

 

[Greg Glynn]

Works Great Peter, Thanks.

By the way ... How do I loop through all charts on a particular
worksheet?


Greg

 

[Peter T]

Sub Test()
Dim i As Long, j As Long
Dim ws As Worksheet
Dim cht As Chart
Dim sr As Series
Set ws = ActiveSheet

For i = 1 To ws.ChartObjects.Count
Set cht = ws.ChartObjects(i).Chart
For Each sr In cht.SeriesCollection
Debug.Print sr.Name
'code
Next
Next
End Sub

Instead of For i = 1 To ws.ChartObjects.Count you could do

Dim chtObj as ChartObject
For each chtObj in ws.ChartObjects
set cht = chtObj.Chart

However in rare scenarios there can be problems with this method of looping
each 'object'

Regards,
Peter T

 

[Jon Peltier]

Peter -

I invariably use

For each chtObj in ws.ChartObjects

What kind of problems have you encountered with this approach?

- Jon

 

[Peter T]

Hi Jon,

One problem using For Each at the DrawingObject level (ChartObject,
Rectangle etc) is if the object's name includes punctuation it's not picked
up, eg

Sub test2()
Dim i As Long, nCnt As Long
Dim chtObj As ChartObject

For i = 0 To 2
ActiveSheet.ChartObjects.Add(10#, 10# + i * 120, 120#, 80#).Name _
= "MyChart." & i
Next

nCnt = 0
For Each chtObj In ActiveSheet.ChartObjects
nCnt = nCnt + 1
Next
Debug.Print nCnt; " Charts returned with For Each"

nCnt = 0
For i = 1 To ActiveSheet.ChartObjects.Count
nCnt = nCnt + 1
Next
Debug.Print nCnt; " Charts returned with 1 to Count"

End Sub


I'm sure I've also been caught out by one or two other things using 'For
Each' but off the top of my head can't remember what (I did say rare
scenarios). Actually, even For Each sr in .SeriesCollection can be less
reliable than For 1 to .Count with series in certain types of charts.

Regards,
Peter T

 

[Jon Peltier]

I remember now the discussion about punctuation in object names, and I am
glad I never got into that habit.

- Jon

 

[Peter T]

Sure, but might need to cater for other people's habits!

Regards,
Peter T