Hi All
I am new to this forum. Thanks for having me as your member
I am testing some product and getting large amount of data from the machine.
I want to Automate some of the boring data analysis, peak detection, trough detection, if possible the point of change ( this is very difficult because of the data is being noisy and not regular.)
I have adopted this vba code, but this is is very slow. Its taking half an hour to run. Could an experienced user suggest a better way to detect peak and output the x and y value?
Sub GetMax()
Dim chr As ChartObject
Dim chrSeries As Series
Dim lngrow, sum As Long
Dim Max As Long
Application.ScreenUpdating = False
Max = 0
sum = 0
On Error Resume Next
Set chr = ActiveSheet.ChartObjects(1)
Set chrSeries = chr.Chart.SeriesCollection(1)
On Error GoTo 0
If chrSeries Is Nothing Then Exit Sub
For lngrow = 101 To UBound(chrSeries.Values) - 100
For i = 1 To 100
sum = sum + chrSeries.Values(lngrow + i) + chrSeries.Values(lngrow - i)
If chrSeries.Values(lngrow) > chrSeries.Values(lngrow - i) Then
If chrSeries.Values(lngrow) > chrSeries.Values(lngrow + i) Then
temp = chrSeries.Values(lngrow)
Max = WorksheetFunction.Max(temp, Max)
End If
End If
Next i
NewMax = Max - (sum + chrSeries.Values(lngrow)) / 100
If NewMax > 0.5 Then
chrSeries.Points(lngrow).ApplyDataLabels
With chrSeries.Points(lngrow).DataLabel
.Position = xlLabelPositionCenter
.Border.Color = 1
End With
End If
Next
Application.ScreenUpdating = True
End Sub
Thanks in advance for your help
Bookmarks