为现有Stacked Bar图表添加Line系列时出现VBA错误求助

已实现月度各分类耗时的堆叠柱状图，尝试在次坐标轴添加按月绘制累计工时的折线系列时，执行Set ns = chart.SeriesCollection.NewSeries语句出现Application-defined or object-defined error错误。

错误原因分析 #

• 堆叠柱状图的数据源technicalAreasRange未包含月份列（分类轴数据），导致图表分类轴未正确绑定，新增系列时无法匹配X轴维度
• cumulativeValues数组第一个值设为0，与月份数据的行数不匹配，引发数据关联错误

修正方案 #

1. 调整堆叠柱状图的数据源，包含月份列，确保分类轴正确初始化
2. 修正累计值数组的计算逻辑，第一个月的累计值直接取当月总工时，保证数组长度与月份数据一致
3. 新增系列时明确指定数据关联方式

修正后的完整代码 #

Sub CreateCombinationChartFromPivotTable()

Dim ws As Worksheet
Dim pt As PivotTable
Dim chartObj As ChartObject
Dim chart As Chart
Dim lastRow As Long
Dim monthRange As Range
Dim technicalAreasRange As Range
Dim totalHoursRange As Range
Dim cumulativeValues() As Double
Dim i As Long
Dim totalHours As Double

' 设置工作表和数据透视表对象
Set ws = ThisWorkbook.Sheets("Sheet1") ' 替换为你的工作表名
Set pt = ws.PivotTables("PivotTable5") ' 替换为你的数据透视表名

' 获取数据透视表的完整数据范围
lastRow = pt.TableRange2.Rows.Count
Set monthRange = pt.TableRange2.Columns(1) ' 第一列：年月

' 第2列到倒数第2列：技术分类（堆叠柱状图数据）
Set technicalAreasRange = pt.TableRange2.Columns(2).Resize(, pt.TableRange2.Columns.Count - 2)
' 调整堆叠图数据源，包含月份列（确保分类轴正确绑定）
Dim stackedChartSource As Range
Set stackedChartSource = Union(monthRange, technicalAreasRange)

' 最后一列：月度总工时（用于计算累计值）
Set totalHoursRange = pt.TableRange2.Columns(pt.TableRange2.Columns.Count)

' 初始化累计值数组
ReDim cumulativeValues(1 To lastRow)

' 添加新图表到工作表
Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=600, Top:=100, Height:=400)
Set chart = chartObj.Chart

' 设置堆叠柱状图类型并绑定包含月份的数据源
chart.ChartType = xlColumnStacked
chart.SetSourceData Source:=stackedChartSource

' 计算月度累计工时
For i = 1 To lastRow
    ' 确保单元格为数值类型
    If IsNumeric(totalHoursRange.Cells(i, 1).Value) Then
        totalHours = totalHoursRange.Cells(i, 1).Value
    Else
        totalHours = 0
    End If
    
    If i = 1 Then
        cumulativeValues(i) = totalHours ' 第一个月累计值为当月工时
    Else
        cumulativeValues(i) = cumulativeValues(i - 1) + totalHours
    End If
Next i

' 添加累计工时折线系列
Set ns = chart.SeriesCollection.NewSeries
With ns
    .XValues = monthRange
    .Values = cumulativeValues
    .ChartType = xlLine
    .AxisGroup = xlSecondary
    .Name = "累计工时"
End With

' 格式化图表
chart.HasTitle = True
chart.ChartTitle.Text = "月度工时分布（堆叠柱状+累计折线）"
chart.Axes(xlValue, xlPrimary).HasTitle = True
chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "分类工时"
chart.Axes(xlValue, xlSecondary).HasTitle = True
chart.Axes(xlValue, xlSecondary).AxisTitle.Text = "累计工时"

' 调整图表尺寸和位置
chartObj.Width = 800
chartObj.Height = 500
chartObj.Left = 150
chartObj.Top = 50

End Sub

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