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?
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.
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: <font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckForUpdates()<br>    lngDDEChannel = Application.DDEInitiate("App", "Topic")<br>    RequestedVal = Application.DDERequest(lngDDEChannel, "Item")<br>    Application.DDETerminate lngDDEChannel<br>    <br>    <SPAN style="color:#00007F">With</SPAN> ThisWorkbook.Worksheets(1)<br>        CellVal = .Range("A1").Value<br>        .Range("B1").Value = CellVal<br>        .Range("C1").Value = RequestedVal(1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT> 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.
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.
Not sure what you mean by this âdefaultâ. Could you elaborate, please? 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â: <font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Increment()<br>    t = 0<br>    Last = Timer<br>    <SPAN style="color:#00007F">Do</SPAN><br>        DoEvents<br>        <SPAN style="color:#00007F">If</SPAN> Timer - Last >= 1 <SPAN style="color:#00007F">Then</SPAN><br>            Last = Timer<br>            t = t + 1<br>            ThisWorkbook.Worksheets(1).Range("A1").Value = t<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> t = 10<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT> 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â: <font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> SetLinks()<br>    ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!MyData'", "CheckForUpdates"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT> 6) In âIn.xlsmâ create a sub âCheckForUpdatesâ: <font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckForUpdates()<br>    lngDDEChannel = Application.DDEInitiate("Excel", "C:\Users\Documents\Out.xlsm")<br>    RequestedVal = Application.DDERequest(lngDDEChannel, "MyData")<br>    Application.DDETerminate lngDDEChannel<br>    <br>    ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!MyData", ""<br>    DoEvents<br>    ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!MyData", "CheckForUpdates"<br>    <br>    <SPAN style="color:#00007F">With</SPAN> ThisWorkbook.Worksheets(1)<br>        CellVal = .Range("A1").Value<br>        .Range("B1").Value = CellVal<br>        .Range("C1").Value = RequestedVal(1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT> 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.
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.
Hi, crmorris. Good to have you join back in. 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
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#.
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.