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.  



2 Replies
best response confirmed by perkin_warbeck (Contributor)


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)



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