Local Maximum of Streaming Torque Data - VBA

[mik901]

Hi guys,
I have a machine that is spitting out torque data that I am transferring into excel. I decided to filter it so that I am not overwhelmed with data that I do not require (no torque / 0 values). The data is also converted into inch lbs from NM. New data is added to the top row of the active sheet.

What I am trying to do now is figure out a way to get the most current incoming peak value, display it in a Text box (until the next new peak value comes in) and also store it in a separate sheet.

Sample data:
I've pasted a few points from two cycles of raw data (about 50 data points for each cycle every 30 seconds). For the first cycle I need to extract the -1.035, then the -0.89. (Note: The data is -ve in the data set, but as soon as I apply my filter and conversion into inch lbs, the values will be +ve). The other tricky thing is that the torque cycle may have another relative max in it (eg. -0.645) but I don't want to record that value for that cycle since the final torque will actually be -1.035.

Any input would be greatly appreciated. Thanks!

[TABLE="width: 500"]
<tbody>[TR]
[TD]2/17/2016 3:18:17 PM[/TD]
[TD]-0.005[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:35 PM[/TD]
[TD]-0.89[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:35 PM[/TD]
[TD]-0.805[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:32 PM[/TD]
[TD]-0.51[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:30 PM[/TD]
[TD]-0.155[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:15 PM[/TD]
[TD]-0.17[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:15 PM[/TD]
[TD]-1.035[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:03 PM[/TD]
[TD]-0.445[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:01 PM[/TD]
[TD]-0.645[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:00 PM[/TD]
[TD]-0.275[/TD]
[/TR]
</tbody>[/TABLE]

Sub GetSWData()
Dim Chan As Long
Dim LowLimit, InchLbs, Torque As Double
Dim MyVariantArray As Variant
Dim MyString As String


LowLimit = (-0.05)
InchLbs = (-8.850746)
ColPtr = 1
RowPtr = RowPtr + 1             ' point to the next row down
Chan = DDEInitiate("WinWedge", MyPort)  ' initiate DDE link with device
MyVariantArray = DDERequest(Chan, "Field(1)")  'get Field(1) from device


If Val(MyVariantArray(1)) * InchLbs < LowLimit * InchLbs Then   ' filter values below 0.05 
DDETerminate Chan
Else
Torque = Val(MyVariantArray(1)) * InchLbs  ' convert to inch pounds


MyString = Torque ' convert to a string


Range("A1:B1").Insert Shift:=xlDown ' insert two cells at A1 and B1 shifting all data in columns A and B down one row
Sheets(SheetName).Cells(1, ColPtr).Formula = Now      ' write the current date and time to the cell A1 (row 1, column 1)
Sheets(SheetName).Cells(1, ColPtr + 1).Formula = MyString   ' write the data from device to cell B1  (row 1, column 2)


DDETerminate Chan ' terminate the dde link


End If


'FIND THE MOST RECENT LOCAL MAX 
'DISPLAY IN TXT BOX
'SAVE IN DIFF EXCEL SHEET


End Sub

 

[mik901]

After clicking around the forum I've found the following code that get's the local mins and maxes. However, I am running into a different issue once the data is processed.
I am trying to get the largest max out of each group of values, record it and display it in a text box. The text box value will be then updated with the next largest local max as the data is coming in.

Result from code below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date - Time[/TD]
[TD]Torque[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:35 PM[/TD]
[TD]0.89[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:31 PM[/TD]
[TD]0.45[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:28 PM[/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:26 PM[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:15 PM[/TD]
[TD]1.035[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:02 PM[/TD]
[TD]0.635[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:52:00 PM[/TD]
[TD]0.535[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:51:29 PM[/TD]
[TD]0.95[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:51:27 PM[/TD]
[TD]0.71[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:51:26 PM[/TD]
[TD]0.625[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:51:24 PM[/TD]
[TD]0.415[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:51:23 PM[/TD]
[TD]0.48[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:50:58 PM[/TD]
[TD]0.99[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:50:56 PM[/TD]
[TD]0.695[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:50:53 PM[/TD]
[TD]0.97[/TD]
[/TR]
[TR]
[TD]2/17/2016 2:50:53 PM[/TD]
[TD]0.97[/TD]
[/TR]
</tbody>[/TABLE]

Out of the above data set I am only interested in pulling out: 0.99, then 0.95, then 1.035, and lastly 0.89.

I was thinking of looking at the timestamps and doing something with the difference in time between torque values.

Any suggestions would be much appreciated.


CODE that runs to get the above table from my data:

Sub FindMaxAndMin()


    Dim i As Long
    Dim iMax As Long
    Dim iMin As Long
    Dim arr, TorqueVal, TorqueDate As Variant
    Dim skMin As Boolean
    Dim skMax As Boolean


   With ActiveSheet
  
        arr = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        iMax = 2
        iMin = 2
        Sheets("Torque").Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Clear
        For i = 2 To UBound(arr)
            If arr(i, 2) > arr(i - 1, 2) Then
                If skMin Then
                    '.Cells(iMax, "C") = arr(i - 1, 1)
                    '.Cells(iMax, "D") = arr(i - 1, 2)
                    iMax = iMax + 1
                End If
                skMin = False
                skMax = True
            End If
            If arr(i, 2) < arr(i - 1, 2) Then
                If skMax Then
                
                    Sheets("Torque").Cells(iMin, "A") = arr(i - 1, 1)
                   Sheets("Torque").Cells(iMin, "B") = arr(i - 1, 2)
                    'Sheets("Torque").Cells(iMin, "B") = arr(i - 1, 2)
                    iMin = iMin + 1
                End If
                skMin = True
                skMax = False
            End If
        Next


    End With
End Sub