Streaming Data: On Cell.Value change, do stuff

[marvelikov]

Hi,

So I have this sheet that gets streaming data from another app. I want to execute some code when the value of some cells change.

I tried Worksheet_Change method but it does not detect changes to the values(Im guessing it only detects manual input?) Stole this method from the forum, have no idea if ive done it correctly.

N5 and Y5 are the cells where the incoming stream goes

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim KeyCells As Range
    Set KeyCells = Range("N5", "Y5")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then Application.OnTime Now + TimeValue("00:00:6"), "Module1.new_entry"


End Sub

Worksheet_Calculate does the job but sometimes it triggers like 50 times for a single value change and I want it to trigger just once and cant put sleep methods because I'll have multiple sheets doing this and dont want to freeze excel

Sub upd_result()


Sheets("Data").Select
    Sheets("Data").Range("G1").Value = Sheets("Data").Range("N5").Value
    Sheets("Data").Range("H1").Value = Sheets("Data").Range("Y5").Value
    Application.OnTime Now + TimeValue("00:00:6"), "Module1.new_entry"


End Sub


Private Sub Worksheet_Calculate()

If Sheets("Data").Range("G2").Value = False Then Call Module1.upd_result


End Sub

Here I use G1 and H1 to record previous values of N5 and Y5, and if they arent the same as the incoming stream, G2 equals to False. Problem is it gets triggered about 50 times before G2 can resolve to True I guess. I tried squeezing in Range("G2").Calculate in the upd_result method to force it to resolve before triggering worksheet_calculate again but it didnt help either

Also open to entirely different ways of achieving things, Ive just picked up VBA yesterday and its all still a fog.

 

[marvelikov]

Sub upd_result()


Sheets("Data").Select
    Sheets("Data").Range("G1").Value = Sheets("Data").Range("N5").Value
    Sheets("Data").Range("H1").Value = Sheets("Data").Range("Y5").Value
    Application.OnTime Now + TimeValue("00:00:6"), "Module1.new_entry"


End Sub

I found that it only triggers multiple times when the value of Y5 changes, if N5 changes it works as intended - only triggering new_entry once,
so the queston now seems to be how to change them simultaneously without triggering a worksheet_calculate in between

 

[marvelikov]

Well, I've solved it. I'll post it in case anyone else might need it

Private Sub Worksheet_Calculate()


If Sheets("Data").Range("G2") = False Then
    Application.EnableEvents = False
    Call Module1.upd_result
    Application.EnableEvents = True
End If
End Sub

Disable events with this command

Application.EnableEvents = False

before calling the method that updates the previous values and enable them back again after method finishes.

PS When simple things like that cost me 2 days is when I know I wont be coming up with any billion dollar ideas