SeriesCollection Values Property Oddity
 

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

SeriesCollection Values Property Oddity
 

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

SeriesCollection Values Property Oddity
 

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

SeriesCollection Values Property Oddity
 

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" wrote in message
...
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

SeriesCollection Values Property Oddity
 

Point taken. Thanks again Jon.

Greg

"Jon Peltier" wrote:

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" wrote in message
...
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