Can I make my VBA trigger automatically off a cell updated from a DDE link?

[mIsT3r_x]

Here's what I'm trying to do - use Excel's DDE capabilities to communicate with a Rockwell PLC.

For a proof of concept, I'm setting a boolean tag in the PLC, which is read as 1 or 0 via the DDE link in Excel, and I would like that to populate a list of 10 random numbers between 1 and 4. I'm just using 10 cells with the RAND func which recalculates on the boolean tag. The problem I'm having is trying to get those 10 values written back to the PLC automatically. I can do it manually with an ActiveX Button, but can't find a way to automate this function off the trigger tag.

I've read a lot of stuff, and some doesn't even make sense anymore. I'm having a hard time getting the parts stitched together. I read an old thread here from 2000 that I thought would work but doesn't. Here's the code I have so far with a lot of commented code for things I tried and didn't work.

Private Function OpenRSLinx()
    On Error Resume Next
    
    'Open the connection to RSLinx
    OpenRSLinx = DDEInitiate("RSLINX", "EXCEL_TEST")
    
    'Check if the connection was made
    If Err.Number <> 0 Then
        MsgBox "Error Connecting to topic", vbExclamation, "Error"
        OpenRSLinx = 0 'Return false if there was an error
    End If
    
End Function


''''''''''''Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''If Target.Cells.Count > 1 Then Exit Sub
''''''''''''If Target.Cells.Address = "$B$1" Then "use a macro here"
''''''''''''End Sub


'Private Sub Worksheet_Change(ByVal Target As Range)
  '  Dim KeyCells As Range


' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
  '  Set KeyCells = Range("A1:A1")


'If Not Application.Intersect(KeyCells, Range(Target.Address)) _
      '     Is Nothing Then


' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
       ' rslinx = OpenRSLinx() 'Open connection to RSlinx


    'Loop through the cells and write values to the CLX array tags
    'For i = 0 To 9
        'Now the array of DINTs
        'Get the value from the DDE link
        'dintdata = DDERequest(rslinx, "DINT_Array[" & i & "],L1,C1")
        'If there is an error, display a message box
        'If TypeName(dintdata) = "Error" Then
            'If MsgBox("Error reading tag DINT_Array[" & i & "]. " & _
               ' "Continue with write?", vbYesNo + vbExclamation, _
               ' "Error") = vbNo Then Exit For
       ' Else
            'No error, place data in CLX
           ' DDEPoke rslinx, "DINT_Array[" & i & "]", Cells(2 + i, 5)
       ' End If
   ' Next i
    
    'Terminate the DDE connection
   ' DDETerminate rslinx


'End If
'End Sub
Private Sub CommandButton1_Click()


            rslinx = OpenRSLinx() 'Open connection to RSlinx
        
         'Loop through the cells and write values to the CLX array tags
    For i = 0 To 9
   
        'Now the array of DINTs
        'Get the value from the DDE link
        'dintdata = DDERequest(rslinx, "DINT_Array[" & i & "],L1,C1")
        'If there is an error, display a message box
        If TypeName(dintdata) = "Error" Then
            If MsgBox("Error reading tag DINT_Array[" & i & "]. " & _
                "Continue with write?", vbYesNo + vbExclamation, _
                "Error") = vbNo Then Exit For
        Else
            'No error, place data in CLX
            DDEPoke rslinx, "DINT_Array[" & i & "]", Cells(1 + i, 5)
        End If
    Next i
    
    'Terminate the DDE connection
    DDETerminate rslinx


End Sub

 

[NoSparks]

I copy your posted code into a single module and without Option Explicit the only compile error I get is
Ambiguous name detected: OpenRSLinx
which is due to functions with the same name.

I remove the unnecessary function and with Option Explicit, which I always use,
all I get is a few Variable not defined errors, which are kinda self explanatory,
so I'm afraid I have no idea what's with compile error:Sub or Function not defined.

 

[mIsT3r_x]

I have to go back to the drawing board on this, this code no longer works as it did yesterday (no longer picks up the DDE change to run the macro), which is twisting my brain into knots. I'll try your trigger code next instead, thx.

 

[mIsT3r_x]

Fixed. For whatever reason, there is a ' in front of the " at the end of the DDE link address. I pulled that out and all is good. Don't know how it was running yesterday with that in there, or how it got there, but it's gone now, and all is good.