如何用VBA在同一图表绘制多组数据集？代码报错求助

问题背景 #

数据集格式如下：

x_val  set1 set2 set3 set4 set5 ...
1.1    12   36   12   23   33   ...
1.2    44   22   11   1    13   ...
1.3    54   5    56   56   34   ...
1.4    1    2    6    12   33   ...

需求是在同一图表中绘制任意多组(setX vs x_val)的XY散点图，通过Coefs工作表的F13:Fn指定数据源工作表名称，G1、G2分别选择数据集中的X列（如2对应x_val列）和Y列（如3对应set1列）。现有VBA代码生成的图表异常，怀疑错误出在以下两行：

dataRanges.Add dataRange.Columns(xColumn)
dataRanges.Add dataRange.Columns

错误分析 #

1. 数据范围选取错误：
   • 原代码中Set dataRange = dataSheet.Range("B2:C" & lastRow)固定了B到C列，但实际数据集包含更多列，无法匹配用户通过G1/G2指定的列索引。
   • dataRange.Columns未指定列索引，会将整个dataRange添加到集合，导致后续数据配对混乱。
2. 循环内重复读取配置：xColumn和yColumn是全局配置，不需要在每个工作表循环中重复读取。
3. 数据合并逻辑错误：Range(dataRanges(j), dataRanges(j + 1))试图跨工作表合并范围，这在VBA中会导致引用错误，应该直接分别赋值X/Y数据范围。

修复后的代码 #

Sub PlotMultiXYScatter()
    ' Step 1: 读取全局配置（移到循环外，避免重复读取）
    Dim coefsSheet As Worksheet
    Set coefsSheet = ThisWorkbook.Sheets("Coefs")
    
    Dim sheetNames As Range
    Set sheetNames = coefsSheet.Range("F13:F15")
    
    Dim xColumn As Integer
    xColumn = coefsSheet.Range("G1").Value
    
    Dim yColumn As Integer
    yColumn = coefsSheet.Range("G2").Value
    
    Dim dataRanges As Collection
    Set dataRanges = New Collection
    
    Dim i As Long
    For i = 1 To sheetNames.Rows.Count
        Dim sheetName As String
        sheetName = sheetNames.Cells(i, 1).Value
        
        Dim dataSheet As Worksheet
        Set dataSheet = ThisWorkbook.Sheets(sheetName)
        
        ' 获取数据区域最后一行（从X列判断，更准确）
        Dim lastRow As Long
        lastRow = dataSheet.Cells(dataSheet.Rows.Count, xColumn).End(xlUp).Row
        
        ' 获取当前工作表的X列和Y列数据范围（从第2行开始，跳过表头）
        Dim xRange As Range
        Set xRange = dataSheet.Range(dataSheet.Cells(2, xColumn), dataSheet.Cells(lastRow, xColumn))
        
        Dim yRange As Range
        Set yRange = dataSheet.Range(dataSheet.Cells(2, yColumn), dataSheet.Cells(lastRow, yColumn))
        
        ' 将X和Y范围成对添加到集合
        dataRanges.Add xRange
        dataRanges.Add yRange
    Next i
    
    ' Step 2: 创建图表并绘制数据
    Dim chartSheet As Worksheet
    Set chartSheet = coefsSheet
    
    Dim chartObject As ChartObject
    Set chartObject = chartSheet.ChartObjects.Add(Left:=300, Width:=600, Top:=300, Height:=400) ' 调整默认尺寸更合理
    
    With chartObject.Chart
        .ChartType = xlXYScatter
        
        ' 添加每个数据对为新系列
        Dim j As Long
        For j = 1 To dataRanges.Count Step 2
            Dim series As Series
            Set series = .SeriesCollection.NewSeries
            
            ' 直接赋值X/Y范围，无需合并
            series.XValues = dataRanges(j)
            series.Values = dataRanges(j + 1)
            ' 设置系列名称为对应工作表名，方便图例区分
            series.Name = ThisWorkbook.Sheets(sheetNames.Cells((j + 1) / 2, 1).Value).Name
        Next j
        
        ' 设置图表标题和轴标题
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "XX"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "YY"
        .HasLegend = True
        .HasTitle = True
        .ChartTitle.Text = "TTITLE"
    End With
End Sub

关键修改点 #

• 将全局配置（xColumn、yColumn）移到循环外，提升效率。
• 动态获取X/Y列的准确数据范围，不再固定列区间。
• 成对添加X/Y范围到集合，确保后续配对正确。
• 直接为系列赋值X/Y范围，避免跨工作表范围合并的错误。
• 添加系列名称为对应工作表名，图例更清晰。
• 调整图表默认尺寸，提升可读性。

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