如何在自定义函数中用结构化引用为Excel图表指定X/Y轴数据源

完整代码 #

将以下代码放入Excel的标准模块中即可使用：

Function addTrend(chartName As String, sheetName As String, xData As Range, yData As Range) As Boolean
    On Error GoTo ErrHandler
    Dim targetSheet As Worksheet
    Dim targetChart As ChartObject
    ' 定位目标工作表
    Set targetSheet = ThisWorkbook.Sheets(sheetName)
    ' 定位目标图表
    Set targetChart = targetSheet.ChartObjects(chartName)
    ' 清空图表原有系列，避免数据残留
    Do While targetChart.Chart.SeriesCollection.Count > 0
        targetChart.Chart.SeriesCollection(1).Delete
    Loop
    ' 创建新数据系列
    Dim newSeries As Series
    Set newSeries = targetChart.Chart.SeriesCollection.NewSeries
    ' 绑定X轴、Y轴数据源
    newSeries.XValues = xData
    newSeries.Values = yData
    
    addTrend = True
    Exit Function
ErrHandler:
    ' 执行出错返回False
    addTrend = False
End Function

核心问题解答 #

1. 自定义函数中结构化引用的处理 #

在Excel中调用函数时传入的结构化引用（如MyDataTable[MyXDataHeader]）会被VBA自动解析为Range类型对象，不需要额外做格式转换，直接读取Range对象的属性即可获取对应数据，原生支持结构化引用、手动选中的单元格区域两种传参形式。

2. 指定图表的数据源导入 #

首先通过传入的工作表名、图表名定位到目标图表对象，先清空原有系列避免旧数据残留，再新建数据系列直接绑定传入的X轴、Y轴Range对象即可完成数据源更新，无需额外操作数据导入流程。

使用说明 #

• 调用示例：addTrend("Chart1","Sheet1",MyDataTable[MyXDataHeader],MyDataTable[MyYDataHeader])
• 受Excel工作表自定义函数安全规则限制，无法直接在单元格公式中调用该函数修改图表，可直接在VBA业务逻辑中调用，或者将函数改造为子过程绑定按钮触发执行。
• 传入的图表名称需要和工作表中图表的实际名称完全一致，避免匹配失败。

内容的提问来源于stack exchange，提问作者Karsten Mikal Kopperstad