Run a macro based on change in a cell caused by incoming Real-Time server data

Question

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
 'code here

But that does not seem to work.

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!

How are the fields being updated? Is it from a formula reference? Or is it another add-in? You need to explain better what is actually happening. Also please give some context for the variable `Prev_Val` where it is defined and how is it assigned. Have you tried putting a breakpoint in your code to see if the change event is even firing? Is the code in a worksheet module or a standard module? **Revise your question (above) to answer each of these questions** (it is OK if you don't know the answer to some of them). Cheers. — David Zemens, Jun 04 '14 at 00:30
Fields are being updated via an RTD server: e.g a cell formula looks like: =RTD("qj.position.excel", , "FEED", $A2,F$1). Prev_Val is defined only as you see it here. That part works if I manually go and change the cell F3; it will remember the previous value and I3 = F3- Prev_Val will give the difference. This code is in a worksheet module. I'm not sure what a breakpoint is or how to use one (I am completely new to VBA and novice in Java). Sorry if that's not very helpful in clearing this up. Appreciate your help. Best - D. — user3705100, Jun 04 '14 at 01:55
OK, no that helps. So one problem is that typically a formula does not trigger any event procedures. Is this occurring at regular interval? If so, you could use something like `Application.OnTime` method, in a loop to call a procedure every X seconds, etc. — David Zemens, Jun 04 '14 at 03:30

score 1 · Accepted Answer · edited May 23 '17 at 11:57

This might work, instead of the Worksheet_Change event, use the Worksheet_Calculate event. This procedure runs every time the sheet calculates, so you will need to have Automatic calculation enabled (this is normal default setting).

This is basically your exact code, slightly tweaked:

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
       End If
End Sub

Now one limit I think is that after you end the session, save/close the file, etc., the value of Prev_Val will not persist. There are a number of ways you might work around this limitation using a hidden worksheet to store the value, or a Name variable in the worksheet/workbook, or CustomDocumentProperties (I think I recently wrote an answer here to another Q about how to use CustomDocumentProperties, and I'm certain I have a few about using Names for this sort of approach, too...).

But maybe the simplest would be to do something like this in the Worksheet_Activate event

Private Sub Worksheet_Activate()
    If Prev_Val = 0 Then Prev_Val = [F3]
End Sub

I haven't really tested that too thoroughly, though, and it may not be a perfect fit for you, so it might be better to use the CustomDocumentProperties, here is an example:

Alternatives to Public Variables in VBA

Several of the other possible methods are posted as answers in that Q, too.

Hope this helps!

This solution worked. My macro runs without manual updates on the sheet. Thanks! Now to work on the next steps! — user3705100, Jun 04 '14 at 20:04

Run a macro based on change in a cell caused by incoming Real-Time server data

1 Answers1