使用VBA巧妙获取图表数据源区域

在日常工作中，使用VBA操作Excel图表是经常遇到的场景。

示例图表如下：

使用如下代码可以更新图表的数据源区域，增加一个数据系列。

Sub UpdateChart()
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$D$6")
End Sub

更新后的图表如下所示。

代码很简单，即使大家不知道SetSourceData方法，也可以通过录制宏的方法获得代码。

现在问题来了，如何读取图表的数据源区域呢？Chart对象并没有SourceData属性，也没有GetSourceData方法。这只能说明微软并未提供相应的接口，我们仍然可以使用变通的方法来获取图表的数据源区域。

示例代码如下：

Sub GetDataSource()
    Dim objCht As chart, sFormula As String
    Dim aTxt, topRightCell As Range, bottomLeftCell As Range
    Set objCht = ActiveSheet.ChartObjects(1).chart
    sFormula = objCht.SeriesCollection(1).Formula
    aTxt = Split(sFormula, ",")
    With Range(aTxt(1))
        Set topRightCell = .Cells(.Cells.Count)
    End With
    sFormula = objCht.SeriesCollection(objCht.SeriesCollection.Count).Formula
    aTxt = Split(sFormula, ",")
    Set bottomLeftCell = Range(aTxt(2)).Cells(1)
    With Range(topRightCell, bottomLeftCell)
        Debug.Print "图表数据源所在工作表：" & .Parent.Name
        Debug.Print "图表数据源地址：" & .Address
    End With
End Sub

【代码解析】
第4行代码获取活动工作表中的第一个Chart对象，需要注意的是ChartObject和Chart是两个不同的对象，Chart对象是ChartObject对象的子对象。
第5行代码获取图表中第一个系列的公式，输出如下所示。

 =SERIES(Sheet11!$A$2,Sheet1!$B$1:$D$1,Sheet1!$B$2:$D$2,1)

其语法格式如下：

=SERIES(<series name>,<x values>,<y values>,<plot order>)

第6行代码使用逗号作为分隔符拆分公式，结果数组下标下界为零。
第7行代码中aTxt(1)为数组中第二个元素，即x轴的值对应的单元格区域（下文简称为x轴区域），即：Sheet1!$B$1:$D$1，这种包含Sheet1!的字符串可以作为Range( )的参数，无需进一步拆分。
第8行代码获取x轴区域的最后一个单元格，即图表数据区域的右上角单元格。
第10行代码获取图表最后一个系列的公式，其中objCht.SeriesCollection.Count为图表中系列的总数。
第12行代获取y轴区域的第一个单元格，即图表数据区域的左下角单元格。
第13行代码使用两个边角单元格获取图表数据区域。
第14行代码输出工作表名称。
第14行代码输出数据源地址。

输出结果如下：

图表数据源所在工作表: Sheet1
图表数据源地址：$B$1:$D$6