如何在Excel VBA中将命名区域data_name&"abs"设为图表X轴？

一、数据源失效的修复原因及代码修改 #

SetSourceData方法要求传入Range对象，但原代码直接传入了Name对象，导致数据源无法正确识别。修改方式是通过Name.RefersToRange获取命名区域对应的实际单元格范围：

' 修正后的数据源设置
graph.Chart.SetSourceData Source:=ActiveWorkbook.Names(data_name & "ord").RefersToRange

二、设置X轴数值的方法 #

图表创建后默认会生成一个数据系列，直接给该系列的XValues属性赋值即可绑定X轴命名区域：

' 将data_name&"abs"设为X轴数值
graph.Chart.SeriesCollection(1).XValues = ActiveWorkbook.Names(data_name & "abs").RefersToRange

三、命名区域定义的优化建议 #

原命名区域中OFFSET的高度参数直接引用单元格地址，若单元格格式为文本可能导致范围计算错误，建议用INDIRECT确保获取单元格数值：

' 优化后的数据源命名区域
ActiveWorkbook.Names.Add Name:=data_name & "ord", RefersTo:= _
    "=OFFSET(data_calc!" & .Cells(9, c_write + 3).Address & ",0,0,INDIRECT(""data_calc!" & .Cells(6, c_write + 3).Address & """)-1)"
' 优化后的X轴数值命名区域
ActiveWorkbook.Names.Add Name:=data_name & "abs", RefersTo:= _
    "=OFFSET(data_calc!" & .Cells(9, c_write + 2).Address & ",0,0,INDIRECT(""data_calc!" & .Cells(6, c_write + 3).Address & """)-1)"

完整修正后的代码片段 #

' 数据源命名区域（优化版）
ActiveWorkbook.Names.Add Name:=data_name & "ord", RefersTo:= _
    "=OFFSET(data_calc!" & .Cells(9, c_write + 3).Address & ",0,0,INDIRECT(""data_calc!" & .Cells(6, c_write + 3).Address & """)-1)"
' X轴数值命名区域（优化版）
ActiveWorkbook.Names.Add Name:=data_name & "abs", RefersTo:= _
    "=OFFSET(data_calc!" & .Cells(9, c_write + 2).Address & ",0,0,INDIRECT(""data_calc!" & .Cells(6, c_write + 3).Address & """)-1)"

Dim graph As ChartObject
' 创建图表
Set graph = .ChartObjects.Add( _
    Left:=.Cells(r_write + 7, c_write + 1).Left, _
    Width:=.Cells(r_write + 7, c_write + 1).Width + .Cells(r_write + 7, c_write + 2).Width + .Cells(r_write + 7, c_write + 3).Width + .Cells(r_write + 7, c_write + 4).Width + .Cells(r_write + 7, c_write + 5).Width, _
    Top:=.Cells(r_write + 7, c_write + 1).Top, _
    Height:=.Cells(r_write + 7, c_write + 1).Height + .Cells(r_write + 8, c_write + 1).Height + .Cells(r_write + 9, c_write + 1).Height + .Cells(r_write + 10, c_write + 1).Height + .Cells(r_write + 11, c_write + 1).Height _
    + .Cells(r_write + 12, c_write + 1).Height + .Cells(r_write + 13, c_write + 1).Height + .Cells(r_write + 14, c_write + 1).Height + .Cells(r_write + 15, c_write + 1).Height + .Cells(r_write + 16, c_write + 1).Height _
    + .Cells(r_write + 17, c_write + 1).Height + .Cells(r_write + 18, c_write + 1).Height + .Cells(r_write + 19, c_write + 1).Height + .Cells(r_write + 20, c_write + 1).Height + .Cells(r_write + 21, c_write + 1).Height)
' 修正后的数据源设置
graph.Chart.SetSourceData Source:=ActiveWorkbook.Names(data_name & "ord").RefersToRange
' 设置X轴数值
graph.Chart.SeriesCollection(1).XValues = ActiveWorkbook.Names(data_name & "abs").RefersToRange

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