Triggering a VBA Sub with DDE/RTD

[nicoan]

Hello


There are a few lines of VBA code that needs to be written to record some data coming to our sheet.
Got to say that I have almost zero experience with VBA, so I´ll just post the problem below:

We have a data sheet like this:
17190



And there is some real time data that is coming there by the second. When there is an error, it shows some columns with data within the same row after the column G (marked).
We want to record the data in a table within the LOG sheet as soon as they appear, adding the timestamp like the screenshot below:

17191



So in short: the macro should check for changes in the column G (first column) from the DATA sheet every second, and if it finds something different (new),
record the whole row (text only) as soon as they appear in the table within the LOG sheet with the timestamp.

Here´s the sample spreadsheet:
mediafire com/file/fg66rc2kbaar0b5


I highly appreciate your help.

[SamT]

Try this:

Right click on the "Data" tab and select "View Code."

Paste this code in that Code Page

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 7 Then Exit Sub

Dim NextRecord As Range

NextRecord = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Offset(1)

With NextRecord
.Value = .Offset(-1) + 1
.Offset(, 1) = Date
.Offset(, 2) = Time
Range(Target, Target.End(xlToRight)).Copy .Offset(, 3)
End With
End Sub

[nicoan]

Thank you very much for your reply SamT.

I just did it. Must be missing something as nothing happens.
Sorry for being such a zero at vba.

Just added some formulas for easy verification: the cells to be populated with the data are copying anything you type in column A, like this:
17192

Here is the sample file again (it has your code too):
mediafire com/file/ateqi1rre94eytq

[SamT]

Sorry. I can't open that link.

If you "Go Advanced" you can click on the PaperClip Icon and upload the file here.

[nicoan]

Sorry, here is it:
17193

[SamT]

My bad. I left out one word. I'm glad I could test it, it also had a copy glitch.

Looking at the made up Data sheet you sent,I'm not sure what you are doing with it.

This sub will work when you make changes in Column G like you said

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 7 Then Exit Sub

Dim NextRecord As Range

Set NextRecord = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Offset(1)

With NextRecord
.Value = .Offset(-1) + 1
.Offset(, 1) = Date
.Offset(, 2) = Time
Range(Target.Offset(, 1), Target.End(xlToRight)).Copy
.Offset(, 3).PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Sub

This sub will work when you make changes in Column A and Column G has formulas that follow A, like the atttachment.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub

Dim NextRecord As Range

Set NextRecord = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Offset(1)


With NextRecord
.Value = .Offset(-1) + 1
.Offset(, 1) = Date
.Offset(, 2) = Time
ActiveSheet.Range(Target.Offset(, 7), Cells(Target.Row, Columns.Count).End(xlToLeft)).Copy
.Offset(, 3).PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Sub

But, you're probably doing something different than both what you said and what you attached and neither one will actually work as is.

And, no. I don't know what caused that artifact of Copy & Pasting the numbers from Column A into the rest of the table.

[nicoan]

Awesome.. Thank a mil
Using the second as there will be formulas there, exactly as the example (same formula).

I tested it a bit, adding, replacing and removing words.
It is recording perfectly, but it seems to be recording when you delete cells too:

17195

[SamT]

Deleting is a change. Add

If Target = "" Then Exit Sub


when you delete cellsUh. . . deleting Cells and Shifting things? OR deleting the contents? That line wont work if a new cell shifts in.

[nicoan]

Sorry. The cells have formulas to extract content from other columns.
And the content appears and disappears constantly by the second. I hope that makes sense.

I tried adding the line in different places. Either I´m adding it incorrectly, or it just doesn´t work.

[SamT]

Uh. . . deleting Cells and Shifting things? OR deleting the contents?

The cells have formulas to extract content from other columns.
???





Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

[nicoan]

You already know I´m a total newbie :)

It seems to be working perfectly now. At least in the sample.
I´ll test it tomorrow on the original sheet.

Thanks so much man. You helped a ton.

[nicoan]

I´m terribly sorry. Trying to simplify the sample to the max, I made terrible omissions.

The spreadsheet is using DDE to stream real time quotes of market data.
We never write/touch anything in any cell.

