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.
 
Sort by date Sort by votes
Hello again. I have another question (more like many questions). (Sorry, I have more question then answers at the moment, but hopefully by learning from here now, I could repay the favour to someone else in the future.) So here they are:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Once a DDE link is updated and a Macro assigned in .SetLinkOnData method is called, what happens to DDE links whilst this Macro is running? Do they still get updated? Is so, does .SetLinkOnData method call Macro again? If so, what happens to the first instance of Macro – does it run to completion or is it terminated? Or do the DDE links not get updated while VBA is running? Do the updates get stored or are they dropped? As you can tell I don’t know much about the sequence of execution in Excel/VBA and perhaps you could recommend a good source of information on this. <o:p></o:p>
Many, many, many thanks. J<o:p></o:p>
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
1.
Kolobok said:
...is there a way to check if a DDE link has gone dead?
Click to expand...
Use error trapping for this:
Rich (BB code): 

Sub Init()
  
  ' ...
  
  On Error GoTo error_trap
  
  ' Init DDE channel
  IdDDE = DDEInitiate(Server, Topic)
  
  ' Provide SetLinkOnData
  '...

error_trap:
  If Err Then
    DDETerminate IdDDE
    MsgBox "Error of DDE Init"
  End If

End Sub

Private Sub Update(ArrIndex As Long)
  
  On Error GoTo error_trap
  
  ' Read the value of DDE item identified by ArrIndex
  v = DDERequest(IdDDE, ArrDDE(ArrIndex))(1)
  ' ...
  ' Common processing code
  ' ...

error_trap:
  If Err Then
    DDETerminate IdDDE
    MsgBox "Error in Update subroutine"
  End If
  
End Sub

2.
Doesn’t really instil much confidence… Would you care to comment and share your thought on this
Click to expand...
There is no special comment because info of FAQ is honest.
Issues of DDE mentioned in FAQ were known initially.
If Excel is not ready for the updating (in calculation, on dialogs, in editing) then pushed data are lost.
You can find the crashing scenario in the further link below.

3.
Once a DDE link is updated and a Macro assigned in .SetLinkOnData method is called, what happens to DDE links whilst this Macro is running? Do they still get updated? Is so, does .SetLinkOnData method call Macro again? If so, what happens to the first instance of Macro – does it run to completion or is it terminated? Or do the DDE links not get updated while VBA is running? Do the updates get stored or are they dropped?
Click to expand...
DDE Server stores only current value of the DDE linked symbol.
Updating history is not stored for the symbol on the DDE Server side.
If updating on the server side is too fast for Excel receiving then some data will be lost for Excel.
DDERequest reads only the present value of DDE linked data at the requested time.

4.
As you can tell I don’t know much about the sequence of execution in Excel/VBA and perhaps you could recommend a good source of information on this.
Click to expand...
My preference is the practice with step debugging of the code.
For the theory and code examples try Internet searching or try searching MrExcal Board.
This is just one of the good links from the googling of keywords "DDE VB6 example": Dynamic Data Exchange Interface Details
To improve your VBA knowledge use resources from Hiker’s excellent list Training / Books / Sites

Regards,
 
Last edited:
Upvote 0
Hi, Vladimir. Thank you for your replies and for the code template. Much appreciated. However, I probably didn’t quite make myself clear when I was asking the question on DDE links updating during running of macros, so let me set it out again.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Let’s say a DDE link is updated on a server and this triggers Macro1 which was assigned via .SetLinkOnData method. While this macro is running, another (or the same) DDE link, which is set up to trigger Macro2 (or Macro1 if it’s the same link), is updated on the server. What happens in this situation, when an update occurs during an execution of a VBA code? Is the second update registered? Does it trigger Macro2 (Macro1)? If so, does Macro2 wait till Macro1 finishes (or does the second instance of Macro1 wait till the first instance finishes)? Or does the second terminate the first? Or is the update trigger queued and waits until all VBA stops running? Or is the second update of the link is not registered at all?
<o:p></o:p>
Many thanks<o:p></o:p>
 
