Automatic high/low data callout for nearest point to click on chart

[Prevost]

Hi There. This may be too much of wishful thinking. I have a chart that plots the supply and return temperature from a heat exchanger. The pattern resembles a sine wave, however there are many spikes/valleys within each sine wave cycle. I am trying to get the numbers from the lowest point to the next highest point (I am doing this by visually reading the column of data and confirming my selection with the actual chart).

Does anyone have any ideas as to how I could speed this process up? Either through VBA or some special function/add ins?

Thanks!

 

[Worf]

Hi
The instructions to use the below code are:
· Run the connect chart routine
· Click chart elements and their names will appear at column S. Data points will have the format S[#series]P[#point]
· When the last two rows at column S show the starting and end points you want, run the chosen range routine to highlight it.

Dim ClassMod As New Class1
' at a standard module


Sub ConnectChart()
    Set ClassMod.Cht = ActiveSheet.ChartObjects(2).Chart
    ActiveSheet.ChartObjects(2).Activate
End Sub


Sub DisconnectChart()
    Set ClassMod = Nothing
    Range("A1").Activate
End Sub


Sub ChosenRange()
Dim r As Range, p(1 To 2)
Set r = Range("p15").CurrentRegion          ' upper left corner of chart source data
lr = Range("s" & Rows.Count).End(xlUp).Row
p(1) = Split(Cells(lr - 1, 19), "P")(1)
p(2) = Split(Cells(lr, 19), "P")(1)
Range(Cells(r.Rows(CInt(p(1))).Row, 16), Cells(r.Rows(CInt(p(2))).Row, 17)).Select
End Sub

Public WithEvents Cht As Chart
' Class1 is the module name, as it appears on the left pane
' this code goes at Class1, which is a class module


Private Sub Cht_MouseDown(ByVal Button&, ByVal Shift&, ByVal x&, ByVal y&)


On Error Resume Next
Cells(Range("s" & Rows.Count).End(xlUp).Row + 1, 19) = Selection.Name
On Error GoTo 0


End Sub

 

[Worf]

It looks like this:

 

[Prevost]

Thanks for the code Worf. It reads off the series points, which is great except I have many points in a series so to locate those I would be going down to each row. Is there a way for the cell to display the y-value or both the series value in the form (x-value, y-value)?

Cheers

 

[Worf]

Hi
I'm not understanding exactly what you want. When placing the cursor over a point, we get a screen tip with series/x/y information, but I guess you want more than that.
Please explain.

 

[Prevost]

Hi Worf,
Initially what I had wanted was to click an area on the chart (click the actual series data) and then have the highest/lowest value in a certain range be displayed. So, if the x-axis is time with 1 minute increments, if I clicked on the series data points then it would display the highest/lowest value for all the series data points for the preceeding and proceeding 60 minutes in x-axis. Does that make sense at all?
Thanks!

 

[Worf]

Hi
- Run the connect routine
- Click the chart until you have the desired point listed on column S
- Run the high-low routine to get minimum and maximum values for the 2-hour range

Dim ClassMod As New Class1
' at a standard module


Sub ConnectChart()
    Set ClassMod.Cht = ActiveSheet.ChartObjects("Chart 2").Chart
    ActiveSheet.ChartObjects(2).Activate
End Sub


Sub DisconnectChart()
    Set ClassMod = Nothing
    Range("A1").Activate
End Sub


Sub High_Low()
Dim p, r As Range, start_x, end_x%, s_ads(1 To 2) As Range, upleft As Range, lr%, ext(1 To 2)
' assumes 1-minute interval on x axis
Set upleft = ActiveSheet.Range("b15")    ' upper left corner of chart source data
Set r = upleft.CurrentRegion
ext(2) = WorksheetFunction.Max(r.Columns(1))
ext(1) = WorksheetFunction.Min(r.Columns(1))
lr = Range("s" & Rows.Count).End(xlUp).Row
p = Split(Cells(lr, 19), "P")(1)
start_x = Cells(CInt(p) + r.Rows(1).Row - 1, 2).Value - 60
If start_x < ext(1) Then start_x = ext(1)
end_x = start_x + 120
If end_x > ext(2) Then end_x = ext(2)
Set s_ads(1) = r.Columns(1).Find(start_x, upleft, xlValues)
Set s_ads(2) = r.Columns(1).Find(end_x, upleft, xlValues)
MsgBox "Max: " & WorksheetFunction.Max(Range(s_ads(1), s_ads(2)).Offset(, 1)) & vbLf & _
"Min: " & WorksheetFunction.Min(Range(s_ads(1), s_ads(2)).Offset(, 1))
End Sub

Public WithEvents Cht As Chart
' Class1 is the module name, as it appears on the left pane
' this code goes at Class1, which is a class module


Private Sub Cht_MouseDown(ByVal Button&, ByVal Shift&, ByVal x&, ByVal y&)


On Error Resume Next
Cells(Range("s" & Rows.Count).End(xlUp).Row + 1, 19) = Selection.Name
On Error GoTo 0


End Sub