DDE-Link - Capture value in VBA

D

depple

Board Regular
Joined
Sep 18, 2009
Messages
64
I have a macro that watches for changes in a DDE-link through the use of the SetLinkOnData method.

The actions in the subsequent macro is conditional on the new value in the same DDE-link. At the moment, I have a cell ("A1") in a sheet that displays the current value of the link and the actions of subsequent macro is dependent on the value in this cell.

But, it seems to me that the SetLinkOnData method is so quick in capturing a change in the DDE-link, that the subsequent macro uses the previous value in cell "A1", and not the value that the DDE-link is changing to.

Is there a way of retrieving the new value of the DDE-link in a macro, so that the subsequent macro don't have to look in a cell for the new value, in which the update of the new DDE-value seems relatively slow.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
I cannot explain the behavior you're running into. This is the first time I have heard of this. In any case, as a workaround, you can draw out an activex textbox on your worksheet and use it's change event. Set the LinkedCell property to A1 and when the value in A1 changes or is updated, the change event will fire.
 
Upvote 0
Hi Tom,

Thanks for the answer.

I believe smartsource describes my suspicion in the following thread. See

http://www.mrexcel.com/forum/showthread.php?t=426969

=> smartsource post on Jan 9th, 2010, 09:11 PM:

"Using setlinkondata can trigger an action on each update faster at 10ms but the update actions are triggered before the new data makes the grid."

I believe that this description is what I tried to convey, just in case I made myself unclear. In contrast to smartsource, I really just need 1 DDE-link. Though my knowledge in VBA is limited to a certain degree, I find it a little strange that there isn't any method of assigning the current value of a DDE-link to a variable in VBA, without doing through a cell value in a sheet.

Before adopting the SetLinkOnData method into my code, I used a sheetcalculate event. Between sheetcalculate and your suggestion, do you know which solution is the quickest?

Thanks for any help! :)
 
Upvote 0
Found when setlinksondata calls a procedure updating any item from the DDE server|topic Excel immediately refreshes all items from server|topic in the grid. The below code solved my data integrity issue and my test file was able to process DDE updates approx every 10ms.

Try modifying the macro that you call using setlinkondata to force refresh of all DDE links by calling any valid item.

Code:

Sub Force_DDE_Refresh()
Dim ddechan, Monitor
ddechan = Application.DDEInitiate(app:="Server", topic:="topic")
Monitor = Application.DDERequest(ddechan, "any_valid_item")
Application.DDETerminate ddechan
End Sub
</pre>


depple said:
I have a macro that watches for changes in a DDE-link through the use of the SetLinkOnData method.

The actions in the subsequent macro is conditional on the new value in the same DDE-link. At the moment, I have a cell ("A1") in a sheet that displays the current value of the link and the actions of subsequent macro is dependent on the value in this cell.

But, it seems to me that the SetLinkOnData method is so quick in capturing a change in the DDE-link, that the subsequent macro uses the previous value in cell "A1", and not the value that the DDE-link is changing to.

Is there a way of retrieving the new value of the DDE-link in a macro, so that the subsequent macro don't have to look in a cell for the new value, in which the update of the new DDE-value seems relatively slow.
Click to expand...
 
Upvote 0
Hi guys. I realize I’m a bit late to the party, but hopefully you will pick this up. J I looking for the best way to process some DDE-linked data. Let me describe what I’m trying to do.
I have at the moment ,let’s say, 200 DDE-linked cells in my worksheet. On a different sheet I have formulas in, say, 500 cells which compare the values in these DDE-linked cells and which report either TRUE or FALSE. Then I use a Worksheet_Calculate event to monitor for changes in results of these formulas.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Now, the problem with _Calculate event is that it fires whenever a calculation takes place, regardless of whether the results of the calculation have actually changes and I’m only interested in situations where the cells change in value (in particular when they turn to TRUE). OK, I have a cell that counts the number of TRUE values amongst these cells and when _Calculate fires I check if it’s > 0 and then either proceed or exit the sub. But still, _Calculate event fires every time DDE links change, which is every 100 milliseconds at the moment.
<o:p></o:p>
Tom’s suggestion of using ActiveX linked textbox would get around the firing on every calculation, but then I would need to set up 500 ActiveX boxed each linked to the TRUE/FALSE cells to monitor for the changes.
<o:p></o:p>
Finally, after this long-winded preamble, my question is – what is the better (stability, performance, speed, memory etc) way to go around this – should I put up with _Calculate firing all the time or should I populate my workbook with ActiveX textboxes? Or is the a third yet better way of doing this?
<o:p></o:p>
Many thanks<o:p></o:p>
 
Upvote 0
Kolobok said:
Hi guys. I realize I’m a bit late to the party...
Click to expand...
Never late to join the party of DDE technology which lasts centuries :)
DDE is too old technology with some disadvantages.
Find more details and alternative here - Real-Time Data: Frequently Asked Questions

