如何用Excel VBA基于两个工作表生成可自动更新的多行折线图？

原代码的问题梳理 #

• 变量a未声明，不符合VBA编码规范，易引发莫名错误
• 图表标题写法错误：直接写死字符串"='Data Generation'!(i, 2)"，无法获取单元格实际内容，需通过工作表对象引用单元格值
• 重复调用SetSourceData，第二次直接覆盖第一次的数据源，且未指定工作表，导致引用当前活动表而非「Data Generation」的正确数据
• 变量名拼写错误：strchr与strchrt不一致，后续定位图表会失败
• 图表位置更新语法错误：ntop = ntop + ActiveSheet.Shapes(strchrt).Height = 20是非法赋值，需改为累加图表高度加间距
• For循环内手动执行i = i + 1，加上循环本身的自动步长，导致跳过一半数据
• 未明确指定图表存放的目标工作表，依赖ActiveSheet易因切换工作表出错

修正后的完整代码 #

Sub GenerateLineCharts()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim i As Long
    Dim nTop As Long, nLeft As Long
    Dim chartObj As ChartObject
    Dim chartTitle As String
    Dim dataRange As Range
    
    ' 指定数据源工作表
    Set wsSource = ThisWorkbook.Worksheets("Data Generation")
    ' 替换为你实际要放图表的类别工作表名称，比如"类别A"
    Set wsTarget = ThisWorkbook.Worksheets("类别A")
    
    ' 图表初始位置和间距
    nLeft = 20
    nTop = 20
    Const CHART_SPACING As Long = 20 ' 图表垂直间距，可按需调整
    
    ' 遍历数据源第34到53行，范围可根据实际修改
    For i = 34 To 53
        ' 获取当前行的标题（第2列内容）
        chartTitle = wsSource.Cells(i, 2).Value
        
        ' 定义当前行的数据源范围：H列到AK列（对应第8到37列）
        Set dataRange = wsSource.Range(wsSource.Cells(i, 8), wsSource.Cells(i, 37))
        
        ' 在目标工作表添加图表，直接指定位置和大小
        Set chartObj = wsTarget.ChartObjects.Add( _
            Left:=nLeft, Top:=nTop, Width:=400, Height:=250)
        
        ' 设置图表属性
        With chartObj.Chart
            .ChartType = xlLine
            .SetSourceData Source:=dataRange ' 绑定数据源
            .HasTitle = True
            .ChartTitle.Text = chartTitle ' 设置标题
            ' 可选：添加坐标轴标题，让图表更清晰
            .Axes(xlCategory).HasTitle = True
            .Axes(xlCategory).AxisTitle.Text = "数据点"
            .Axes(xlValue).HasTitle = True
            .Axes(xlValue).AxisTitle.Text = "数值"
        End With
        
        ' 更新下一个图表的顶部位置
        nTop = nTop + chartObj.Height + CHART_SPACING
    Next i
End Sub

关键细节说明 #

1. 明确工作表对象：用Set指定数据源和目标工作表，避免依赖ActiveSheet导致的切换错误
2. 数据源正确绑定：直接引用「Data Generation」的单元格区域，原始数据更新时图表会自动同步（若未自动更新，检查Excel选项的「自动重算」是否开启）
3. 变量规范：所有变量均声明，变量名统一，避免低级错误
4. 布局灵活：用常量CHART_SPACING控制图表间距，图表宽高可按需调整
5. 自动更新机制：只要图表数据源为直接引用的单元格，Excel默认会自动更新图表内容

扩展：给所有类别工作表批量生成图表 #

如果要给所有非数据源的工作表生成对应图表，可添加遍历逻辑：

Sub GenerateAllCategoryCharts()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim rowStart As Long, rowEnd As Long
    
    Set wsSource = ThisWorkbook.Worksheets("Data Generation")
    
    ' 遍历所有工作表，跳过数据源表
    For Each wsTarget In ThisWorkbook.Worksheets
        If wsTarget.Name <> "Data Generation" Then
            ' 根据工作表名称对应数据源行范围，自行调整
            Select Case wsTarget.Name
                Case "类别A"
                    rowStart = 34: rowEnd = 53
                Case "类别B"
                    rowStart = 54: rowEnd = 73
                Case "类别C"
                    rowStart = 74: rowEnd = 93
                ' 继续添加更多类别对应关系
            End Select
            
            ' 调用单独的生成过程
            Call GenerateChartsForTarget(wsSource, wsTarget, rowStart, rowEnd)
        End If
    Next wsTarget
End Sub

' 带参数的图表生成过程，方便复用
Sub GenerateChartsForTarget(wsSource As Worksheet, wsTarget As Worksheet, rowStart As Long, rowEnd As Long)
    Dim i As Long
    Dim nTop As Long, nLeft As Long
    Dim chartObj As ChartObject
    Dim chartTitle As String
    Dim dataRange As Range
    
    nLeft = 20
    nTop = 20
    Const CHART_SPACING As Long = 20
    
    For i = rowStart To rowEnd
        chartTitle = wsSource.Cells(i, 2).Value
        Set dataRange = wsSource.Range(wsSource.Cells(i, 8), wsSource.Cells(i, 37))
        
        Set chartObj = wsTarget.ChartObjects.Add(Left:=nLeft, Top:=nTop, Width:=400, Height:=250)
        
        With chartObj.Chart
            .ChartType = xlLine
            .SetSourceData Source:=dataRange
            .HasTitle = True
            .ChartTitle.Text = chartTitle
            .Axes(xlCategory).HasTitle = True
            .Axes(xlCategory).AxisTitle.Text = "数据点"
            .Axes(xlValue).HasTitle = True
            .Axes(xlValue).AxisTitle.Text = "数值"
        End With
        
        nTop = nTop + chartObj.Height + CHART_SPACING
    Next i
End Sub

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