VBA使用Range.Address设置图表数据源时报运行时错误1004如何解决

错误原因排查 #

• 变量Lcols未定义且未赋值：代码中多次使用Lcols作为列索引参数，但全程没有给这个变量赋值，运行时默认为0或者空值，导致Cells()调用直接出错，后续ChartRange、ChartRngTitles对象实际为空。
• 地址字符串多包裹了多余双引号：你构建RngStr的时候额外加了前后两层双引号，最终生成的字符串是"'UM - Monthly & YTD Trend'!$A$1:$L$1,..."格式，而Range()方法不需要外层的引号包裹，直接传入纯地址字符串即可。
• Range调用未指定所属工作表：你运行Range(RngStr)时当前激活的是图表工作表，全局Range默认指向当前激活的工作表，跨表地址直接传给当前工作表的Range方法就会触发1004错误。
• Cells未绑定工作表：你设置ChartRange时用的Cells()没有指定所属工作表，即使激活了数据源表，也存在隐式引用的风险，最好显式绑定。

修正后代码 #

Sub UBCharts()
    Dim Wb As Workbook, WsCharts As Worksheet, UBMainChart As ChartObject
    Dim UBMonthlyYTDSht As Worksheet, YearValue As Long
    Dim MatchStartRow As Long, MatchEndRow As Long, Lcols As Long
    Dim ChartRange As Range, ChartRngTitles As Range, FullSourceRange As Range
    
    Set Wb = ThisWorkbook
    Set WsCharts = Wb.Sheets("Trend Charts")
    Set UBMainChart = WsCharts.ChartObjects("UBMainChart")
    Set UBMonthlyYTDSht = Wb.Worksheets("UM - Monthly & YTD Trend")
    YearValue = WsCharts.Range("A1").Value
    
    ' 新增：给Lcols赋值，取第一行最后一列非空值的列号，可根据实际需求调整
    Lcols = UBMonthlyYTDSht.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Xrows = UBMonthlyYTDSht.Range("A" & Rows.Count).End(xlUp).Row
    MatchStartRow = Application.WorksheetFunction.Match(CLng(CDate(DateSerial(YearValue, 1, 1))), UBMonthlyYTDSht.Columns("A:A"), 0)
    MatchEndRow = Application.WorksheetFunction.Match(CLng(CDate(DateSerial(YearValue, Month(CLng((DateAdd("m", -1, Date)))), 1))), UBMonthlyYTDSht.Columns("A:A"), 0)
    
    UBMainChart.Chart.ChartArea.ClearContents
    
    ' 显式绑定Cells到数据源表，避免隐式引用错误
    Set ChartRange = UBMonthlyYTDSht.Range(UBMonthlyYTDSht.Cells(MatchStartRow, 1), UBMonthlyYTDSht.Cells(MatchEndRow, Lcols))
    Set ChartRngTitles = UBMonthlyYTDSht.Range(UBMonthlyYTDSht.Cells(1, 1), UBMonthlyYTDSht.Cells(1, Lcols))
    ' 用Union拼接两个不连续区域，不需要手动拼接地址字符串
    Set FullSourceRange = Union(ChartRngTitles, ChartRange)
    
    ' 直接传入拼接好的Range对象，不需要激活图表
    UBMainChart.Chart.SetSourceData Source:=FullSourceRange, PlotBy:=xlColumns
End Sub

补充说明：如果存在日期匹配不到的场景，建议给Match方法加错误捕获逻辑，避免匹配失败直接中断程序。

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