ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   seriescollection(name) not working in excel 2007 (https://www.excelbanter.com/excel-programming/408100-seriescollection-name-not-working-excel-2007-a.html)

bucweat

seriescollection(name) not working in excel 2007
 
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..."


Ron Rosenfeld

seriescollection(name) not working in excel 2007
 
On Thu, 20 Mar 2008 18:50:14 -0700 (PDT), bucweat wrote:

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..."


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

Jon Peltier

seriescollection(name) not working in excel 2007
 
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
_______



"Ron Rosenfeld" wrote in message
...
On Thu, 20 Mar 2008 18:50:14 -0700 (PDT), bucweat
wrote:

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..."


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




bucweat

seriescollection(name) not working in excel 2007
 
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 -

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



Ron Rosenfeld

seriescollection(name) not working in excel 2007
 
On Fri, 21 Mar 2008 10:15:43 -0400, "Jon Peltier"
wrote:

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

Jon Peltier

seriescollection(name) not working in excel 2007
 
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
_______


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

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





All times are GMT +1. The time now is 06:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com