Thanks Jon for the reply.

I was mostly perplexed as to what I am missing. I long ago figured out how
to get the individual elements but never understood why #3 wouldn't work. The
Values property clearly returns an array (IsArray/LBound/UBound return
results) yet the elements can't be directly accessed which seems weird.

I was thinking that my understanding of Variants and/or arrays is the
problem. From your response, I assume it's just something missing in the
Object Model.

Use #2, which gets the data into VBA all in one shot. If you have a lot of
points, #1 will have to keep going back to Excel to get the data.

Thanks for the tip.


Best Regards,
Greg

"Jon Peltier" wrote:

Two out of three ways work. Why beat yourself up over the third?

Use #2, which gets the data into VBA all in one shot. If you have a lot of
points, #1 will have to keep going back to Excel to get the data.

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

"Greg Wilson" wrote in message
...
This has long perplexed me. Thought I would ask someone.

The Values property of a series apparently returns an array of all the
point
values in the series. Howver, one apparently can't extract an element from
this array. The following macros demo the issue. Looking for
enlightenment:

'This works
Sub Test1()
Dim i As Long
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
For i = 1 To .Points.Count
MsgBox Application.Index(.Values, i)
Next
End With
End Sub

'This also works
Sub Test2()
Dim arr As Variant
Dim i As Long
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
arr = .Values
For i = LBound(arr) To UBound(arr)
MsgBox arr(i)
Next
End With
End Sub

'This DOESN'T work
Sub Test3()
Dim i As Long
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
MsgBox IsArray(.Values)
MsgBox LBound(.Values)
MsgBox UBound(.Values)
For i = LBound(.Values) To UBound(.Values)
MsgBox .Values(i) 'This doesn't work ???
Next
End With
End Sub

If someone can enlighten me I think it will be of general interest as
well.

Greg