SOLVED

Question about the VBA SeriesCollection method

%3CLINGO-SUB%20id%3D%22lingo-sub-2357689%22%20slang%3D%22en-US%22%3EQuestion%20about%20the%20VBA%20SeriesCollection%20method%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357689%22%20slang%3D%22en-US%22%3E%3CP%3EAccording%20to%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Fexcel.chart.seriescollection%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EMicrosoft%3C%2FA%3E%2C%20the%20SeriesCollection%20method%20is%20defined%20as%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3Eexpression%3C%2FEM%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSTRONG%3ESeriesCollection%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CEM%3EIndex%3C%2FEM%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ewhere%20expression%20is%20a%20variable%20that%20represents%20a%20Chart%20object%20and%20Index%20is%20%22the%20name%20or%20number%20of%20the%20series.%22%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20trying%20to%20use%20this%20method%20with%20a%20name%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EDim%20seriesName%20as%20String%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EseriesName%20%3D%20%22foo%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWorksheets(%22Sheet1%22).ChartObjects(1).Chart.SeriesCollection(seriesName).Format.line.ForeColor.RGB%20%3D%20RGB(255%2C%200%2C%200)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBut%20this%20gives%20a%20runtime%20type%20mismatch%20error.%26nbsp%3B%20However%20if%20I%20use%20the%20numeric%20index%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWorksheets(%22Sheet1%22).ChartObjects(1).Chart.SeriesCollection(2).Format.line.ForeColor.RGB%20%3D%20RGB(255%2C%200%2C%200)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eit%20works.%20I%20have%20tried%20declaring%20seriesName%20both%20as%20String%20and%20as%20Variant%2C%20but%20it%20gives%20the%20same%20result.%20What%20am%20I%20doing%20wrong%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIncidentally%2C%20this%20code%20worked%20just%20fine%20when%20I%20depended%20on%20ActiveChart%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveChart.SeriesCollection(seriesName).Format.line.ForeColor.RGB%20%3D%20RGB(255%2C%200%2C%200)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHowever%20it%20is%20important%20that%20I%20refer%20to%20the%20chart%20without%20activating%20it.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2357689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2358576%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20about%20the%20VBA%20SeriesCollection%20method%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2358576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382386%22%20target%3D%22_blank%22%3E%40perkin_warbeck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20got%20the%20same%20error%2C%20but%20it%20worked%20when%20I%20broke%20the%20code%20down%20into%20small%20steps%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E%20%20%20%20Dim%20wsh%20As%20Worksheet%0A%20%20%20%20Dim%20obj%20As%20ChartObject%0A%20%20%20%20Dim%20cht%20As%20Chart%0A%20%20%20%20Dim%20ser%20As%20Series%0A%20%20%20%20Dim%20seriesName%20As%20String%0A%20%20%20%20Set%20wsh%20%3D%20Worksheets(%22Sheet1%22)%0A%20%20%20%20Set%20obj%20%3D%20wsh.ChartObjects(1)%0A%20%20%20%20Set%20cht%20%3D%20obj.Chart%0A%20%20%20%20seriesName%20%3D%20%22foo%22%0A%20%20%20%20Set%20ser%20%3D%20cht.SeriesCollection(seriesName)%0A%20%20%20%20ser.Format.Line.ForeColor.RGB%20%3D%20RGB(255%2C%200%2C%200)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWeird...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2359250%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20about%20the%20VBA%20SeriesCollection%20method%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2359250%22%20slang%3D%22en-US%22%3EI%20also%20tried%20breaking%20it%20down%2C%20but%20I%20obviously%20didn't%20break%20it%20down%20far%20enough.%20Thank%20you!%3C%2FLINGO-BODY%3E
Contributor

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

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