How to Evaluate Dynamic DDE Formulas

[MArcus Baffa]

Hi All,

I am new in Excel, but I have some experience in general programming. I

have a problem with DDE dynamic formulas.

I have an application that maintains templates of Excel Formulas in SQL

Server. What I need is to load these formulas in Excel and evaluate
them.

These formulas are, in fact, DDE Links to a resource server. The
resource
values are updated as they change, and I need that the loaded formulas

respond to the DDE advise event. That is they should change continuosly
as
the resource changes.

And more some formulas must be contructed based on a specific cell. For

instance Column 1 has the name of the resource, so the DDE Link formula
in
column 2 must be of type =appserver|topic!&CONTENTS OF ("C1").

Well if I manually put the formulas in a cell like "=app|topic!item"
they work.
The Server is working OK !!!!

To load the formulas from SQL Server is simple I have already done it.
The
problem:

- How to contruct the formula dynamically
- How to evaluate it so that it is refreshed as their value changes in

the server

Thanks in advance

 

[Harlan Grove]

MArcus Baffa wrote...
....

These formulas are, in fact, DDE Links to a resource server. The
resource values are updated as they change, and I need that the loaded formulas
respond to the DDE advise event. That is they should change continuosly as
the resource changes.

And more some formulas must be contructed based on a specific cell. For
instance Column 1 has the name of the resource, so the DDE Link formula in
column 2 must be of type =appserver|topic!&CONTENTS OF ("C1").

Well if I manually put the formulas in a cell like "=app|topic!item"
they work. The Server is working OK !!!!

....

You've come across one of the archaic deficiencies of Excel. DDE links
in cell formulas can only be static, that is, =app|topic!item. You'd
need to use VBA to make them function dynamically, and that involves
using user-defined formulas (udfs) in VBA to create another Excel
application instance, construct static DDE link formulas and evaluate
them in the other instance, then return their values to the calling
instance. It's slow and relatively fragile.

Can you use VBA to do this?

 

[MArcus Baffa]

Thanks Harlan,

Well I have a good experience in VB, maybe I could implement it in
VBA.

But as I have told I do not have much experieence in Excel and its
Object Model. I did not know that we could develop UDF.

Where can I read about this ????

Thanks again

 

[xlcharlie]

Hi Marcus,

I found your posting and was hoping you might be able to help me with a DDE
problem I am having. I hope this is not an intrusion.

I am trying to create a dynamic Excel function that will use DDE Links to
pull in
information from another program. The code I have so far, where symb and fld
are cell values, is as follows:

Public Function ExternalData(symb As String, fld As String)
Dim ch As Variant, code As Variant

ch = DDEInitiate(App:="ExtSys", Topic:="Quote")
code = DDEAppReturnCode
If code <> 0 Then
LiquidData = "No DDE"
Exit Function
End If
ExternalData = DDERequest (channel:=ch, item:=symb)
code = DDEAppReturnCode
If code <> 0 Then
ExternalData = "DDE Failure"
Exit Function
End If
DDETerminate Channel:=ch

End Function

I'm brand new to using DDE links in VBA so I'm not sure where I've gone
wrong, but the function results in the value "FALSE" when it should be
giving me a numeric value (a stock quote).

Also when I look in the Links Editor, it does not appear any
DDE Links have been established, even when I comment out the DDETerminate in
the code. I've tried replacing "DDERequest" in the code with the DDE link
formula -

Evaluate ("=ExtSys|" & fld & "!" & symb)

- but because I can't seem to
establish the DDE links, this just results in a "N/A" error. Any tips on how
I can make this work? I realize it's a difficult question to field without
knowing the system Excel is going out to.

Thanks for the help!

 

[xlcharlie]

Hi Harlan,

I actually just posted a question to Marcus that I meant to direct to you.
I found your posting and was hoping you might be able to help me with a DDE
problem I am having. I hope this is not an intrusion.

I am trying to create a dynamic Excel function that will use DDE Links to
pull in
information from another program. The code I have so far, where symb and fld
are cell values, is as follows:

Public Function ExternalData(symb As String, fld As String)
Dim ch As Variant, code As Variant

ch = DDEInitiate(App:="ExtSys", Topic:="Quote")
code = DDEAppReturnCode
If code <> 0 Then
LiquidData = "No DDE"
Exit Function
End If
ExternalData = DDERequest (channel:=ch, item:=symb)
code = DDEAppReturnCode
If code <> 0 Then
ExternalData = "DDE Failure"
Exit Function
End If
DDETerminate Channel:=ch

End Function

I'm brand new to using DDE links in VBA so I'm not sure where I've gone
wrong, but the function results in the value "FALSE" when it should be
giving me a numeric value (a stock quote).

Also when I look in the Links Editor, it does not appear any
DDE Links have been established, even when I comment out the DDETerminate in
the code. I've tried replacing "DDERequest" in the code with the DDE link
formula -

Evaluate ("=ExtSys|" & fld & "!" & symb)

- but because I can't seem to
establish the DDE links, this just results in a "N/A" error. Any tips on how
I can make this work? I realize it's a difficult question to field without
knowing the system Excel is going out to.

Thanks for the help!

 

[xlcharlie]

Sorry, I just posted a question to you that I meant to send to Harlan. I
apologize, but if you have any tips, I'm desperate for help. THanks!