Search code examples
excelvbacharts

ComboCharts for more than 6 data series

I am unable to create a Combochart in VBA Excel with more than 5 data series. Up to 5 data series, it works fine and as soon as I have 6th one, I get error: "Runtime error 1004, Parameter not valid". As you can see it is not a lot of data, there seems to be an issue with more than 5 data series in the ComboChart. The first series is barChart (primary y-axis), rest are Scatter (all in secondary y-axis). Please advice what may be going on here.

This is my code

Sub CreateComboChart()
    Dim ws As Worksheet
    Dim chtObj As ChartObject
    Dim cht As Chart
    Dim rngData As Range
    Dim rngCategories As Range
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Perf")
    

    Set rngData = ws.Range("B2:F10")
    Set rngCategories = ws.Range("B1:F1")
    
    ' Create a new chart
    Set chtObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)
    Set cht = chtObj.Chart
    
    ' Set the chart type to combo
    cht.ChartType = xlColumnClustered
    
    ' Add data to the chart
    cht.SetSourceData Source:=rngData
    cht.SeriesCollection(1).XValues = rngCategories
    cht.FullSeriesCollection(1).AxisGroup = 1
    ' Set the chart type for the first row (bars)
    cht.SeriesCollection(1).ChartType = xlColumnClustered
    
    cht.FullSeriesCollection(2).AxisGroup = 2
    cht.SeriesCollection(2).ChartType = xlXYScatter
    cht.FullSeriesCollection(2).MarkerStyle = 8
        
    cht.FullSeriesCollection(3).AxisGroup = 2
    cht.SeriesCollection(3).ChartType = xlXYScatter
    cht.FullSeriesCollection(3).MarkerStyle = 3
    
    cht.FullSeriesCollection(4).AxisGroup = 2
    cht.SeriesCollection(4).ChartType = xlXYScatter
    cht.FullSeriesCollection(4).MarkerStyle = 8
    
    cht.FullSeriesCollection(5).AxisGroup = 2
    cht.SeriesCollection(5).ChartType = xlXYScatter
    cht.FullSeriesCollection(5).MarkerStyle = 3
    ' STOPS here
    cht.FullSeriesCollection(6).AxisGroup = 2
    cht.SeriesCollection(6).ChartType = xlXYScatter
    cht.FullSeriesCollection(6).MarkerStyle = 8
        
    cht.FullSeriesCollection(7).AxisGroup = 2
    cht.SeriesCollection(7).ChartType = xlXYScatter
    cht.FullSeriesCollection(7).MarkerStyle = 3
End Sub

This is the data

Plant   P1  P2  P3  P4  P5
A1  1500    7600    1300    1800    1500
A2  11.5%   12.2%   3.6%    3.3%    9.9%
A3  17.8%   19.8%   0.0%    0.0%    13.4%
A4  10.1%   10.1%   3.5%    1.4%    9.1%
A5  14.7%   15.0%   13.0%   15.1%   11.3%
A6  47.6%   55.3%   24.1%   27.2%   36.5%
A7  13.5%   11.9%   12.2%   14.0%   10.6%
A8  43.1%   40.7%   21.8%   23.6%   33.3%

In the above code if I change to Set rngData = ws.Range("B2:F6"), and remove seriescollection 6 and 7 I get this chart. But does not work with "B2:F9".

enter image description here

Solution

  • Your issue may be "plot by rows" vs "plot by columns"...

    I prefer to add each series one-by-one, since it gives you more control:

    Sub CreateComboChart()
    Dim ws As Worksheet, chtObj As chartObject
    Dim cht As Chart, rngData As Range
    Dim rngCategories As Range, i As Long, ms
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Perf")
    
    ws.ChartObjects(1).Delete 'remove existing plot

    Set rngData = ws.Range("B2:F10")
    Set rngCategories = ws.Range("B1:F1")
    
    ' Create a new chart
    Set chtObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)
    Set cht = chtObj.Chart
    
    'remove any "auto-plotted" series from the chart
    Do While cht.SeriesCollection.Count > 0
        cht.SeriesCollection(1).Delete
    Loop
    
    'add the first series (bars)
    With cht.SeriesCollection.NewSeries
        .ChartType = xlColumnStacked
        .XValues = rngCategories
        .Values = rngData.Rows(1)
        .AxisGroup = 1
    End With
    
    ms = 8 'marker style
    'add the rest of the series
    For i = 2 To rngData.Rows.Count
        With cht.SeriesCollection.NewSeries
            .ChartType = xlXYScatter
            .XValues = rngCategories
            .Values = rngData.Rows(i)
            .AxisGroup = 2
            .MarkerStyle = ms
            ms = IIf(ms = 8, 3, 8) 'toggle marker style
        End With
    Next i
    
End Sub

    Plot:

    enter image description here