DDE and Excel VBA

Alex123 · Jul 31, 2011

Just had another thought. Instead of using DDERequest every time SetLinkOnData triggers a macro, store links that were triggered in an array (have an array for all links and assign 1 if it was triggered) then wait until some time elapses after a last SetLinkOnData trigger (how long should one wait?) and then use DDERequest to pull in data for all those links that were triggered and reset the trigger array values for them to 0. So:

1) Populate worksheet cells with DDE links;
2) Assign the same macro âTriggerdLinksâ to all these links using SetLinkOnData method;
3) In TriggeredLinks macro, assign 1 to arrTriggeredLinks array element that corresponds to the link that triggered this macro;
4) In TriggeredLinks macro, set Windows API function SetTimer to trigger a macro âCheckForUpdatesâ in, say, 1ms, first cancelling previous settings for SetTimer. Thus if no other links set off TriggeredLinks macro via SetLinkOnData, CheckForUpdates macro will be called in 1ms after the last call to TriggeredLinks macro;
5) Once CheckForUpdates macro is called via SetTimer, use DDERequest to pull in data into a VBA array for all links that have corresponding element in the array arrTriggeredLinks set to 1 and then set these elements to 0.

Comments? Is 1ms to long to wait before calling DDERequest? Is there a way to find out how many DDE updates are stored in a queue, so that one doesnât have to use the Timer?

PocketChange · Jul 31, 2011

Try both methods and see which works better for your purposes.
sounds like two relatively simple subs that you can wrap in a timing loop logging the results. run them simultaneously in different instances and see which performs better over a few hours. I use system timers to periodically write the in memory db out to disk every minute.

Alex123 · Aug 1, 2011

OK. I have run a couple experiments and Iâm afraid I was unable to confirm your statements with regard to DDEInitiate or DDERequest calls forcing data in the cells to be updated even for the links that were requested for in DDERequest, let alone for other links. Here is the setup that I have used to test this

1) Insert a DDE link into a worksheet cell;
2) Attach âCheckForUpdatesâ macro to this link using SetLinkOnData Method;
3) CheckForUpdates:

Public Sub CheckForUpdates()     lngDDEChannel = Application.DDEInitiate("App", "Topic")     RequestedVal = Application.DDERequest(lngDDEChannel, "Item")     Application.DDETerminate lngDDEChannel          With ThisWorkbook.Worksheets(1)         CellVal = .Range("A1").Value         .Range("B1").Value = CellVal         .Range("C1").Value = RequestedVal(1)     End With End Sub

The data value that the DDE link was linked to incremented by 1 every second, and the value in cell B1, which was a copy of the value in the DDE-linked cell, always lagged by 1 from the value in cell C1, which was pulled with DDERequest. What was the setup that you used to come to a conclusion that DDERequest forced updates into cells?

I have also found that it takes approximately 100 ms between the triggering of the macro assigned with SetLinkOnData method and the actual change of the value in a worksheet cell.

PocketChange · Aug 1, 2011

100ms is excel default.

Post the rest of your vba code and the dde links in the worksheet cells.

i don't see where you are setting setlinkondata:

' Get all DDE Links and set trigger for just ask/bid/last price updates

Dim aLinks As Variant
Dim i As Long

aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
If UCase(Right(aLinks(i), 4)) = "?ASK" Or UCase(Right(aLinks(i), 4)) = "?BID" Or UCase(Right(aLinks(i), 4)) = "LAST" Then
ActiveWorkbook.SetLinkOnData aLinks(i), "get_ticks"
End If
Next i
End If
End Sub

I can help with the first variation using setlinkondata.

Quote from Alex123:

OK. I have run a couple experiments and Iâm afraid I was unable to confirm your statements with regard to DDEInitiate or DDERequest calls forcing data in the cells to be updated even for the links that were requested for in DDERequest, let alone for other links. Here is the setup that I have used to test this

1) Insert a DDE link into a worksheet cell;
2) Attach âCheckForUpdatesâ macro to this link using SetLinkOnData Method;
3) CheckForUpdates:

Public Sub CheckForUpdates()     lngDDEChannel = Application.DDEInitiate("App", "Topic")     RequestedVal = Application.DDERequest(lngDDEChannel, "Item")     Application.DDETerminate lngDDEChannel          With ThisWorkbook.Worksheets(1)         CellVal = .Range("A1").Value         .Range("B1").Value = CellVal         .Range("C1").Value = RequestedVal(1)     End With End Sub

