alessiobdr
11-19-2009, 05:00 AM
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!
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!