Excel VBA：嵌入式图表使用Range函数时多变量赋值问题

我在工作表中使用VBA尝试多次复制粘贴嵌入式图表，为每个新图表分配不同的数据范围。每个图表的Y轴包含3个数据系列，X轴包含1个数据系列。但代码第29行无法用变量p、q、r替换固定的83、104、293来赋值，原代码如下：

Sub CopyChart()
Dim Sht As Worksheet
Dim chrt As Chart
Dim n As Integer
Dim k As Integer
Dim p As Integer
Dim q As Integer
Dim r As Integer
Set Sht = ThisWorkbook.Sheets("Sheet2")
Set ShtBND1 = ThisWorkbook.Sheets("BND1")
Set chrt = Sht.ChartObjects("Chart 1").Chart
n = 99
k = 2
p = 83
q = 104
r = 293
chrt.ChartArea.Copy

Do Until k >= 4
Sht.Range("A1").Select
ActiveSheet.Paste
    With ActiveChart.Parent
         .Top = n    ' reposition new chart
         .Left = 180   ' reposition new chart 
         .Name = "Chart " & k
     End With
     
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Range("BND1!$A$2:$EZ$2,BND1!$A$83:$EZ$83,BND1!$A$104:$EZ$104,BND1!$A$293:$EZ$293")

'I am having issues to assign the variables p, q, r in above code in the place of 83, 104, 293

ActiveChart.PlotBy = xlColumns
ActiveChart.PlotBy = xlRows
    
n = n + 92
k = k + 1
p = p + 1
q = q + 1
r = r + 1
Loop
End Sub

解决方案 #

问题核心是你直接将变量写入了固定字符串中，VBA不会自动解析字符串内的变量。需要通过字符串拼接生成正确的数据源范围，同时优化代码避免依赖Select/Activate，提升稳定性。

修改后的代码：

Sub CopyChart()
    Dim Sht As Worksheet
    Dim chrt As Chart
    Dim newChrtObj As ChartObject
    Dim n As Integer
    Dim k As Integer
    Dim p As Integer
    Dim q As Integer
    Dim r As Integer
    Dim sourceRangeStr As String

    Set Sht = ThisWorkbook.Sheets("Sheet2")
    Set ShtBND1 = ThisWorkbook.Sheets("BND1")
    Set chrt = Sht.ChartObjects("Chart 1").Chart
    
    n = 99
    k = 2
    p = 83
    q = 104
    r = 293
    
    chrt.ChartArea.Copy

    Do Until k >= 4
        Sht.Range("A1").Select
        ActiveSheet.Paste
        ' 获取刚粘贴的图表对象，避免依赖ActiveChart
        Set newChrtObj = Sht.ChartObjects(Sht.ChartObjects.Count)
        
        With newChrtObj
             .Top = n    ' 重新定位新图表
             .Left = 180 ' 重新定位新图表
             .Name = "Chart " & k
        End With
        
        ' 拼接数据源范围，用变量替换固定行号
        sourceRangeStr = "BND1!$A$2:$EZ$2,BND1!$A$" & p & ":$EZ$" & p & _
                        ",BND1!$A$" & q & ":$EZ$" & q & ",BND1!$A$" & r & ":$EZ$" & r
        
        newChrtObj.Chart.SetSourceData Source:=Range(sourceRangeStr)
        ' 保留最终需要的PlotBy设置，去掉重复操作
        newChrtObj.Chart.PlotBy = xlRows
        
        n = n + 92
        k = k + 1
        p = p + 1
        q = q + 1
        r = r + 1
    Loop
End Sub

关键修改点 #

• 通过"BND1!$A$" & p & ":$EZ$" & p的字符串拼接方式，将变量p、q、r插入到单元格引用中，生成动态的数据源范围
• 新增newChrtObj变量直接引用粘贴后的图表对象，避免依赖ActiveChart，减少代码出错概率
• 移除重复的PlotBy设置，只保留最终需要的xlRows

内容的提问来源于stack exchange，提问作者Santhana Krishnan Prakash