Do Update only Loop, NOT again from start Sub with DDE link

[alessiobdr]

I have cells linked to DDE. They change according to price changes. In order to run the macro, I have following worksheet_calclation.

I have following code:


Private Sub worksheet_calculate()
Call Sub test
End Sub

It works good for running the macro when an price update happens. But I have a loop in the macro. So, when it goes into the loop and there is a price update, the loop probabely goes on, BUT it also calls the Sub from the start again. Thus not just keeping the Loop running until the condition is met.


Following Code (partly, to keep it simple):


Sub test()

If Range("B34").Value = "" Then 'when cell B34 is empty

Do
If Range("B23").Value = Range("B20").Value Then
Range("D20") = Range("B20").Value

If Range("B23”).Value > Range("D20").Value Then
Range("B34").Value = "OK"

Exit Do
End If
End If

DoEvents
Loop
End If
End Sub


Thus:
B23 and B20 are updating via DDE link. When B23 = B20, then it puts that value into D20. Then, when B23 price changes so that B23 > D20, it puts “OK” into B34.

This works very good while pressing F8, manually execution and with live price changes. But as soon as I let it run with worksheet_calculate, it does not put “OK” into B34, when the condition is met. It’s like not staying in the loop, it goes through.
So, that’s why I think, that when a cell updates with a DDE price change, then it calls the Sub from the start again, despite the loop.

I am right? Any suggestions?

I really appreciate a lot for any help. I am really stuck on that one!

Many thanks!

[p45cal]

I'm not sure what triggers sheet recalculation, but I'm fairly sure that a change in value of a cell that is referred to in a formula causes it. In which case lines such as:
Range("D20") = Range("B20").Value

(and Range("B34").Value = "OK" ?)
might trigger a recalculation, which then fires off another run of the event handler before it's finished the first.
You could disable, then re-enable event handling with
Application.EnableEvents=True/False at the beginning and end of either the event handler itself or the macro Test. It stops all such events though.

Another way is to set up a global boolean variable such as StopTestRunning
and have that set and reset at the beginning and end of the macro or event handler. Then at the beginning of the sub you test whether the value is true/false, immediately exiting the sub if it is set. Eg. in the sub Test:
Sub test()
If StopTestRunning Then Exit Sub
StopTestRunning = True
If Range("B34").Value = "" Then 'when cell B34 is empty
Do
If Range("B23").Value = Range("B20").Value Then
Range("D20") = Range("B20").Value
If Range("B23").Value > Range("D20").Value Then
Range("B34").Value = "OK"
Exit Do
End If
End If
DoEvents
Loop
End If
StopTestRunning = False
End Sub I've only answered your query generally, and guessed at what might be producing the signs you describe because I can't easily reproduce your setup here.

[alessiobdr]

I'm not sure what triggers sheet recalculation, but I'm fairly sure that a change in value of a cell that is referred to in a formula causes it. In which case lines such as:
Range("D20") = Range("B20").Value

(and Range("B34").Value = "OK" ?)
might trigger a recalculation, which then fires off another run of the event handler before it's finished the first.
You could disable, then re-enable event handling with
Application.EnableEvents=True/False at the beginning and end of either the event handler itself or the macro Test. It stops all such events though.

Another way is to set up a global boolean variable such as StopTestRunning
and have that set and reset at the beginning and end of the macro or event handler. Then at the beginning of the sub you test whether the value is true/false, immediately exiting the sub if it is set. Eg. in the sub Test:
Sub test()
If StopTestRunning Then Exit Sub
StopTestRunning = True
If Range("B34").Value = "" Then 'when cell B34 is empty
Do
If Range("B23").Value = Range("B20").Value Then
Range("D20") = Range("B20").Value
If Range("B23").Value > Range("D20").Value Then
Range("B34").Value = "OK"
Exit Do
End If
End If
DoEvents
Loop
End If
StopTestRunning = False
End Sub I've only answered your query generally, and guessed at what might be producing the signs you describe because I can't easily reproduce your setup here.

Thanks a lot for your sugestion, p45cal.

I implemented your code, but it does not solve the problem.
it still calls the sub from the top.. and does not keep just the loop looping..