seriescollection(name) not working in excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bucweat - 21 Mar 2008 02:50 GMT

Hi,

I've got some VBA code running in some VBScript classes that works in
Excel 2000 and 2003 (or excel versions 9 and 11) that identifies a
series object in the following way (assume taht oMyChart points to a
valid chart object):

set oSeries = oMyChart.SeriesCollection("MyChartSeries")
msgbox oSeries.name

I know that "MyChartSeries" is a valid series name as the following
code tells me (works in 2000,2003,2007):

for each oSeries in oMyChart.SeriesCollection
msgbox oSeries.Name
next

Is there something wrong with the way SeriesCollection works on 2007?
I can't believe there is something I'm doing wrong here as it does
work in the other versions just fine (famous last words I know...). I
can not find any examples on the net where a name is used for index.

Signed, "In VBA hell..."

Reply to this Message

Ron Rosenfeld - 21 Mar 2008 03:30 GMT

>Hi,
>
[quoted text clipped - 19 lines]
>
>Signed, "In VBA hell..."

I don't know much about chart objects, but this seems to work in 2007, with one
embedded chart on Sheet1

======================
Option Explicit
Sub foo()
Dim oSeries As SeriesCollection
Dim oMychart As Chart
Dim oC As Object
Set oMychart = Worksheets("sheet1").ChartObjects(1).Chart
Set oSeries = oMychart.SeriesCollection

For Each oC In oSeries
Debug.Print oC.Name
Next oC
End Sub
======================
--ron

Reply to this Message

Jon Peltier - 21 Mar 2008 15:15 GMT

Ron -

I think that's what he says already works.

Bucweat -

Try something like this:

for each oSeries in oMyChart.SeriesCollection
if oSeries.Name = "MyChartSeries" then
' do what you need to do with "MyChartSeries"
exit for
end if
next

In fact, this isn't even robust enough. I've found that doing For Each in
some collections may not actually sample each item in the collection. This
is more reliable:

for iSeries = 1 to oMyChart.SeriesCollection.Count
set oSeries = oMyChart.SeriesCollection(iSeries)
if oSeries.Name = "MyChartSeries" then
' do what you need to do with "MyChartSeries"
exit for
end if
next

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

>>Hi,
>>
[quoted text clipped - 39 lines]
> ======================
> --ron

Reply to this Message

bucweat - 21 Mar 2008 16:37 GMT

Hi Jon,

Thanks...I actually had something like the first block of code in mind
as a workaround, but given what you said I will try something more
along the lines of the second block of code.

Just wondering...have you used SeriesCollection(index) where index is
a name successfully with 2007?

charlie

> Bucweat -
>
[quoted text clipped - 20 lines]
>
> - Jon

Reply to this Message

Jon Peltier - 22 Mar 2008 04:53 GMT

I haven't done too much in 2007, but I've tested plenty of existing code,
and I don't recall this being an issue.

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

Hi Jon,

Thanks...I actually had something like the first block of code in mind
as a workaround, but given what you said I will try something more
along the lines of the second block of code.

Just wondering...have you used SeriesCollection(index) where index is
a name successfully with 2007?

charlie

> Bucweat -
>
[quoted text clipped - 20 lines]
>
> - Jon

Reply to this Message

Ron Rosenfeld - 22 Mar 2008 00:51 GMT

>Ron -
>
>I think that's what he says already works.

<xx> (sound of hand slapping forehead). Of course, I misread.

This seems to work in 2007:

-------------------
Sub foo()
Dim oSeries As Object
Dim oMychart As Chart
Set oMychart = Worksheets("sheet1").ChartObjects(1).Chart
Set oSeries = oMychart.SeriesCollection("C1")
Debug.Print oSeries.Name
End Sub
----------------------------

Where "C1" is a valid name.
--ron

Reply to this Message