Upvote 0
Any update, which occurs during an execution of a VBA code, will be lost (dropped).
Such update does not trigger the linked macro at all, and it's not registered by Excel.
 
Last edited:
Upvote 0
Thank you. Sorry for being a pest, but is there a way to find out in VBA which channel is being used by a DDE link in a spreadsheet? Thanks
 
Upvote 0
OK. I have run a test using two separate instances of Excel with one DDE link in each Workbook connected to the same data feed, one Workbook used Worksheet_Calculate event and the other SetLinkOnData to run Macros that wrote out Timer values. It would appear that SetLinkOnData fires about 100 milliseconds before the _Calculate event, in this case.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Not sure yet how they will compare once there are, say, 100 links in each workbook. I seem to remember from earlier experiments that Worksheet_Calulate event is not triggered until all calculation on a worksheet is complete, which might be preferable to triggering when only one link is updated. Am gonna have to do some tests on this…
<o:p></o:p>
One problem with SetLinkOnData though, the only way I could stop it was by terminating the connection to the server, which is not great. Question: is there a way to Unset Link On Data so that it stops triggering a macro?<o:p></o:p>
Many thanks<o:p></o:p>
 
Upvote 0
1.
Kolobok said:
... is there a way to find out in VBA which channel is being used by a DDE link in a spreadsheet?
Click to expand...
See the code in my post#7

2.
Kolobok said:
...is there a way to Unset Link On Data so that it stops triggering a macro?
Click to expand...
There is the code to set / unset macro triggering:
Rich (BB code): 

  ' Set Link On Data to Macro1 triggering
  ThisWorkbook.SetLinkOnData "DDEServer|TIK!BID", "Macro1"
  
  ' Unset Link On Data from the Macro1 triggering
  ThisWorkbook.SetLinkOnData "DDEServer|TIK!BID", ""

Regards,
 
Upvote 0
Thank you, Vladimir, once again. J I have another one for you: is there a way to define a parameter to be passed into a macro during the declaration of SetLinkOnData? The thing is, the number of linked DDE cells contained in my workbook is not fixed, so I don’t know how many Macros I need to predefine to be assigned during SetLinkOnData declaration. Or I need to create some kind of an object which contains macros, which I can then reference in SetLinkOnData.. Is this even doable? Or is there a better way to go around this? Many thanks.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
 
Upvote 0
OK. I have done two more experiments and found that
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
1) One cannot assign a macro using SetLinkOnData method to a link, unless this link is entered in a worksheet cell;
<o:p></o:p>
2) Excel will drop all but the last updates of a link during an execution of a VBA code. For example, if a link changes in value to 1 and triggers some macro via SetLinkOnData method and then, whilst the macro is running, it first changes to 2 and then to 3, then once the macro triggered by 1 has finished, it (the macro) will be triggered again (the second and laast time) by 3.
 
Upvote 0
I seem to have run into some strange behaviour of SetLinlOnData method – it triggers a macro if any link is updated and not only the one that was assigned in the declaration. I was wondering if someone could see if they can replicate what I’m seeing. Thanks. Here is my set up.

1) Run two separate instances of Excel: In.xlsm and Out.xlsm.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
2) Link cells A1 and A2 on Sheet1 in In.xlsm to cells A1 and A2 in Sheet1 in Out.xlsm respectively.<o:p></o:p>
3) In Module1 of In.xlsm put the following code (note the link to A2 in Out.xlsm is not set):<o:p></o:p>

<o:p></o:p>
<o:p>
Code: 
</o:p>
<o:p>[FONT=Courier][COLOR=#00007f]Public[/COLOR] [COLOR=#00007f]Sub[/COLOR] setLink()[/FONT]
[FONT=Courier]Application.EnableCancelKey = xlErrorHandler[/FONT]
[FONT=Courier][COLOR=#00007f]On[/COLOR] Error [COLOR=#00007f]GoTo[/COLOR] ErrHandler[/FONT]
 
[FONT=Courier]ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!R1C1'", "Macro1"[/FONT]
[FONT=Courier][COLOR=#007f00]'ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!R2C1'", "Macro2"[/COLOR][/FONT]
 
[FONT=Courier]MsgBox "Links set"[/FONT]
 
[FONT=Courier]ErrHandler:[/FONT]
[FONT=Courier][COLOR=#00007f]If[/COLOR] Err.Number = 18 [COLOR=#00007f]Then[/COLOR][/FONT]
[FONT=Courier]unsetLinks[/FONT]
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]If[/COLOR][/FONT]
[FONT=Courier]Application.EnableCancelKey = xlInterrupt[/FONT]
 
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]
 
 
[FONT=Courier][COLOR=#00007f]Public[/COLOR] [COLOR=#00007f]Sub[/COLOR] Macro1()[/FONT]
[FONT=Courier]Application.EnableCancelKey = xlErrorHandler[/FONT]
[FONT=Courier][COLOR=#00007f]On[/COLOR] Error [COLOR=#00007f]GoTo[/COLOR] ErrHandler[/FONT]
 
[FONT=Courier]MsgBox "Macro1"[/FONT]
 
[FONT=Courier][COLOR=#00007f]Exit[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]
 
[FONT=Courier]ErrHandler:[/FONT]
[FONT=Courier][COLOR=#00007f]If[/COLOR] Err.Number = 18 [COLOR=#00007f]Then[/COLOR][/FONT]
[FONT=Courier]unsetLinks[/FONT]
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]If[/COLOR][/FONT]
[FONT=Courier]Application.EnableCancelKey = xlInterrupt[/FONT]
 
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]
 
 
[FONT=Courier][COLOR=#00007f]Public[/COLOR] [COLOR=#00007f]Sub[/COLOR] Macro2()[/FONT]
[FONT=Courier]Application.EnableCancelKey = xlErrorHandler[/FONT]
[FONT=Courier][COLOR=#00007f]On[/COLOR] Error [COLOR=#00007f]GoTo[/COLOR] ErrHandler[/FONT]
 
[FONT=Courier]MsgBox "Macro2"[/FONT]
 
[FONT=Courier][COLOR=#00007f]Exit[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]
 
[FONT=Courier]ErrHandler:[/FONT]
[FONT=Courier][COLOR=#00007f]If[/COLOR] Err.Number = 18 [COLOR=#00007f]Then[/COLOR][/FONT]
[FONT=Courier]unsetLinks[/FONT]
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]If[/COLOR][/FONT]
[FONT=Courier]Application.EnableCancelKey = xlInterrupt[/FONT]
 
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]
[FONT=Courier][COLOR=#00007f][/COLOR][/FONT] 
 
[FONT=Courier][COLOR=#00007f]Public[/COLOR] [COLOR=#00007f]Sub[/COLOR] unsetLinks()[/FONT]
 
[FONT=Courier]ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!R1C1'", ""[/FONT]
[FONT=Courier]ThisWorkbook.SetLinkOnData "Excel.SheetMacroEnabled.12|'C:\Users\Documents\Out.xlsm'!'!Sheet1!R2C1'", ""[/FONT]
[FONT=Courier][/FONT] 
[FONT=Courier]MsgBox "Links unset"[/FONT]
[FONT=Courier][/FONT] 
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]
<o:p>
</o:p>
<o:p></o:p>
4) Change value in cell A2 on Sheet1 in Out.xlsm – a message box “Macro1” is displayed. :confused:

PS VBHTML maker didn't seem to preserve the indentations. I pasted the generated cocde inside the CODE brackets. Do I need to do anything else with it? Thanks

<o:p></o:p>
</o:p>
 
Last edited:
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
62
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
413
Dan_W
D
R
  • Question Question
ComboBox Update of Same Value
Replies
0
Views
256
RetireSoon
R
B
  • Question Question
link a VBA checkbox to a Cell to change value
Replies
6
Views
628
NoSparks
N
O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
257
Micron
M

Forum statistics

Threads
1,224,551
Messages
6,179,472
Members
452,915
Latest member
hannnahheileen

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