eval and kepdde

Question

First off i want to show the formula that works correctly.  =kepdde|_ddedata!Channel1.M340.INT_1 returns the value of the tag inside kepserver with no problems. I want to create a column that concatenates  =kepdde|_ddedata!Channel1.M340. and INT_1 so the user just has to enter the tag name in the tag column. When i do this i get #REF! error. Any help would be appreciated.

Answer 1

right click on the sheet tab where you want this behavior.

in the resulting module put in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$1" Then

 If Len(Trim(Target.Value)) <> 0 Then

  Target.Offset(0, -1).Formula = "=kepdde|_ddedata!Channel1.M340." & Target.Value

 End If

End If

End Sub

this will fire each time a cell changes.   It checks if the cell that triggered the event (changed) is cell B1.  In otherwords, you just typed Int_1 in cell B1.  It will then put the formula  

=kepdde|_ddedata!Channel1.M340.Int_1  in cell A1 if B1 contains the value  Int_1 

when the Change event is fired, the parameter "Target" holds a range reference to the cell(s) that triggered the event.   So the first thing is to check if cell B1 triggered the event.   Then check if B1 is not empty.   If it passes those two tests, it assumes that B1 has a valid value to describe a legitimate Item for you server and it builds the DDE formula.

if you get prompted that this is a dangerous operation, you might try adding:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$1" Then

 If Len(Trim(Target.Value)) <> 0 Then

  Application.DisplayAlerts = False

  Target.Offset(0, -1).Formula = "=kepdde|_ddedata!Channel1.M340." & Target.Value

  Application.DisplayAlerts = True

 End If

End If

End Sub

if that doesn't suppress the message, then you are out of luck in terms of suppressing the message.

You can of course change the $B$1 to a cell in your tag column.  If you wanted to work on cells say B2:B30 in your tag column you could alter the code to  

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B2:B30")) Is Nothing Then

 If Len(Trim(Target.Value)) <> 0 Then

  Application.DisplayAlerts = False

  Target.Offset(0, -1).Formula = "=kepdde|_ddedata!Channel1.M340." & Target.Value

  Application.DisplayAlerts = True

 End If

End If

End Sub

--

Regards,

Tom Ogilvy

Answer 2

Wow thank you so much tom it works perfectly!

Answer 3

Tom could you give me an example on how to do this with a Macro? I'm very new with Macro's.

Answer 4

Using VBA in excel,

Use the change event against the tag column and have the code in the change event build the proper DDE formula.

I am pretty sure you can't use concatenation to build a dde formula dynamically but I don't claim to be an authority on this.

--

Regards,

Tom Ogilvy