Programming Excel with VBA and .NET

Name

series.Formula [= setting]

Synopsis

Gets or sets the formula for a series. This formula uses the Series worksheet function. The Formula properties are the only way to get the source range from the chart. For example, the following code gets the source range from the active chart and then selects that range:

Sub TestGetSourceRange(  )
    Dim chrt As Chart, rng As Range
    ' Get a chart.
    Set chrt = ActiveChart
    Set rng = GetSourceRange(chrt)
    rng.Worksheet.Activate
    rng.Select
End Sub
 
Function GetSourceRange(chrt As Chart) As Range
    Dim sc As SeriesCollection, sr As Series, _
      result As Range, temp As String, i As Integer, _
      ar(  ) As String, j As Integer
    Set sc = chrt.SeriesCollection
    ' For each of the series.
    For i = 1 To sc.Count
        ' Get the formula.
        temp = sc(i).Formula
        ' Get the address part of the formula.
        temp = Replace(temp, "=SERIES(", "")
        ' Break into an array.
        ar = Split(temp, ",")
        ' Omit the last element, which is the index of the series.
        For j = 0 To UBound(ar) - 1
            ' If the data point is not omitted.
            If ar(j) <> "" Then
                ' Convert the address to a range.
                If result Is Nothing Then
                    Set result = Range(ar(j))
                Else
                    ' Append the range using Union.
                    Set result = Union(result, Range(ar(j)))
                End If
            End If
        Next
    Next
    ' Return the result.
    Set GetSourceRange = result