alternative to embedding cell reference within DDE link

[andy]

Hello,

I read (somewhere) that it isn't possible to embed a reference to a
specific excel cell in a DDE link. I'm attempting to replace the
following:

=JavaDdeServer|IBM!LAST_PRICE

with

=JavaDdeServer|$A1!LAST_PRICE

where the value of cell A1 would be IBM (or whatever ticker symbol
resided in cell A1). I thought about writing a VBA macro which would
accept two parameters (TOPIC and ITEM), but I'm not sure what to do
next? Could I generate the DDE function call by just concatenating
the Topic and Item to the "=JavaDdeServer|" function? If so, how
would I return the value?

Any example would be greatly appreciated and thanks in advance for any
help you can provide.

Andy

 

[Tom Ogilvy]

You can't do what you show

sStr = Range("A1").Value
Range("B9").Formula = "=JavaDdeServer|" & sStr & "!LAST_PRICE"

 

[andy]

Thank you Tom!

I still haven't been able to successfully create a function (or sub)
to do this for me automatically.

Here's the function I have:

Function SubscribeQ(t As String, i As String)

ActiveCell.Formula = "=JavaDdeServer|" & t & "!" & i

End Function

I receive a "#VALUE!" response from the function with an error stating
"The value used in the formula is of the wrong data type".

Is this because I'm trying to assign a value to the Formula attribute
of the active cell?

Btw, here's how I'm calling the function in Excel:

=SubscribeQ("ORCL","VOLUME")

Thanks for any input.

Warm regards,

Andy

 

[Steve Garman]

Worksheet functions are very restricted in what they let you do.

Apart from returning a value they won't let you change anything on the
worksheet.

Try putting Tom's code into the Worksheet_Change event, like this
(untested, for obvious reasons)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sStr As String
If Not Intersect(Target, Range("A1")) Is Nothing Then
sStr = Range("A1").Value
Range("B9").Formula = "=JavaDdeServer|" & sStr & "!LAST_PRICE"
End If
End Sub

 

[andy]

Thanks for everyone's help to date.

I'm new to VBA, so I'm having difficulty determining what I can and
can't do within a vba function/subroutine called from an Excel cell.

I have a DDE server that feeds Excel clients with financial updates.
I can successfully register the cell with *raw* DDE calls from an
Excel cell like so:

=JavaDdeServer|YHOO!VOLUME

But, what I'd prefer is a function which users could call which would
allow them to pass in cell references; thus making it easier to use.
I imagined the function looking like this:

Function SubscribeQ(TheTopic As String, TheItem As String)

ActiveCell.Formula = "=JavaDdeServer|" & TheTopic & "!" & TheItem

End Function

Then a user could pass in a reference to another cell (which might
contain the symbol IBM when calling the function, thus reducing the
repeated *hard coded* text in each function call).

E.g., if IBM were in cell A1, then the user might do something like
this:

Contents of cell A2: "=SubscribeQ($A1,BID)"
Contents of cell A3: "=SubscribeQ($A1,ASK)"

....

Contents of cell An: "=Subscribe!($A1,LAST_PRICE)"

I just can't seem to figure out how to go about doing this. I'm
starting to think that this cannot be done in a function, but rather
should be written in a VBA subroutine??

I apologize for my ignorance in these matters. I had hoped this would
be straightforward.

Thanks in advance for any help you can provide.

Regards,

Andy

within the function I was going to assign the ActiveCell's Formula
attribute to the

 

[Steve Garman]

You can't call a sub from a cell (unless someone can tell me different)

You can call a fuction from a cell but if you do it is very restricted
in what it can do.

It can't do anything (any thing useful, anyway) that will change the
worksheet or the environment.

Effectively, all it can do is those things that change the way it
calculates the value that it will return.

If you can call the javaDdeServer direct from your code, you may be able
to write a function like:

Function SubscribeQ(TheTopic As String, TheItem As String)
Dim retVal As String
retVal = myDDEcall("JavaDdeServer|" & TheTopic & "!" & TheItem)
SubscribeQ = retVal
End Function
which would then return the result to the calling cell.

But I'm afraid you're on your own when it comes to writing myDDEcall
because I have no idea what it is or what it does.