Worksheet_Change and DDE

[Kevin S.]

:dunno

Hello Everyone,

Please help me with a task I am trying to figure out. I am reading data from a PLC using DDE and I am placing data into a cell. What I am trying to do is have a macro run in Excel whenever one of the cells go from a "0" to a "1". I am trying to use the Worksheet_Change function however it seems that due to the limitations of the function, my function isn't working. I assume that this is due to the fact that the cell has a DDE link in it and the result of the link is treated as a equation. If this is so please help me figure out how to perform this.

Here is a copy of my code for the Worksheet_Change:

Private Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
target = "$A$2"
Call Capture_data
Application.EnableEvents = True
End Sub
The macro is named Capture_data (fyi).

[Jan Karel Pieterse]

There is a "Application.OnDDE" method you should be able to use, check VBA help.

[CodeNinja]

Kevin,
Tricky... only way I could get this to work was to have a global variable store the old value on change Selection and change event test for the change you want. It looks like this (of course change msgbox("Call here") to calling your sub):



Dim oldVal As Integer
Private Sub Worksheet_Change(ByVal Target As Range)

If oldVal <> 0 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If CInt(Target) = 1 Then MsgBox ("Call here")

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) Then
If Target.Value = 0 Then
oldVal = 0
Else
oldVal = 1
End If
Else
oldVal = 1
End If

End Sub


Only problem is, if you enter 1 from a blank cell it triggers the call... not sure if that is a problem for you, and if it is, anyone smarter than me, please show us how to avoid this annoyance...

Good luck.

[Kevin S.]

First off, Thank you for your replies.

I looked into the Application.OnDDE statement and I am a bit confused by it. t ems as though this command will call an action on another application. My VBA is running within Excel and am trying to invoke the macro based on the DDE information being drawn in. If I am mis understanding this please correct my error.

I also went into my macro for the worksheet_change function and replaced my code with that posted by CodenNinja. I know that my macro called Capture_data runns correctly by it self. However at this point the code doesn't seem to be calling it for some reason. I will enter the code as I have it installed so maybe we can fish out what I am doing wrong.

Dim oldVal As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If oldVal <>0 Then Exit Sub
Target.Range = (A2) <- (not sure if this is necessary)
If Target.Cells.Count > 1 Then Exit Sub
If Cint (A2) =1 Then
Call Capture_data

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Ranger)

If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric (A2) Then
If Target.Value =0 Then
oldVal = 0
Else
oldVal = 1
End If
Else
oldVal =1
End If

End Sub


I am less than a novice when it comes to VBA so please excuse my screw ups where they exist. I have verified that my macro securities are opened up to allow this to run.
It should also be noted that the target cell has this DDE link in it:
=RSLINX|abnormal_cure_data!'S:42/4 (this is looking at a bit in the PLC system timer)
I am wanting to fire the maro when the bit turns to a "1" which will be about 5 secs. and then loop around to fire again the next time it turns to a 1. So on and So on for around 25 mins.

[CodeNinja]

Kevin,
A few things... First, you need to change Ranger to Range (this will really mess up the subroutine as ranger is not a proper data type). Second, Not sure why you want to change target to A2... Are you only wanting this change when the cell is A2? If so, you need an if target.address to check if it is a2, otherwise, get rid of it and go back to if cint(target) = 1.


So, I have the following (just change capture_data subroutine to be the code you want to run)


Dim oldVal As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If oldVal <> 0 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If CInt(Target) = 1 Then
Call capture_data

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) Then
If Target.Value = 0 Then
oldVal = 0
Else
oldVal = 1
End If
Else
oldVal = 1
End If

End Sub


Sub capture_data()
MsgBox ("Test")

End Sub