Question about the VBA SeriesCollection method

According to Microsoft, the SeriesCollection method is defined as

expression.SeriesCollection (Index)

where expression is a variable that represents a Chart object and Index is "the name or number of the series."

I am trying to use this method with a name:

Dim seriesName as String

seriesName = "foo"

Worksheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(seriesName).Format.line.ForeColor.RGB = RGB(255, 0, 0)

But this gives a runtime type mismatch error. However if I use the numeric index

Worksheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(2).Format.line.ForeColor.RGB = RGB(255, 0, 0)

it works. I have tried declaring seriesName both as String and as Variant, but it gives the same result. What am I doing wrong?

Incidentally, this code worked just fine when I depended on ActiveChart

ActiveChart.SeriesCollection(seriesName).Format.line.ForeColor.RGB = RGB(255, 0, 0)

However it is important that I refer to the chart without activating it.

I got the same error, but it worked when I broke the code down into small steps:

    Dim wsh As Worksheet
    Dim obj As ChartObject
    Dim cht As Chart
    Dim ser As Series
    Dim seriesName As String
    Set wsh = Worksheets("Sheet1")
    Set obj = wsh.ChartObjects(1)
    Set cht = obj.Chart
    seriesName = "foo"
    Set ser = cht.SeriesCollection(seriesName)
    ser.Format.Line.ForeColor.RGB = RGB(255, 0, 0)

Weird...

HansVogelaar
MVP
May 16, 2021
perkin_warbeck

I got the same error, but it worked when I broke the code down into small steps:

Dim wsh As Worksheet Dim obj As ChartObject Dim cht As Chart Dim ser As Series Dim seriesName As String Set wsh = Worksheets("Sheet1") Set obj = wsh.ChartObjects(1) Set cht = obj.Chart seriesName = "foo" Set ser = cht.SeriesCollection(seriesName) ser.Format.Line.ForeColor.RGB = RGB(255, 0, 0)

Weird...
- perkin_warbeck
  Brass Contributor
  May 16, 2021
  I also tried breaking it down, but I obviously didn't break it down far enough. Thank you!
- djames313
  Copper Contributor
  Jun 18, 2024
  HansVogelaar
  I struggled with this too. The answer relates to how VBA in Excel variously handles " or "" or """.
  
  If .SeriesCollection(4).name is Speed then .SeriesCollection("Speed").name will give "Speed"
  
  However a using string variable as Tmp and setting Tmp = "Speed"
  .SeriesCollection(Tmp).name will fail!
  BUT .SeriesCollection("" & Tmp & "").name works!

Forum Discussion

Question about the VBA SeriesCollection method

Share

Resources