Programming Excel with VBA and .NET

Name

series.Values [= setting]

Synopsis

Sets or returns the array of values plotted by the series. For example, this helper function builds an array containing all the values from a chart:

' Useful function for getting a chart's source data
' in the form of an array.
Function GetChartData(chrt As Chart) As Variant
    Dim sc As SeriesCollection, i As Integer
    ' Get the series collection.
    Set sc = chrt.SeriesCollection
    ' Size an array to fit all the series.
    ReDim result(1 To sc.Count) As Variant
    ' For each of the series.
    For i = 1 To sc.Count
        ' Add the array of points to the result.
        result(i) = sc(i).Values
    Next
    ' Return the result array.
    GetChartData = result
End Function

You can use GetChartData to get values from a chart for use with Excel’s WorksheetFunction methods as shown here:

Sub TestGetChartData(  )
    Dim chrt As Chart, sc As SeriesCollection
    Set chrt = Charts("Stock Price History")
    Debug.Print WorksheetFunction.Min(GetChartData(chrt)), _
      WorksheetFunction.Max(GetChartData(chrt))
End Sub