Using DDE in VBA

[CPWilson]

Hi,

I am accessing DDE VIA a cell, for example "=TOS|LAST!AAPL". Is it possible to do the same in VBA placing the result directly into a variable?

Thanks!
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[ZVI]

Hi,

You don’t, but you can assign directly the event of DDE-cell changing to VBA macro by the aid of:
ThisWorkbook.SetLinkOnData "TOS|LAST!AAPL", "MyVbaMacroName"

There is a possibility in Visual Basic (but not in VBA) to assign DDE connection string to the property of the form control, for example to the textbox control.

Regards,
Vladimir

 

[clym]

From excel menu select....Insert, name, define.

Under"Names in workbook" type a name such as "lastAAPL"
In "refers to" "=TOS|LAST!AAPL"

'example to access with vba

sub test()
     msgbox [lastaapl]
end sub

 

[ZVI]

From excel menu select....Insert, name, define.

Under"Names in workbook" type a name such as "lastAAPL"
In "refers to" "=TOS|LAST!AAPL"

'example to access with vba

sub test()
     msgbox [lastaapl]
end sub

As nothing was said about the goal of the placing result directly into VBA variable the using the name can has sense.
But is it not the same as through a cell?
How you will determine the event of the name changing in this case?

The main drawback of the cell reading is the relatively slow Excel to VBA data transferring mechanism.
Visual Basic (not VBA) can transfer DDE data directly to the form control (i.e. to the code), it is much faster than read from the cell or name to VBA.

