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.

BIDSIZE(B)

BID(C)

ASK(D)

ASKSIZE(E)

CumulativeVOLUME(F)

LASTPrice(G)

(H)

LASTVOL(I)

Price*QTY(J)

Σ(Price*QTY)(K)

ΣVOL(L)

VarX(M)

500

400

46.74

46.75

25100

46.74

200

=I3 * G3

uses a macro

uses a macro

=K3/L3

<tbody>
</tbody>

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