PDA

View Full Version : Loop OK manually, but "freezes", when run with DDE update



alessiobdr
11-18-2009, 07:33 AM
Hi , I do have following big problem, for which I have already spent hours trying to find a solution:
I have on a excel spreadsheet cell, which are updated via a DDE link. They do update perfectly.
I have following VBA code Range:

Sub test()

If Range("I23").Value = Range("I19").Value Then
Range("K19") = Range("I19")

Else

Do while Range("I23").Value <> Range("I19").Value

If Range("I23").Value = Range("I19").Value Then
Range("K19") = Range("I19").Value
Exit Do
End If
Loop

End Sub

It basically says: If the first time it runs, it checks if I23 equals I19. If it does it puts the Value of I19 in Cell K19.
If it does not equal, then:
Do loop as long, as the value of I23 = I19. If it is the case, then put the value of I19 into a different cell, here into K19. Then exit.
If I run it manually by holding down F8, in order to run it through as many times as I wish, it works. And depending of the momentaneous Value in the cell I23 and I19 (they change on the DDE link), it puts the accordingly value in K19.
If I let the macro run with a worksheet_calculate (whoch works for subs without a loop), it freezes. The cells in the excel (which are feed by DDE) do not update anymore, nor can I do anything in VBA, the mouse becomes a sandclock.
If I click Ctrl+Break, then the routine halts and the cells are updating again.
What can the problem be? It's like the cells are not updating as long as the loop is running, and because they are not updating the condition never becomes true and therefore never exits the loop? Or is there a logical mistake?
When I halt the routine it Debugs on the line: "If Range("I23").Value = Range("I19").Value

Thank you a lot for any advise! I am completely stuck!

p45cal
11-19-2009, 06:14 AM
see this thread:
http://vbaexpress.com/forum/showthread.php?p=199637#post199637