Excel VBA refer to chart seriescollection by string name

Question

I have a need to refer to a seriescollection by name. The MSDN object model page for seriescollection states either an index or a name can be used. However when I try to use a string variable I get error 1004 'Invalid parameter':

Dim sSeriesName As String
dim aRootCats() as string
Dim cSeriesCol As SeriesCollection

For x = 0 To UBound(aRootCats)
    sSeriesName = aRootCats(x)
    Set cSeriesCol = cChart.ChartGroups(1).SeriesCollection(sSeriesName)
Next x

I've tried the following too:

    sSeriesName = CStr(aRootCats(x)) ' just in case, because I filled this array with variant data type earlier
    Set cSeriesCol = cChart.ChartGroups(1).SeriesCollection(sSeriesName)

and

    Set cSeriesCol = cChart.ChartGroups(1).SeriesCollection(aRootCats(x))

and

    Set cSeriesCol = cChart.ChartGroups(1).SeriesCollection("Product Support")

UPDATE: I'm also experiencing the same error when looping on the seriescollection:

For c = 1 To cChart.ChartGroups(1).SeriesCollection.Count
    Set cSeriesCol = cChart.ChartGroups(1).SeriesCollection(c)
Next c

Any ideas?

Many thanks.

Generally you'd get that error if the series name doesn't match. — Rory
– Rory, Commented Mar 2, 2016 at 14:45
Thanks but it definitely does, in debugging I got the exact names with cChart.ChartGroups(1).SeriesCollection(1).name etc — Absinthe
– Absinthe, Commented Mar 2, 2016 at 14:46
So testing something like aRootCats(x) = cChart.ChartGroups(1).SeriesCollection(1).name returns True? — Rory
– Rory, Commented Mar 2, 2016 at 14:51
Yes, I get a true / false as appropriate. I'm thinking of doing this with another loop inside the first, this time on the series collection - is that your thought too? — Absinthe
– Absinthe, Commented Mar 2, 2016 at 14:53
Not really, since I can't replicate your error other than when the series name is incorrect. Can you put a file somewhere (OneDrive / Dropbox etc) showing the problem? If it's a bug, I'll report it. Which version of Excel are you using? — Rory
– Rory, Commented Mar 2, 2016 at 14:55

Rory · Accepted Answer · 2016-03-02 15:36:03Z

1

Because you are using a ChartGroup not a Chart, you have to use the Item property of the SeriesCollection and you have to pass a variant. Use either:

cChart.ChartGroups(1).SeriesCollection.Item(1)

or:

cChart.ChartGroups(1).SeriesCollection.Item(CVar(sSeriesName))

(or declare the variable as Variant rather than String)

answered Mar 2, 2016 at 15:36

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Absinthe Over a year ago

Yes, declaring as variant works. Thanks for all your help.

Collectives™ on Stack Overflow

Excel VBA refer to chart seriescollection by string name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related