The needed log ([X] columns of data) come as a product from the DDE cells (explained below).

I made a new sample for you to see exactly how it works here:
17214


As you can see, it starts with the column B (DDE), that streams the live quotes by the second,
(It won´t stream on your end unless you have TOS open (and the market on), so I better post pic for you to see:

17215


Next you have "Pivot 1" and "Pivot 2" columns, that just have prices.

Then the "Action" column is created from the DDE column and the pivots. This is: if Last price breaks a pivot it appears "Symbol+Pivot".


Now you see that this "Action" column is the first column that shoots the log. Price crosses the pivot and the "Symbol+Pivot" appears.
It appears and disappears, all starting from the DDE "Last" column automatically, same as the Data columns.


We just want to log the actions ("Action" column) as it occur, plus all the values on the right from the data columns.


I hope it´s clear now.
Again, I´m very sorry for the confusion. I highly appreciate your help.

[SamT]

Program Timing is Critical. I tried to eliminate any issues I can foresee without knowing anything about your Workbook.

Anyway. . .

Place this code in the ThisWorkbook Code Page

Option Explicit

Private Sub Workbook_Open()
NextLogRow = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Row

End Sub



Place this code in the Data Sheet Code Page

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
LoggerCaller Target

End Sub


Function LoggerCaller(Target)
Dim Data As Variant

Target.Row.Calculate

If Target <= Target.Offset(, 1) And Target >= Target.Offset(, 2) Then Exit Function

NextLogRow = NextLogRow + 1
Data = Target.Offset(, 3).Resize(, DataColumnsCount).Value
UpdateLog Data

End Function



Place this code in a Standard Module.
Be sure to set the value of DataColumnsCount to the number of columns, including "Action," that you want logged.

Option Explicit

Public NextLogRow As Long
Public Const DataColumnsCount As Long = 5 'Edit to reflect number of Data Columns

Public Sub UpdateLog(Data As Variant)
Dim ThisRecord As Range

Set ThisRecord = Sheets("LOG").Range("A" & NextLogRow) '.Resize(, DataColumnsCount)

With ThisRecord
.Value = .Offset(-1) + 1
.Offset(, 1) = Date
.Offset(, 2) = Time
.Offset(, 3).Resize(, DataColumnsCount) = Data
End With

End Sub

Save and Close the Workbook. Only then open it and test it. The Workbook_ Open sub must run before testing.

[nicoan]

I´ve just tried it with the streaming cell and it doesn´t work.
I´m probably doing something wrong, so to be sure I made a screencast here:

screencast.com/t/xwwCPqjdFXR

You know what could be the error?


(BTW I read somewhere that for DDE, people use a function called Setlinkondata, but idk if that applies to this case)

[SamT]

I´ve just tried it with the streaming cell and it doesn´t work.
What is a "Streaming Cell?"

WE need to see all the code in the workbook

Please use the # Icon to insert Code Tags,and place each page of Code in its own CODE Tags, And tell us the name of the Code Page they came from.

[nicoan]

Streaming Cells, sorry I called that the "Last" (C) column cells (DDE, the ones streaming the RT quotes).
We can only see if it works when the "Last" column is being updated in real time and breaking the pivots.

I placed it as you mentioned:

ThisWorkbook:


Option Explicit

Private Sub Workbook_Open()
NextLogRow = Sheets("LOG").Cells(Rows.Count, "A").End(xlUp).Row

End Sub


DATA sheet:


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
LoggerCaller Target

End Sub


Function LoggerCaller(Target)
Dim Data As Variant

Target.Row.Calculate

If Target <= Target.Offset(, 1) And Target >= Target.Offset(, 2) Then Exit Function

NextLogRow = NextLogRow + 1
Data = Target.Offset(, 3).Resize(, DataColumnsCount).Value
UpdateLog Data

End Function


Modules/Module1:


Option Explicit

Public NextLogRow As Long
Public Const DataColumnsCount As Long = 6 'Edit to reflect number of Data Columns

Public Sub UpdateLog(Data As Variant)
Dim ThisRecord As Range

Set ThisRecord = Sheets("LOG").Range("A" & NextLogRow) '.Resize(, DataColumnsCount)

With ThisRecord
.Value = .Offset(-1) + 1
.Offset(, 1) = Date
.Offset(, 2) = Time
.Offset(, 3).Resize(, DataColumnsCount) = Data
End With

End Sub



Is something wrong?

Here´s the file with the new code:
17221

[nicoan]

Thinking that maybe a self updating Time cell could be used to verify if it works in your end (without the real time quotes).

[SamT]

Streaming Cells, sorry I called that the "Last" (C) column cells (DDE, the ones streaming the RT quotes).
We can only see if it works when the "Last" column is being updated in real time and breaking the pivots.

Oops! The code is set to Trigger on the B column. I am sorry, I missed that the A column is empty.

In the Data sheet code, Worksheet_Change Sub, change the Column number to 3

If Target.Column <> 2 Then Exit Sub
To

If Target.Column <> 3 Then Exit Sub

[nicoan]

Nope, I saw it and tested with a 3 too. Still nothing, must be something else.

Let me see if I can make another sample with "Time" (self updated, dynamic) cells instead of the real time quotes.
That way you could see if it works in your end.

[nicoan]

I wanted to use the NOW function with seconds and dummy pivots, so that when the seconds are above/below the pivots the data would appear.
Been struggling with it for hours with no luck.

Maybe you could think of a better way to test if it works on your end without having real time quotes.

Any ideas?

[SamT]

Any ideas?
Manually change a value in C

It might be possible to tweak the DDE code to Call the Sub after it 'Pushes' the new values to Excel.

[nicoan]

I tried it and is not recording. Have you tried it to see if it works?

[SamT]

This works on my computer


Option Explicit

Private Sub Workbook_Open()
NextLogRow = Sheets("LOG").Cells(Rows.Count, "A").End(xlUp).Row

End Sub



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
LoggerCaller Target

End Sub


Function LoggerCaller(Target As Range)
Dim Data As Variant

If Target <= Target.Offset(, 1) And Target >= Target.Offset(, 2) Then Exit Function

NextLogRow = NextLogRow + 1
Data = Target.Offset(, 3).Resize(, DataColumnsCount).Value
UpdateLog Data

End Function


Option Explicit

Public NextLogRow As Long
Public Const DataColumnsCount As Long = 6 'Edit to reflect number of Data Columns

Public Sub UpdateLog(Data As Variant)
Dim ThisRecord As Range

Set ThisRecord = Sheets("LOG").Range("A" & NextLogRow)

With ThisRecord
.Value = .Offset(-1) + 1
.Offset(, 1) = Date
.Offset(, 2) = Time
.Offset(, 3).Resize(, DataColumnsCount) = Data
End With

End Sub



Remember. You must Open the book after saving it for it to work.

OR. You can put the cursor inside the Workbook_Open sub and press F5 to run it.

The reason the NextLogRow number is set the way it is is because of Timing. You will be making many changes to the book in a very short time frame. Each change will be calling the Worksheet_Change code. this way each Change will get a new NextLogRow number and none will over write the other.

[nicoan]

Cool, thanks.

Now it´s recording if I manually type the number that cross the pivot.

---

Another thing I tried is replacing a "Last" cell with a formula that copies the value from another cell. Like:

In cell a C6, I placed the formula:
=B14

Then when I enter a number in B14, that value appears in cell C6 and a pivot is broken appearing the data in columns F to K (within the Data sheet).
But it doesn´t record if we do this.

---

I´ll try later with streaming quotes and report back.

[nicoan]

I tried with currency (EUR/USD) streaming quotes and the recording doesn´t work.

Something seems to be wrong.


BTW I just realized that this isn´t DDE, but RTD (TOS turned to RTD a while ago):
https://support.microsoft.com/en-us/kb/289150
(not sure if this changes anything tough.)

[SamT]

I am changing the thread title to see if we can get an RTD/DDE expert.

[nicoan]

Thanks. Or maybe I should start another fresh one.

[snb]

Your level of expertise doesn't match your ambitions.
In that case 'help' is equivalent to 'outsourcing'; I don't think that's the purpose of this forum.

[SamT]

No, don't start another thread. There is much background in this one.