...what is the better (stability, performance, speed, memory etc) way to go around this – should I put up with _Calculate firing all the time or should I populate my workbook with ActiveX textboxes? Or is the a third yet better way of doing this?
Click to expand...
Possible third way with DDE: instead of using 2 sheets and 500 textboxes for VBA code triggering use 1 sheet with 200 cells, in which DDE formulas are linked directly to 200 VBA subroutines by SetLinkOnData method.
It’s faster and saves the memory.
And provide True/False logic in VBA (not in the cells) to speed up common VBA processing .

Below is possible template of the code:
Rich (BB code): 

' ZVI:2011-05-15 http://www.mrexcel.com/forum/showthread.php?t=472286
Dim ArrDDE(1 To 200) As Range     ' <-- Array is referenced to the cells with DDE formulas
Dim ArrFlags(1 To 500) As Boolean ' <-- This array can be used for True/False logic instead of Sheet(2)

' Run Init code only once after DDE connection is established
Sub Init()
  
  Dim i As Long
  
  ' Set each element of ArrDDE() to the relevant DDE cell.
  With ThisWorkbook.Sheets(1)
    
    ' It's assumed that A1:A200 are the cells with DDE formulas.
    For i = 1 To 200
      
      ' Set each element of ArrDDE() to the relevant DDE cell.
      ' DDE formulas can be set as well via code, but if so, then do it cell by cell!
      ' Example of DDE formulas in A1:A3 cells:
      '   =DDEServer|TIK!Bid
      '   =DDEServer|TIK!Ask
      '   =DDEServer|TIK!Last
      Set ArrDDE(i) = .Cells(i, 1)
      
      ' Link cells with DDE formulas directly to the relevant macros
      ' It sets the links:
      '   ThisWorkbook.SetLinkOnData "DDEServer|TIK!Bid", "Macro1"
      '   ThisWorkbook.SetLinkOnData "DDEServer|TIK!Ask", "Macro2"
      '   ThisWorkbook.SetLinkOnData "DDEServer|TIK!Last", "Macro3"
      '   ... and so on
      .Parent.SetLinkOnData Mid(.Cells(i, 1), 2), "Macro" & i
      
    Next
  
  End With
  
End Sub

' Macros which are already linked by SetLinkOnData
' Macro1 ... Macro200 will be triggered automatically at relevant DDE cells updating
Private Sub Macro1()
  Update 1    ' Bid
End Sub
Private Sub Macro2()
  Update 2    ' Ask
End Sub
Private Sub Macro3()
  Update 3    ' Last
End Sub
' === and so on up to Macro200 ===
Private Sub Macro200()
  Update 200  ' 200th DDE cell
End Sub


' This subroutine is for the case the processing of Macro1 ... Macro200 is similar
' to each other, otherwise use only the code of  Macro1 ... Macro200
Private Sub Update(ArrIndex As Long)
  
  Dim v
  
  ' Read the value from the updated DDE cell
  v = ArrDDE(ArrIndex).Value
  
  ' Use your logic for filling up ArrFlags()
  Select Case ArrIndex
    Case 1
      '... code for triggering of 'Bid' DDE cell updating
    Case 2
      '... code for triggering of 'Ask' DDE cell updating
    Case 3
      '... code for triggering of 'Last' DDE cell updating
    
    ' === and so on up to Case 200 ===
    
    Case 200
      '... code for triggering of 200th DDE cell updating
      
  End Select
  
  ' Common processing code
  ' ...
  
End Sub

Regards,
 
Last edited:
Upvote 0
Hi, Vladimir. Thank you for reading and replying to my post. I’ll give your suggestion a try, once I have thought about and understood it a bit more J. One thing, would it be better (or even possible) to avoid using cells in worksheets all together? For example, could I not use something like<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

(not sure if the below is in correct order (or even if it’s correct at all) and what should actually go as “app”, “topic” and Item into DDEInitiate and DDERequest…)
Code: 
[SIZE=3][FONT=Calibri]For i=1 to 200<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  ‘arrIDs is an array with DDE IDs, e.g. “Bid”, “Ask” etc<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  ThisWorkbook.SetLinkOnData “DDEServer|TIK!” & arrIDs(i), “Macro” & CStr(i)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Next i[/FONT][/SIZE]
<o:p></o:p>
<o:p></o:p>
and then on each .SetLinkOnData event retrieve the values via
Code: 
[SIZE=3][FONT=Calibri]lngDDEChannel = Application.DDEInitiate( app := “DDEServer”, topic:=”TIK”)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]For i=1 to 200<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     varResult = Application.DDERequest(lngDDEChannel, arrIDs(i))<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     arrFinalResult(i) = varResult<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Next i<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]‘do I need to .DDETerminate here?..[/FONT][/SIZE]
<o:p></o:p>
<o:p></o:p>
Does the above make any sense and could it be made into a better workable solution?
<o:p></o:p>
Many thanks
<o:p></o:p>
<o:p></o:p>
PS How does one insert a formatted code into a message?..<o:p></o:p>
 
