Forum Discussion

perkin_warbeck's avatar
perkin_warbeck
Brass Contributor
May 15, 2021

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 n...
Excel
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    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...

HansVogelaar's avatar
HansVogelaar
MVP

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

djames313's avatar
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!

Resources