In a cell somewhere on the sheet have the formula:
=SUM(B2:$B$1001)
This is just so that any change of value in that range triggers the sheet's
_Calculate event.
Then in
that sheet's own code-module (get there by right-clicking on the sheet concerned's tab and choose
View code) have:
Dim StaticVals, StaticTimes
at the top.
Then in the same module:
Private Sub Worksheet_Calculate()
Dim ChangeMade As Boolean
If IsEmpty(StaticVals) Then
StaticVals = Range("B2:B1001").Value
StaticTimes = Range("A2:A1001").Value
Else
RecentVals = Range("B2:B1001").Value
TimeNow = Time
For i = 1 To UBound(StaticVals)
If RecentVals(i, 1) <> StaticVals(i, 1) Then
StaticVals(i, 1) = RecentVals(i, 1)
StaticTimes(i, 1) = TimeNow
ChangeMade = True
End If
Next i
If ChangeMade Then
Range("A2:A1001").Value = StaticTimes
End If
End If
End Sub
The times in Excel retrieved like this are, I think, only to the nearest 100th second, so there's little point in having 3 decimal places of seconds.
Note that a time on a row will only change when the price changes, not if the RTD updates with the same price.
The code updates the whole column of times if any price changes, this is faster than trying to update a handful of individual cells.
Regarding advice to use the
_Change event rather than
_Calculate given at another forum, you can try it, but in my experience, RTD does
not trigger that event.
Regarding cross-posting: it's fine to do it, only tell us where you have done it.