In VBA the fastest way to catch DDE event provides SetLinkOnData method, instead of the sheet’s calculation event triggering (because it's delayed) at which on fast DDE traffic some DDE data can be lost.

 

[smartsource]

As nothing was said about the goal of the placing result directly into VBA variable the using the name can has sense.
But is it not the same as through a cell?
How you will determine the event of the name changing in this case?

The main drawback of the cell reading is the relatively slow Excel to VBA data transferring mechanism.
Visual Basic (not VBA) can transfer DDE data directly to the form control (i.e. to the code), it is much faster than read from the cell or name to VBA.

In VBA the fastest way to catch DDE event provides SetLinkOnData method, instead of the sheet’s calculation event triggering (because it's delayed) at which on fast DDE traffic some DDE data can be lost.

SetLinkonData requires the DDE link to be listed in LinkSources (Data - Edit Links). In order to be listed in LinkSources the DDE link must be placed in a cell. If the cell is not in the active window performance is increased (ie. hidden sheet). Setlinkondata is still the fastest method to trigger a macro to work with the data in VBA/Excel.

I do not believe you can store an updating DDE link into a named range. You can store the link formula which can be placed in a cell and activated with a paste value. You can dynamically build DDE Links using defined names than paste formulas into a worksheet.

 

[ZVI]

Hi smartsource,

SetLinkonData requires the DDE link to be listed in LinkSources (Data - Edit Links). In order to be listed in LinkSources the DDE link must be placed in a cell.

It’s not correct, SetLinkOnData method can establish new DDE link into LinkSources (Data - Edit Links) and set the fast connection between the source DDE server and destination VBA subroutine, see the example of the code in my post#2

Setlinkondata is still the fastest method to trigger a macro to work with the data in VBA/Excel.

I think so, and it was reflected in my posts.

I do not believe you can store an updating DDE link into a named range

Nobody had proposed to set DDE link into a named range in this thread. Clym in post #3 just had mentioned the using of name for DDE link, but not the named range.
My question was "How you will determine the event of the name changing in this case?" and it means that for me there is no any benefits of name usage for DDE link.
Seems that there is nothing to discuss here.

If you've had issue in SetLinkOnData implementation without usage of DDE formulas in the cells, please give the details of the code.

Regards,
Vladimir

 

[smartsource]

You are correct.

One problem I am having is how to identify the cell address of the link that triggered setlinkondata. ie. a table of 50 stock quotes, 500 links. I have a macro that uses index(range, ** row **,) to process price changes from a previously trapped Workbook on Calculate routine. Still working through changes... setlinkondata seems to alleviate the dde update delays.

If i bypass placing the dde links on a worksheet and establish the link directly as you have suggested i suppose i can use dderequest in a update macro to retrieve the changing values and keep it all in a VBA array. This would probably be the fastest method.

Hi smartsource,


It’s not correct, SetLinkOnData method can establish new DDE link into LinkSources (Data - Edit Links) and set the fast connection between the source DDE server and destination VBA subroutine, see the example of the code in my post#2

 

[smartsource]

Testing setlinkondata for approx 500 dde links grabbing market data.. ie ask, bid etc. Each update triggers just a counter.

This method appears to queue all dde updates without drops. Problem now is the worksheet can not keep up and process these updates in real time. When I pause the dde server application the worksheet continues to process updates FIFO until the queued updates flush out.

Worksheet still contains dde links with calculations based on these cells. I need to rework the worksheet, DDE links need to be removed and replaced with vba processed values to maintain real time pace.

Very fast and efficient method but requires substantial rework. I'm concerned about data integrity and knowing if I'm working with the latest update.

public ddecount as long
Sub Start_Links()
    Dim aLinks As Variant
    Dim i As Long
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
        ActiveWorkbook.SetLinkOnData aLinks(i), "ddecounter"
        Next i
    End If
End Sub

Sub ddecounter()
ddecount = ddecount + 1
End Sub

Hi smartsource,

If you've had issue in SetLinkOnData implementation without usage of DDE formulas in the cells, please give the details of the code.

Regards,
Vladimir

 

[Legacy 98055]

Smart. I am not at all sure that you are going to gain a leap in performance by doing anything in VBA.

You need to create your own DDE client and then delegate to separate intances of Excel which have been given thread priority. That or buy a new computer that can keep up!

 

[smartsource]

Tom,

My objective is to process rapidly changing dde data faster. The pure worksheet method with dde links in cells trapping on_calculate to process updates is not multithreading and has a delay before new data updates appears in the worksheet. For unknown reasons or by design Excel will not automatically update dde links faster than 100ms.

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. Obviously there is substantial overhead pushing DDE data into the worksheet and back into VBA to perform calculations and my worksheet needs to be reworked and optimized.

For testing I made a worksheet with the DDE Links for 20 stock symbols x 5 columns (Bid,Ask,Volume,Time,Symbol) in a range ("Feed_DDELinks") with a macro set to copy the entire range to another worksheet range ("Feed_value") to simulate doing work with the data.

sub process_update()
Range("Feed_Value") = Range("Feed_DDELinks").value2
end sub

The On_calculate method processes new DDE updates approx 132ms when visible and 116ms when hidden. No queueing and all updates are transmitted to grid in one shot. No matter what you try Excel will not refresh DDE data to the grid faster than 100ms.

Using setlinkondata each update individually triggers process_update and performs the 100 cell copy in 32ms when visible and 16ms when hidden. The problem is the triggered range copy occurs before excel has refreshed the ddelinks data range creating data integrity issues.

How Frequently Can Excel Accept Updates?
The number of times that a single topic can be updated seems to be limited by the number of times that Excel checks for Microsoft Windows messages, which is at most 700 times per second. Since some of the messages have higher priority Excel effectively gets about 200 updates per second. http://msdn.microsoft.com/en-us/library/aa140060%28office.10%29.aspx


To fix the data integrity issue I need to eliminate all dde links from the worksheet and process all real time updates in VBA writing just the end results to the grid... 10ms vs 100ms is substantial enough to support the effort for this particular application.

Smart. I am not at all sure that you are going to gain a leap in performance by doing anything in VBA.

You need to create your own DDE client and then delegate to separate intances of Excel which have been given thread priority. That or buy a new computer that can keep up!