Option Explicit
Public Const Intervalle = 10 'intervalle entre deux analyses (en seconde)
Const LaFeuille = "Feuil1"
Public ProchaineAnalyse
Dim arretHausse(1 To 10000) As Boolean
Dim arretBaisse(1 To 10000) As Boolean
Sub Scanner()
Dim derlig&, t, i&, s$
On Error Resume Next
Application.OnTime ProchaineAnalyse, "Scanner", , False
On Error Resume Next
derlig = Application.IfError(Application.Match(String(255, "z"), Sheets(LaFeuille).Columns(2), 1), 0)
If derlig <= 1 Then Exit Sub
t = Sheets(LaFeuille).Range("a1:o1").Resize(derlig)
For i = 2 To UBound(t)
If IsNumeric(t(i, 6)) Then
If t(i, 14) <> "" And t(i, 6) >= t(i, 14) And (Not arretHausse(i)) Then
s = t(i, 2) & vbLf & "a atteint ou a dépassé" & vbLf & _
"le seuil à la hausse = " & Format(t(i, 14), "#,##0.0000")
MsgBox s, vbExclamation + vbOKOnly
arretHausse(i) = True
Sheets(LaFeuille).Shapes("BoutonAlerte").Fill.ForeColor.RGB = RGB(255, 192, 0)
End If
If t(i, 15) <> "" And t(i, 6) <= t(i, 15) And (Not arretBaisse(i)) Then
s = t(i, 2) & vbLf & "a atteint ou est passée sous" & vbLf & _
"le seuil à la baisse = " & Format(t(i, 15), "#,##0.0000")
MsgBox s, vbExclamation + vbOKOnly
arretBaisse(i) = True
Sheets(LaFeuille).Shapes("BoutonAlerte").Fill.ForeColor.RGB = RGB(255, 192, 0)
End If
End If
Next i
ProchaineAnalyse = Now() + Intervalle / (24# * 60 * 60)
Application.OnTime ProchaineAnalyse, "Scanner", , True
End Sub
Sub ReInitInfo()
Dim i&
For i = 1 To UBound(arretHausse): arretHausse(i) = False: Next
For i = 1 To UBound(arretBaisse): arretBaisse(i) = False: Next
Sheets(LaFeuille).Shapes("BoutonAlerte").Fill.ForeColor.RGB = RGB(146, 208, 80)
End Sub