What VBA Stmt Will Tell Me the Index of a Chart's SERIES Formula?

[SidBord]

I have written a macro that allows a user to select a
specific series on an embedded chart, then specify a change
to the series limit ranges. It works very well, EXCEPT I
have not figured out how to write a statement that will
return the index number of the selected series. I
mistakenly believed that the series index number is the
last number on the right end of the series formula. It's
not. That's the plotting order sequence number, and it can
change. I need the series index number, which I think is
unique within the workbook. I tried using SELECTION.NAME
after selecting the series with my cursor, but about half
the time it returned the chart index. When it succeeded,
it would return the series name, if it exists, or a value
like SERIES4, if the name does not exist. That's too iffy.
I need a statement that will consistently give me the
series index number. Anyone know how to do that?

 

[Jon Peltier]

You have to go back in time to the land before VBA to find this one.
Excel's old XLM was able to get some information that they left out of
VBA. This line returns a text string with the old XLM name for the
selected object:

ExecuteExcel4Macro("SELECTION()")

If a series is selected, it returns "Si", where i is the series number
you're looking for. If a single point is selected, it returns "SiPj",
where j is the number of the point. The series are numbered in the order
you add them to the chart, with one twist. If you make a chart with
three series, S1, S2, and S3, then delete series 2, you are left with S1
and S3. If you add another series now, it becomes S2, taking the slot of
the missing series.

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

 

[xlrotor]

Why do you want the series's index number?

Is it so you can access the series object like this...

Activechart.seriescollection(i)

Maybe an alternative to the excel4macro would be to use a function tha
loops through the Seriescollection and compares the .Formula t
Selection.Formula. When you get a match, you can be darn sure you'v
got the index number of the selected series. The .Formula propert
should be unique, so this approach ought to work under all conditions.
But watch out for items in the SeriesCollection that don't return
.Formula property (like when the charted cell ranges are empty).

Brian Murphy
Austin, Texa