0

I'm trying to write few data from MSExcel to PLC tags. Here I use the DDEpoke instructions and run my macro which writes the data perfectly to my PLC. Now I want this operation to be done automatically. Whenever the values in a column changes (based on the calculations made in the previous columns) the DDE enabled macro has to run the macro for writting those tags to PLC. I tried many macros which does the autorun of macro but its either not working or I'm doing some mistake. Someone please help me on this.

codes:

    Sub PLC_Transfer()
'
' PLC_Transfer Macro
'
'
' DDE_Transfer Macro
' Transfer DDE data to PLC
'
RSLinx_channel = DDEInitiate("RSLinx", "Excel")
'write data thru channel
DDEPoke RSLinx_channel, Range("X6"), Range("W6")
DDEPoke RSLinx_channel, Range("X7"), Range("W7")
DDEPoke RSLinx_channel, Range("X8"), Range("W8")
DDEPoke RSLinx_channel, Range("X9"), Range("W9")
DDEPoke RSLinx_channel, Range("X10"), Range("W10")
DDEPoke RSLinx_channel, Range("X11"), Range("W11")
DDEPoke RSLinx_channel, Range("X12"), Range("W12")
DDEPoke RSLinx_channel, Range("X13"), Range("W13")
DDEPoke RSLinx_channel, Range("X14"), Range("W14")
DDEPoke RSLinx_channel, Range("X15"), Range("W15")
DDEPoke RSLinx_channel, Range("X16"), Range("W16")
DDEPoke RSLinx_channel, Range("X17"), Range("W17")
DDEPoke RSLinx_channel, Range("X18"), Range("W18")
DDEPoke RSLinx_channel, Range("X19"), Range("W19")
DDEPoke RSLinx_channel, Range("X20"), Range("W20")
DDEPoke RSLinx_channel, Range("X21"), Range("W21")
DDEPoke RSLinx_channel, Range("X22"), Range("W22")
DDEPoke RSLinx_channel, Range("X23"), Range("W23")
DDEPoke RSLinx_channel, Range("X25"), Range("W24")
DDEPoke RSLinx_channel, Range("X25"), Range("W25")

'close dde channel
DDETerminate (RSLinx_channel)
End Sub

This is the macro I used for writing tags to PLC. Here how to add an instruction which monitors the change in the column 'W' ?

edit: here I add an eg for clear understanding

AT 00:00 AM

W   X            
0   Excel_TBM101
0   Excel_TBM101
0   Excel_TBM102
0   Excel_TBM103
0   Excel_TBM104
0   Excel_TBM105
0   Excel_TBM106

AT 00:05 AM

W   X            
0   Excel_TBM101
0   Excel_TBM101
0   Excel_TBM102
1   Excel_TBM103
0   Excel_TBM104
0   Excel_TBM105
0   Excel_TBM106

'Run PLC_Transfer macro to change the tag value of Excel_TBM103

At 00:23 AM

W   X            
0   Excel_TBM101
0   Excel_TBM101
0   Excel_TBM102
0   Excel_TBM103
0   Excel_TBM104
0   Excel_TBM105
0   Excel_TBM106

'Run PLC_Transfer macro for changing Tag value of Excel_TBM103

Update: I tried using following code to at least check if the code can automatically call my macro so that I can extend it for the column later, but the code doesn't seems to call my macro at all. 

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Target.Worksheet.Range("W23")) Is Nothing Then PLC_Transfer End Sub
San_man
  • 120
  • 1
  • 8
  • [This](http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change) might get you started. – xidgel Aug 23 '15 at 14:29
  • @xidgel I tried that code but nothing seems to work also in the list of macros in the worksheet the macro doesnt show up at all. – San_man Aug 26 '15 at 10:02
  • Can you post the code that's not working and point out where you're getting an error or unexpected behavior? – xidgel Aug 26 '15 at 14:51
  • @xidgel I used the same example given in the link you had suggested earlier with small edits like in the place of H5 I used W25 and in the place of macro I used PLC_Transfer. I thought my workbook events aint triggering so I even set them true still no improvement. I don't get any error but I don't get my macro run automatically either. – San_man Aug 26 '15 at 14:55
  • My test code works after running `Application.EnableEvents = True`. I did not expect to need to do this. – xidgel Aug 26 '15 at 18:40
  • My test code works after running `Application.EnableEvents = True`. I did not expect to need to do this, because according to the Excel VBA help article entitled "Using Events with Excel Objects", worksheet events should always be available. – xidgel Aug 26 '15 at 18:46
  • @xidgel can you say how you tested your code so that I can verify my code that way – San_man Aug 26 '15 at 18:47

1 Answers1

0

First, make sure that Application events are enabled. You can execute this like in the Immediate window:

Application.EnableEvents = True

In the code for Sheet1 (you can place similar code in whichever sheet has your DDE data) I have this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("W5:X25")) Is Nothing Then
        MySub
    End If
End Sub

MySub is a short bit of test code --- you can replace with your PLC_Transfer. Here's my test code:

Public Sub MySub()
    MsgBox "Executing MySub"
End Sub

When I make any change in W5:X25 the message box is displayed.

Hope this helps.

xidgel
  • 3,085
  • 2
  • 13
  • 22
  • it is working when I manually enter the value in those columns but my data is not entered manually. It's a formula which updates those values – San_man Aug 27 '15 at 03:12
  • The Worksheet_Change event fires when you manually change a cell, but not on a recalculation. Can you trigger the Worksheet_Change event on another cell? Do the formulas in you W or X column change because of an manual entry somewhere else? You may also be able to use the Worksheet_Calculate event to trigger your DDEPokes --- it fires on a recalculation. – xidgel Aug 27 '15 at 04:26
  • I understand that and as you said when I manually enter a value in the cells it works well. But my requirement is the cells are calculated from formula which is referred to another cell. Probably I ll share my excel to you so that you can get a clear picture, but wonder how to do that – San_man Aug 27 '15 at 04:47