Insert time in another column when a column containing RTD updates values

[dsenff]

Hello,
I have a spreadsheet that has RTD (real time data) and I would like to create a macro that
when the values of column “B” with 1000 cells with RTD update their values, automatically record in the corresponding cells next to these values (column “A”) the hour, minutes, seconds and milliseconds.
And if any cell in column “B” is blank, leave the time cell blank.
I managed to make a macro, but it only works for a specific cell, I need the macro to work for a column with 1000 cells.

Private Sub Worksheet_Calculate()
If Range("B2").Value = "" Then
Range("A2").Value = ""
Else
Static oldval
If Range("B2").Value <> oldval Then
oldval = Range("B2").Value
Range("A2").Value = Application.WorksheetFunction.Text([now()] - tStart, "h:mm:ss.000")
End If
End If
End Sub

RTD2.jpg

[Logit]

Please post your workbook ... do not include any confidential data.

[dsenff]

Please post your workbook ... do not include any confidential data.

[rlv]

fyi, cross-posted in at least 3 other forums.

https://www.mrexcel.com/board/thread...alues.1185619/
https://stackoverflow.com/questions/...-rtd-updates-v
https://www.excelforum.com/excel-pro...es-values.html

[Paul_Hossler]

@dsenff

Please read the FAQ, especially the part about cross-posting in other forums

http://www.vbaexpress.com/forum/faq...._new_faq_item3

[p45cal]

I have a possible solution for you.
Its success depends on the likely frequency of incoming data, can you give us an idea?
How is the data getting from column D to column B?

Finally, I'm not posting a sausage until, one way or another, you acknowledge this cross-posting business.

[dsenff]

I have a possible solution for you.
Its success depends on the likely frequency of incoming data, can you give us an idea?
How is the data getting from column D to column B?

Finally, I'm not posting a sausage until, one way or another, you acknowledge this cross-posting business.

Hi, very sorry for the cross-posting, won't happen again.

The RTD code refers to the number of stock trading, the frequency is high.

In fact, this spreadsheet below is the correct one. The RTD data in column D is actually in to column B.

Thanks in advance

RunChangeRTD.xlsm

[dsenff]

Sorry for the cross-posting, won't happen again.

[p45cal]

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.

[dsenff]

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.

Thank you very much for the quick reply, i tested it today and it worked perfectly!!!

[Paul_Hossler]

Sorry for the cross-posting, won't happen again.

We only ask that you let us know that you've posted the question in one or more of the other forums by including the appropriate links

The other forums have a similar rule

That way if we see that you have an answer we won't spent our time and effort unnecessarily

From the FAQ

http://www.vbaexpress.com/forum/faq...._new_faq_item3

What is multiposting?

Multiposting is the act of posting a single question to different forums around the same time.

We discourage multiposting because it is like calling five cab companies and going with the one that comes first -- it shows disrespect for the volunteers that monitor this forum because they will unknowingly be helping someone who may have already received help elsewhere. Their time is no less important than that of the person asking the question.

Many of the volunteers here visit multiple forums and can easily spot duplicate posts. Some of them may even reply to your posts with a link to your post on another forum. Don't be the person that gets caught.

If you must post your question on a different forum, include a link to the question you have already posted on the previous forum(s). That way, those helping you can decide for themselves if you are already receiving the help you need somewhere else.



If you are still confused, read A message to forum cross posters.

https://www.excelguru.ca/content.php?184