Chart Series Min/Max Values (2002/SP3)

Topic

New Reply

I’m trying to get the minimum and maximum values for the displayed chart series in a particular range of X and Y axes (see attached figure). I’ve found a code that does that for the whole series range (see below), but I’m only interested in getting the min/max series values for any particular range, that is, for a subset of the whole series range. Can anyone help me modify the code below to achieve this task?

Sub GetSeriesMinAndMaxValues()
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer
Dim X As Series, i As Integer
Dim Xmin As Double, Xmax As Double, Ymin As Double, Ymax As Double
TotCtr = 0
With ActiveSheet.ChartObjects(“TemperatureChart”).Chart
‘ Loops through all of the Series and retrieves the values
‘ and places them into an array named ValuesArray.
For i = 1 To 2
For Each X In .SeriesCollection
If i = 1 Then SeriesValues = X.Values
If i = 2 Then SeriesValues = X.XValues
ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
For Ctr = 1 To UBound(SeriesValues)
ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
Next Ctr
TotCtr = TotCtr + UBound(SeriesValues)
Next
If i = 1 Then
Ymin = Application.Min(ValuesArray)
Ymax = Application.Max(ValuesArray)
Else
Xmin = Application.Min(ValuesArray)
Xmax = Application.Max(ValuesArray)
End If
TotCtr = 0
Next i
End With
Range(“F7”) = Xmin
Range(“F8”) = Xmax
Range(“G7”) = Ymin
Range(“G8”) = Ymax
End Sub

Reply | Quote

Replies

Change the line

For Each X In .SeriesCollection

to

Set X = .SeriesCollection(3)

where 3 is the index of the series you’re interested in, and delete the line

Next X

You can also use

Set X = .SeriesCollection(“Calcium”)

if you know the name of the series.

Reply | Quote

Thanks for the reply Hans, but that’s not exactly what I was looking for. I have a code that already controls which series to plot, so for example I can plot series 3 of 12 in the ChartObjects(“TemperatureChart”). I can then use the “GetSeriesMinAndMaxValues” subroutine that I posted before in order to get the min/max values for this series. However, let’s say that I change the scale of the chart on the X-axis to 160 to 200 rather than the autoscale values of 0 to 200, the min/max values for this series in this X-axis range will change. How can I find out the min/max values in this range?

Reply | Quote

Try this:

Sub GetSeriesMinAndMaxValues()
Dim XValuesArray(), SeriesXValues As Variant
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer
Dim X As Series
Dim Xmin As Double, Xmax As Double, Ymin As Double, Ymax As Double
Dim XAxisMin As Double, XAxisMax As Double
TotCtr = 0
With ActiveSheet.ChartObjects(“TemperatureChart”).Chart
‘ Loops through all of the Series and retrieves the values
‘ and places them into an array named ValuesArray.
XAxisMin = .Axes(xlCategory).MinimumScale
XAxisMax = .Axes(xlCategory).MaximumScale
For Each X In .SeriesCollection
SeriesValues = X.Values
SeriesXValues = X.XValues
For Ctr = 1 To UBound(SeriesXValues)
If SeriesXValues(Ctr) >= XAxisMin And SeriesXValues(Ctr) <= XAxisMax Then
TotCtr = TotCtr + 1
ReDim Preserve ValuesArray(1 To TotCtr)
ReDim Preserve XValuesArray(1 To TotCtr)
ValuesArray(TotCtr) = SeriesValues(Ctr)
XValuesArray(TotCtr) = SeriesXValues(Ctr)
End If
Next Ctr
Next X
End With
Ymin = Application.Min(ValuesArray)
Ymax = Application.Max(ValuesArray)
Xmin = Application.Min(XValuesArray)
Xmax = Application.Max(XValuesArray)
Range("F7") = Xmin
Range("F8") = Xmax
Range("G7") = Ymin
Range("G8") = Ymax
End Sub

It will fail if the minimum or maximum of the x axis is set to automatic.

Reply | Quote

Works great. Thanks Hans.

Qestion: What do you mean by “It will fail if the minimum or maximum of the x axis is set to automatic”?

Reply | Quote

Hmm, when I first tried this code I got an error that MinimumScale couldn’t be retrieved when it was set to automatic, but now that I try it again it works OK.
So I probably made a mistake somewhere. Please ignore that remark.

Reply | Quote