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
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.