Results 1 to 6 of 6

20081127, 02:29 #1
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
Chart Series Min/Max Values (2002/SP3)
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

20081127, 02:35 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Chart Series Min/Max Values (2002/SP3)
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.

20081127, 02:47 #3
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Chart Series Min/Max Values (2002/SP3)
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 Xaxis to 160 to 200 rather than the autoscale values of 0 to 200, the min/max values for this series in this Xaxis range will change. How can I find out the min/max values in this range?

20081127, 03:11 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Chart Series Min/Max Values (2002/SP3)
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.

20081127, 03:56 #5
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Chart Series Min/Max Values (2002/SP3)
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"?

20081127, 04:02 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Chart Series Min/Max Values (2002/SP3)
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.