Question about the VBA SeriesCollection method

[perkin_warbeck]

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.  

[#2 · Accepted solution]

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

[#3]

I also tried breaking it down, but I obviously didn't break it down far enough. Thank you!

[#4]

@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!