SeriesCollection Values Property Oddity

[Greg Wilson]

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

 

[Jon Peltier]

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]

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]

Maybe you need to interpret it as: Values and XValues can be placed into a
Variant Array. They are not exactly that, since you can set either to a
worksheet range.

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

 

[Greg Wilson]

Point taken. Thanks again Jon.

Greg

Maybe you need to interpret it as: Values and XValues can be placed into a
Variant Array. They are not exactly that, since you can set either to a
worksheet range.

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