Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    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
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    2 Star Lounger
    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 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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  5. #5
    2 Star Lounger
    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"?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •