Get Excel Chart datasource

Question

It is easy to set a datasource for an Excel Chart with Visual Studio, like

Chart.SetSourceData(Source:=SomeRange)

But how do I GET (retrieve) the datasource (Range) for an already exisiting Chart in an Excel file ?

Answer 1

Here's a function that will parse the ranges out of the series. If you have custom series formulas that don't use ranges, it will probably break.

Public Function GetSourceData(ByRef cht As Chart) As Range

    Dim srs As Series
    Dim vaArgs As Variant
    Dim i As Long
    Dim rReturn As Range

    For Each srs In cht.SeriesCollection
        vaArgs = Split(Split(srs.Formula, "SERIES(")(1), ",")
        For i = 0 To UBound(vaArgs) - 1
            If rReturn Is Nothing Then
                Set rReturn = Range(vaArgs(i))
            Else
                Set rReturn = Union(rReturn, Range(vaArgs(i)))
            End If
        Next i
    Next srs

    Set GetSourceData = rReturn

End Function

Use as:

?getsourcedata(activechart).Address
$B$2:$C$2,$A$3:$C$14