Recently a project required that I perform calculations on the point values of an Excel chart using VBA. Upon researching how to do so, I found that writing the code is not as easy as I originally thought. The task becomes difficult because the point object does not have a values property.

Fortunately I found a solution that was posted by Andy Pope in response to a question posted to the Ozgrid.com forum. Below is Andy's example:

x=activechart.SeriesCollection(1).values 
For i=LBound(x) To UBound(x) 
    Debug.Print "Point ";i;"=";x(i) 
Next i 

In the solution above, "x" is a single-dimension array that hold the point values of "SeriesCollection(1)". A loop is used to increment through the array and return the point values.

Using this solution as a start, I needed to expand it to cover all of the series in a chart. The example below is the result:

Option Explicit

Sub PointValues1()

   Dim arrValues() As Variant
   Dim Srs() As Variant
   Dim Cnt As Integer
   Dim Sr As Integer
   Dim Pt As Integer
   Dim i As Integer
     
   Cnt = 0

    For Sr = 1 To ActiveChart.SeriesCollection.Count
        Srs = ActiveChart.SeriesCollection(Sr).Values
        For Pt = 1 To ActiveChart.SeriesCollection(Sr).Points.Count
             Cnt = Cnt + 1
             ReDim Preserve arrValues(1 To Cnt)
             arrValues(Cnt) = Srs(Pt)
        Next Pt
    Next Sr

    For i = 1 To UBound(arrValues)
        Debug.Print arrValues(i)
    Next i

 End Sub

In PointValues1, the first loop is used to increment through each series on the chart. Like Andy's example, the statement

Srs = ActiveChart.SeriesCollection(Sr).Values

is used to extract the point values from the active series. Because the array needs to hold all of the point values of the chart, a simple counter is used to record the placement of each point value within the array where that value should fall.

I created the following chart in which to test the macro above:

The macro returns the following point values in the immediate window from the "arrValues" array:

Next, I modified the macro above to perform several calculations using the point values stored in the array. The modified code below returns a maximum, minimum, total, and average value for all of the data points in the chart:

Option Explicit

Sub PointValues2()

   Dim arrValues() As Variant
   Dim Srs() As Variant
   Dim Cnt As Integer
   Dim Sr As Integer
   Dim Pt As Integer
   Dim i As Integer
   Dim MaxVal As Double
   Dim MinVal As Double
   Dim TotVal As Double
   
   Cnt = 0

    For Sr = 1 To ActiveChart.SeriesCollection.Count
        Srs = ActiveChart.SeriesCollection(Sr).Values
        For Pt = 1 To ActiveChart.SeriesCollection(Sr).Points.Count
             Cnt = Cnt + 1
             ReDim Preserve arrValues(1 To Cnt)
             arrValues(Cnt) = Srs(Pt)
        Next Pt
    Next Sr

    MaxVal = arrValues(1)
    MinVal = arrValues(1)
    
    For i = 1 To UBound(arrValues)
        If arrValues(i) > MaxVal Then MaxVal = arrValues(i)
        If arrValues(i) < MinVal Then MinVal = arrValues(i)
        TotVal = arrValues(i) + TotVal
    Next i

 Debug.Print "Maximum Value = " & MaxVal
 Debug.Print "Minimum Value = " & MinVal
 Debug.Print "Range Total = " & TotVal
 Debug.Print "Range Average = " & TotVal / UBound(arrValues)

End Sub

The macro returns the following point values in the immediate window from the "arrValues" array:

Finally, I thought to experiment with creating a two-dimension array to store the point values and a one-dimension array to store the values from the X-Axis. In this example, a counter like those used in examples 1 and 2 is replaced with two loops. The outer loop is based on the number of series on the chart and the inner loop is based on the number of points within each series. The code below is the result:

Option Explicit

Sub PointValues3()

    Dim arrValues() As Variant
    Dim Srs() As Variant
    Dim xVal() As Variant
    Dim SrCnt As Variant
    Dim PtCnt As Variant
    Dim Sr As Integer
    Dim Pt As Integer
    Dim i As Integer
    Dim j As Integer
    
    SrCnt = ActiveChart.SeriesCollection.Count
    PtCnt = ActiveChart.SeriesCollection(1).Points.Count
        
    For Sr = 1 To ActiveChart.SeriesCollection.Count
        Srs = ActiveChart.SeriesCollection(Sr).Values
        xVal = ActiveChart.SeriesCollection(Sr).xValues
        For Pt = 1 To ActiveChart.SeriesCollection(Sr).Points.Count
            ReDim Preserve arrValues(1 To SrCnt, 1 To PtCnt)
            arrValues(Sr, Pt) = Srs(Pt)
        Next Pt
    Next Sr

    For i = 1 To SrCnt
        For j = 1 To PtCnt
            Debug.Print "Series " & i & " " & "Point " & xVal(j) & " Value: " & arrValues(i, j)
        Next j
    Next i

End Sub

The macro returns the following point values in the immediate window from the "arrValues" array: