DDE-Link - Capture value in VBA

[depple]

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.

 

[RoryA]

I don't know for sure but I'd suspect that the link is in fact purely the link to the file, and not the specific cell link (as in what you see when you choose Edit-Links) and hence any update on that link triggers the macro.

 

[Kolobok]

In Excel Developer Reference it says “… .SetLinkOnData(Name, Procedure)… Name - The name of the DDE/OLE link, as returned from the LinkSources method.” When I use the LinkSources method to return the array of links it returns 2 links:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Excel.SheetMacroEnabled.12|C:\Users\Documents\Out.xlsm!!Sheet1!R1C1
<o></o>
and
<o></o>
Excel.SheetMacroEnabled.12|C:\Users\Documents\Out.xlsm!!Sheet1!R2C1
<o></o>
so I would expect it to link to specific cells… <o></o>

 

[RoryA]

The reference says a lot of things. Some of it is true.

 

[ZVI]

1.

... is there a way to define a parameter to be passed into a macro during the declaration of SetLinkOnData?

There is no VBA way for this.
But you can reserve macros with code silimar to one in post#7 or post#9

2.

1) One cannot assign a macro using SetLinkOnData method to a link, unless this link is entered in a worksheet cell;

Why not? See the code in post#9

 

[chuckchuckit]

I have not read all the posts here but I have been through the ringer with DDE. Sounds like perhaps your DDE and other program are not taking turns or waiting for each other to finish before their update, and so data is lost. This is reminding me of similar problem I had with DDE conflicting with Excel queries.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
My programs were waiting for each other to finish, but the conflict was in the waiting. To make a long story short the problem was resolved in this manner. I discovered that the DDE timing/wait requirements was conflicting with Excels query wait parameter so my system conflicted and locked up. I tried every conceivable code modification to bypass the wait feature on queries (as once it is removed data would not finishing updating query before other code would run). This caused all kinds of lost data updates etc. This non waiting situation may be similar to your situation.
<o></o>
What it came down to was both Excel and the DDE provider could not be updating at the same time (crashing my system) and in your case not crashing your system, but loosing data because when one situation is running the other is not waiting.
<o></o>
Solution for me: was the DDE provider eventually modified their code so that whenever Excel was doing a query their DDE would halt its processing completely until my query was completed. That fixed the wait conflict crash on my system. Their DDE has a couple second noticeable hang time when my query finishes, but no data is lost, and 2 seconds does not matter since I Excel query only once per minute.
<o></o>
I am guessing that your system might need some sort of more integrated communication between your DDE and Excel for one to perhaps wait while the other finishes its task.
<o></o>
Here is discussions on my DDE problems and how resolved, even though it is not exactly the same as your situation, it may give some ideas on a different approach if applicable. DDE discussion starts post #5.
<o></o>
http://www.mrexcel.com/forum/showthread.php?t=510321
<o></o>
Hope it is of use.
<o></o>
Chuck

 

[Kolobok]

1.
There is no VBA way for this.
But you can reserve macros with code silimar to one in post#7 or post#9

I have decided to use VBA Editor to create macros at run time, so I can add a macro every time I loop through SetLinkOnData code.

2.
Why not? See the code in post#9

What I found is that if I use SetLinkOnData method to assign a macro to be triggered on an update of a DDE, I does not fire unless there is a cell in the worksheet with the DDE link in it. So, while you can write and run a code in VBA that does the assignment, since the macro doesn’t get triggered, it’s of no use.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Kolobok]

This DDE is palaver is driving me nuts! I now have encountered the following problem. I use DDEInitiate (with error trapping) to test if a connection to the server is available. However, what it seems to do when there is no connection is to truncate some of the DDE links in the cells (at random), which then throws up all sorts of errors. Say, I have in some cell a formula
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=servername|tik!ask
<o></o>
Then, after running the following code
<o>

</o:p>
<o:p>[FONT=Courier][COLOR=#00007f]Public[/COLOR] [COLOR=#00007f]Sub[/COLOR] UpdateDDELinks()[/FONT]
[FONT=Courier][COLOR=#00007f]Dim[/COLOR] lngTestChannel [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Long[/COLOR][/FONT]
 
[FONT=Courier]lngTestChannel = -1[/FONT]
 
[FONT=Courier]Application.DisplayAlerts = [COLOR=#00007f]False[/COLOR][/FONT]
[FONT=Courier][COLOR=#00007f]On[/COLOR] [COLOR=#00007f]Error[/COLOR] [COLOR=#00007f]Resume[/COLOR] [COLOR=#00007f]Next[/COLOR][/FONT]
 
[FONT=Courier]lngTestChannel = Application.DDEInitiate("servername", "tik")[/FONT]
 
[FONT=Courier]Application.DisplayAlerts = [COLOR=#00007f]True[/COLOR][/FONT]
[FONT=Courier][COLOR=#00007f]On[/COLOR] [COLOR=#00007f]Error[/COLOR] [COLOR=#00007f]GoTo[/COLOR] ErrHandler[/FONT]
 
 
[FONT=Courier][COLOR=#00007f]If[/COLOR] lngTestChannel = -1 [COLOR=#00007f]Then[/COLOR][/FONT]
[FONT=Courier] MsgBox "Could not open a DDE channel."[/FONT]
[FONT=Courier][COLOR=#00007f]Else[/COLOR][/FONT]
[FONT=Courier] MsgBox "DDE channel is open."[/FONT]
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]If[/COLOR][/FONT]
 
 
[FONT=Courier][COLOR=#00007f]Exit[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]
 
[FONT=Courier]ErrHandler:[/FONT]
 
[FONT=Courier]MsgBox "In Error Handler."[/FONT]
 
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR] [COLOR=#007f00]'UpdateDDELinks[/COLOR][/FONT]</o:p>
<o:p>

</o>
<o></o>
<o>that cell now has</o>
<o><o></o>
=servername|tik!
<o></o>
And I get an error when trying to save workbook
<o></o>
“One or more formulas in this workbook are longer than the allowed limit of 8192 characters” and it points this cell out.
<o></o>
Am I doing something wrong or is it just one of those DDE + Excel issues? Many thanks<o></o>
</o>