Run Dynamically Created DDE Strings as Function in VBA

Dear all,


I really could not find a simiar problem to mine, so I highly welcome any guidance from you:


String Example: "=APP|DATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads real-time stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 20-50 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the only-changing bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various if-then calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newly-created DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".

One interesting observation of mine is that if I manually type "=APP|DATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.



How I make the function call to get the real-time data is:

A1 YHOO

B1 other stock symbol

C1 another stock symbol


A2 =FunctionEvaluate($A1,"Bid")

A3 =FunctionEvaluate($A1,"Ask")

A4 =FunctionEvaluate($A1,"Lot")

and seven more


The function is:

Function FunctionEvaluate(Symbol, Field)
' Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APP|DATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APP|DATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function


When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new real-time data to make and return numerous if-then calculations.

Currently, it does not because the FunctionEvaluate above returns "N/A".


Lastly, my attempts to formulate DDE strings and get the real-time data below failed:

=INDIRECT("APP|DATA!'" & $A1 &".Ask'")

=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")

=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))

=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))

="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here

=eval("=APP|DATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"

=SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))


Since the old-macro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.


I think I have already tried all possible alternatives to get the job done but failed.


Thanks in advance for all your return

Eros

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.