Macro wont run when a Cell changes due to incoming Real-Time Data! How to bypass need for manual intervention??

[DrLight]

I want to run the bellow macro whenever cell F3 increases. I need this to happen without manual intervention because F3 is increasing due to incoming RTD server data. As it stands, unless I manually update something in my sheet the macro does not run.

Public Prev_Val As Long


Private Sub Worksheet_Change(ByVal Target As Range)
'using a Public variable to store the previous value
    If Range("F3") <> Prev_Val Then
        Application.EnableEvents = False
        Range("I3") = Range("F3") - Prev_Val
        Prev_Val = Range("F3")
        Application.EnableEvents = True
    End If
End Sub

I've tried using:

If Target.Address = "$F$3" Then
'my code

But that does not seem to work either.
Context: I'm using RTD with a stock simulator to automatically populate fields in Excel. Several calculations are done on the incoming data, but I cant do any of them without having Cell I3 work properly!

 

[Michael M]

If, whenever new data comes in, the worksheet is recalculated, try changing the code to a Workbook_SheetCalculate event

 

[shadow12345]

If you want to retain the data after the procedure runs I always use the Static rather than the public command ... like this

Private Sub Worksheet_Change(ByVal Target As Range)
'using a Public variable to store the previous value

Static Prev_Val As Long
    If Range("F3") <> Prev_Val Then
        Application.EnableEvents = False
        Range("I3") = Range("F3") - Prev_Val
        Prev_Val = Range("F3")
        Application.EnableEvents = True
    End If
End Sub

 

[DrLight]

->I tried your solution, Shadow, but it still only updates I3 when I change something in the sheet manually (which is strange because F3 is changing almost every 2 seconds automatically).

-> Michael could you put that option in context with my code so I can copy past (I'm still [very] novice - trying to avoid errors)

Best,
D

 

[Michael M]

Just reread your post and realised there is no "calculations"
Is that all the code ??
Does the imported data trigger any recalcs in the sheet ?

 

[DrLight]

No there's more. The sheet looks like this. Note: Bidsize through Last Price are populated via RTD. The rest are for my formulas.

The cell under Cumulative VOLUME is F3. Cell under Last Vol is I3.


[TABLE="class: cms_table_grid, width: 1144"]
<tbody>[TR]
[TD]BIDSIZE(B)[/TD]
[TD]BID(C)[/TD]
[TD]ASK(D)[/TD]
[TD]ASKSIZE(E)[/TD]
[TD]CumulativeVOLUME(F)[/TD]
[TD]LASTPrice(G)[/TD]
[TD](H)[/TD]
[TD]LASTVOL(I)[/TD]
[TD]Price*QTY(J)[/TD]
[TD]Σ(Price*QTY)(K)[/TD]
[TD]ΣVOL(L)[/TD]
[TD]VarX(M)[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]46.74[/TD]
[TD="align: right"]46.75[/TD]
[TD="align: right"]25100[/TD]
[TD="align: right"]46.74[/TD]
[TD="align: right"][/TD]
[TD]200[/TD]
[TD]=I3 * G3[/TD]
[TD="align: right"]uses a macro[/TD]
[TD="align: right"]uses a macro[/TD]
[TD]=K3/L3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The formula in F3 looks like this: =RTD("qj.position.excel", , "FEED", $A3,F$1).

Once I can figure out I3 I have to code macros for K3 and L3, which are running cumulative sums of J3 and I3, respectively.

I planned on using something like the following (although these have not been functioning consistently -not sure whats wrong).

[COLOR=#333333][FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 With Target 
   If .Address(False, False) = "K3" Then 
      If IsNumeric(.Value) Then 
      Application.EnableEvents = False
      Range("K3").Value = Range("K3).Value + Range("J3").Value 
      Application.EnableEvents = True 
      End If 
   End If
End With
End Sub
[/FONT][/COLOR]

Your help (or anyone else's reading) is uber welcome.

-DLight.

 

[DrLight]

Problem solved:

Public Prev_Val As Long


Private Sub Worksheet_Calculate()
Dim rngF3 As Range
Set rngF3 = Range("F3")
      If rngF3 <> Prev_Val Then
         Application.EnableEvents = False
         Range("I3") = rngF3 - Prev_Val
         Prev_Val = rngF3
         Application.EnableEvents = True
           
        If IsNumeric(Range("J3").Value) Then
            Application.EnableEvents = False
            Range("K3").Value = Range("K3").Value + Range("J3").Value
            Application.EnableEvents = True
        End If


        If IsNumeric(Range("I3").Value) Then
            Application.EnableEvents = False
            Range("L3").Value = Range("L3").Value + Range("I3").Value
            Application.EnableEvents = True
        End If
    End If
End Sub