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!
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!