Upvote 0
Kolobok said:
... would it be better (or even possible) to avoid using cells in worksheets all together?
Click to expand...
Sure, here is version of the code without usage of DDE formulas in cells:
Rich (BB code): 

' ZVI:2011-05-15 http://www.mrexcel.com/forum/showthread.php?t=472286
' Ver2: only VBA code is used
Dim IdDDE As Long                 ' DDE channel
Dim ArrDDE(1 To 200) As String    ' DDE items names
Dim ArrFlags(1 To 500) As Boolean ' Array for True/False logic

' Init DDE channel and provide SetLinkOnData
' Run it only once after DDE connection is established
Sub Init()
  
  ' --> DDE settings, change to suit
  Const Server$ = "DDEServer"
  Const Topic$ = "TIK"
  ArrDDE(1) = "BID"
  ArrDDE(2) = "ASK"
  ArrDDE(3) = "LAST"
  ' <-- End of DDE settings
  
  Dim i As Long
  
  ' Init DDE channel
  IdDDE = DDEInitiate(Server, Topic)
  
  ' Provide SetLinkOnData
  With ThisWorkbook
    For i = 1 To UBound(ArrDDE)
      If Len(ArrDDE(i)) > 0 Then
        .SetLinkOnData Server & "|" & Topic & "!" & ArrDDE(i), "Macro" & i
      End If
    Next
  End With
  
End Sub

' Close DDE channel, run it once at the end of session
Sub CloseDDE()
  DDETerminate IdDDE
End Sub

' Macros which are already linked by SetLinkOnData
' Macro1 ... Macro200 will be triggered automatically at relevant DDE item updating
Private Sub Macro1()
  Update 1    ' Bid
End Sub
Private Sub Macro2()
  Update 2    ' Ask
End Sub
Private Sub Macro3()
  Update 3    ' Last
End Sub
' === and so on up to Macro200 ===
Private Sub Macro200()
  Update 200  ' 200th DDE item
End Sub

' This subroutine is for the case the processing of Macro1 ... Macro200 is similar
' to each other, otherwise use only the code of  Macro1 ... Macro200
Private Sub Update(ArrIndex As Long)
  
  Dim v
  
  ' Read the value of DDE item identified by ArrIndex
  v = DDERequest(IdDDE, ArrDDE(ArrIndex))(1)
  ' Uncomment the line below for debugging
  'Debug.Print ArrIndex, v
  
  ' Use your logic for filling up ArrFlags()
  Select Case ArrIndex
    Case 1
      '... code for triggering of 'BID' DDE item updating
    Case 2
      '... code for triggering of 'ASK' DDE item updating
    Case 3
      '... code for triggering of 'LAST' DDE item updating
    
    ' === and so on up to Case 200 ===
    
    Case 200
      '... code for triggering of 200th DDE cell updating
      
  End Select
  
  ' Common processing code
  ' ...
  
End Sub

PS How does one insert a formatted code into a message?
Click to expand...
Try googling of "VB html maker AddIn"

Regards,
 
Last edited:
Upvote 0
Hi, Vladimir. Thank you very much indeed for your reply and the code template. I really appreciate it. Will give it a go this week. Sorry to pester you on this, but is there a way to check if a DDE link has gone dead?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Also, with regard to RTD, I’ve had a look at the site you suggested and was somewhat troubled to find the following sentence at the end of a section “What is a Push Mechanism?”:
<o:p></o:p>
“With a push mechanism, the part of the solution that is feeding the real-time data tries to push the data into Excel. One of the disadvantages of push mechanisms is that they often try to push data into Excel when Excel is not ready for it (for example, while Excel is doing a calculation or has a modal dialog box that needs to be handled). This often leads to dropped updates and sometimes even causes a crash.”
<o:p></o:p>
And this is from MS themselves. Doesn’t really instil much confidence… Would you care to comment and share your thought on this. Many thanks.<o:p></o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Freezing/Logging Data from DDE at 1 Minute Intervals
Replies
1
Views
154
Anthony47
A
A
  • Question Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
453
Dan_W
D
R
  • Question Question
ComboBox Update of Same Value
Replies
0
Views
266
RetireSoon
R
B
  • Question Question
link a VBA checkbox to a Cell to change value
Replies
6
Views
709
NoSparks
N
O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
304
Micron
M

Forum statistics

Threads
1,225,156
Messages
6,183,233
Members
453,152
Latest member
ChrisMd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top