Excel VBA ComboChart超5个数据系列触发Runtime error 1004求助

问题 #

使用Excel VBA创建组合图（ComboChart）时，最多仅能添加5个数据系列，添加第6个系列时会抛出Runtime error 1004，参数无效错误。其中第一个系列为绑定主Y轴的柱形图，其余均为绑定次Y轴的散点图。缩小数据范围（仅保留前5个系列）时代码可正常运行，但扩展至第6个及以上系列就会报错。

原代码 #

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

数据示例 #

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%

问题原因与解决方法 #

核心原因 #

1. 散点图数据结构要求未满足：散点图需要明确的X/Y值对，但原代码直接将普通数据范围绑定到散点图系列，未单独为每个散点图系列指定X值。当系列数量超过5个时，Excel无法自动解析符合散点图要求的数据结构，触发参数错误。
2. 批量绑定数据的校验冲突：一次性绑定整个数据范围后再逐个修改系列类型和轴组的方式，在系列数量增加时容易触发Excel内部的参数校验逻辑报错。

修正后的代码 #

Sub CreateComboChart_Fixed()
    Dim ws As Worksheet
    Dim chtObj As ChartObject
    Dim cht As Chart
    Dim rngCategories As Range
    Dim i As Integer
    
    Set ws = ThisWorkbook.Worksheets("Perf")
    Set rngCategories = ws.Range("B1:F1") ' X轴分类值
    
    ' 创建新图表
    Set chtObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)
    Set cht = chtObj.Chart
    cht.ChartType = xlColumnClustered ' 初始设为柱形图
    
    ' 添加第一个系列（柱形图，主Y轴）
    With cht.SeriesCollection.NewSeries
        .Name = ws.Range("A2").Value
        .Values = ws.Range("B2:F2")
        .XValues = rngCategories
        .AxisGroup = 1
        .ChartType = xlColumnClustered
    End With
    
    ' 循环添加第2到第8个系列（散点图，次Y轴）
    For i = 3 To 10
        With cht.SeriesCollection.NewSeries
            .Name = ws.Range("A" & i).Value
            ' 散点图需明确X/Y值，这里X用分类位置序号，Y用对应行数据
            .XValues = Array(1, 2, 3, 4, 5) ' 也可直接用rngCategories.Value
            .Values = ws.Range("B" & i & ":F" & i)
            .AxisGroup = 2
            .ChartType = xlXYScatter
            ' 交替设置标记样式
            .MarkerStyle = IIf((i - 2) Mod 2 = 1, 8, 3)
        End With
    Next i
End Sub

关键修改点 #

• 逐个创建系列：不再一次性绑定整个数据范围，通过NewSeries逐个生成系列，确保每个系列的数据结构符合图表类型要求。
• 明确散点图X/Y值：为每个散点图系列指定清晰的X值（分类序号或分类标签），解决Excel无法自动解析散点图数据的问题。
• 循环简化逻辑：用循环替代重复的系列设置代码，避免手动编写重复代码的失误，同时支持任意数量的系列扩展。

内容的提问来源于stack exchange，提问作者user2043446