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:
DataSheet - Extraction page.JPG



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:

LOG Sheet.JPG



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:
test1.JPG

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:
Recording Log Macro Question v2.xlsm

[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:

error.JPG

[SamT]

Deleting is a change. Add

If Target = "" Then Exit Sub

when you delete cells

Uh. . . 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:
DDE Derivatives Recording.xlsm


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:

dde data page sample.JPG


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:
DDE Derivatives Recording v2.xlsm

[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?