cellMatrix.net

Get a Maximum and Minimum Value from Certain Charts

A utility that I use at work automatically sets the Y Axis of an embedded chart to a calculated minimum and maximum value. To do so, the utility must extract the maximum and minimum point values from that chart. As I'm in the process of updating this utility to work with the Excel 2007 ribbon, I thought it would be good to document the VBA procedure that I used to extract the minimum and maximum values:

Sub Max_Min_Chart_Point_Values()

    Dim Cht As Chart
    Dim Srs As Series

    Set Cht = ActiveChart
    Set Srs = Cht.SeriesCollection(1)

    MaxVal = Srs.Values(1)
    MinVal = Srs.Values(1)

    For m = 1 To ActiveChart.SeriesCollection.Count
        A = ActiveChart.SeriesCollection(m).Values
        For l = 1 To ActiveChart.SeriesCollection(m).Points.Count
            If A(l) > MaxVal Then MaxVal = A(l)
            If A(l) < MinVal Then MinVal = A(l)
        Next l
    Next m
    
    Debug.Print MaxVal
    Debug.Print MinVal

End Sub

Please note that I've not tested this procedure with every chart type. I do know that it works with line and column charts, which happen to be most of the charts that I work with.