CODEWITHSUNDEEP
vbaexcelcharts
Absinthe
Absinthe

Reputation: 3391

Excel VBA refer to chart seriescollection by string name

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.

Upvotes: 0

Views: 2826

Answers (1)

Rory
Rory

Reputation: 34075

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)

Upvotes: 1

Related Questions