The data value that the DDE link was linked to incremented by 1 every second, and the value in cell B1, which was a copy of the value in the DDE-linked cell, always lagged by 1 from the value in cell C1, which was pulled with DDERequest. What was the setup that you used to come to a conclusion that DDERequest forced updates into cells?

I have also found that it takes approximately 100 ms between the triggering of the macro assigned with SetLinkOnData method and the actual change of the value in a worksheet cell.

Alex123 · Aug 1, 2011

Quote from PocketChange:

100ms is excel default.

Not sure what you mean by this âdefaultâ. Could you elaborate, please?

Quote from PocketChange:

i don't see where you are setting setlinkondata

It's in a different sub. Here's my complete set up:

1) Create two Excel Wrokbooks (âOut.xlsmâ and âIn.xlsmâ) and run them in two separate instances of Excel;
2) In âOut.xlsmâ set cell A1 on Sheet1 to 0 and name this cell âMyDataâ;
3) Create a sub âIncrementâ in âOut.xlsmâ:

Sub Increment()     t = 0     Last = Timer     Do         DoEvents         If Timer - Last >= 1 Then             Last = Timer             t = t + 1             ThisWorkbook.Worksheets(1).Range("A1").Value = t         End If     Loop Until t = 10 End Sub 
4) Copy cell A1 from Sheet1 in âOut.xlsmâ into cell A1 on Sheet1 in âIn.xlsmâ as a link. This inserts a formula (in my case)
=Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!MyData'

5) In âIn.xlsmâ create a sub âSetLinksâ:

Public Sub SetLinks()     ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!MyData'", "CheckForUpdates" End Sub 
6) In âIn.xlsmâ create a sub âCheckForUpdatesâ:

Public Sub CheckForUpdates()     lngDDEChannel = Application.DDEInitiate("Excel", "C:\Users\Documents\Out.xlsm")     RequestedVal = Application.DDERequest(lngDDEChannel, "MyData")     Application.DDETerminate lngDDEChannel          ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!MyData", ""     DoEvents     ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!MyData", "CheckForUpdates"          With ThisWorkbook.Worksheets(1)         CellVal = .Range("A1").Value         .Range("B1").Value = CellVal         .Range("C1").Value = RequestedVal(1)     End With End Sub

7) Run âSetLinksâ in âIn.xlsmâ;
8) Run âIncrementâ in âOut.xlsmâ;
9) In âIn.xlsmâ watch as the value in cell B1 lags by 1 behind the value in cell C1.

crmorris · Aug 1, 2011

your test seems convoluted. why don't you set it up in a single workbook and compare your price results: once as pure dde (copy the links as vals) and another using the forced update vba version suggested by pocketchange?

i'm interested in the results and appreciate the thread renewal.

Alex123 · Aug 1, 2011

Hi, crmorris.

Good to have you join back in.

Quote from crmorris:

why don't you set it up in a single workbook

I need to have 2 macros running at the same time: one to increment values in Out.xlsm workbook to simulate, say, price changes on a server, and another to process these changes. The only way to do it is to have two instances of Excel running. At least that's the only way I could think of how to do it. I welcome any suggestions and contributions that you might have on this. It would be good to finally resolve this thing, and hopefully between us three we can crack it.

Regards

Steven.Davis · Aug 7, 2011

There are timer Add-ins that will give you an event where you can simulate prices updating. I found it easier to go the VSTO route and write the real/simulated order flow through a combination of VB.Net and C#.

PocketChange · Aug 12, 2011

If your interested in a sample workbook setup to work with OEC demo accounts send me a private message with your demo account ID.
It is locked down but you can gauge performance and get an idea of what kind of tick capture you can achieve to a sql db using excel.

Alex123 · Aug 15, 2011

Quote from PocketChange:

If your interested in a sample workbook setup to work with OEC demo accounts send me a private message with your demo account ID.
It is locked down but you can gauge performance and get an idea of what kind of tick capture you can achieve to a sql db using excel.

Thanks, PocketChange, but I'm with IB at the mo. I'm also finding that DDE is not really up to sratch (the data keeps returning empyt after a DDERequest call and DDESetLinkOnData doesn't seem to fire on every change of data?...) for what I'm looking to do, so am probably goona go with ActiveX or API instead.