worksheet change vs calculate for DDE Excel (trading system)

[travmont]

Summary of problem:
"worksheet_change" doesn't acknowledge changes in my cells that get their data from a broker through DDE. They told me "worksheet_calculate" could do the job, but how and what else may work?


Details:

I have developed an excel sheet that takes live data from the broker and uses it to calculate Support and Resistance on two cells, which should change as the data from the broker changes.

In order to calculate Support and Resistance (D2 and D4) my sheet uses C2 and C4 (today's High & Low) and I thought I told my vb code (through worksheet_change) that if it saw any changes in those two cells within the time C3 (US market open, CET), it would have to change D2 and D4, automatically.

It does it only if I paste different values on C2 and C4 - not if such values get changed by the source live data (from Interactive Brokers). How do I make the sheet react to the data changing other than by pasting it each time on the cell?

Other system traders told me that "worksheet_change" doesn't do the job, and that "worksheet_calculate" would do it, except that I don't know exactly how.

I posted the file here in case anyone wants to help me modify it:

http://www.elitetrader.com/vb/showthread.php?s=&postid=975544

 

[Joe4]

There is a really good write-up on all the event procedures here:
http://www.cpearson.com/excel/events.htm

If you search this site, you should be able to find a lot of examples of these different event procedures in action. Here is one that you may find interesting, as it has a few different event procedures in it:
http://www.mrexcel.com/board2/viewtopic.php?t=184775

 

[travmont]

Thank you for your reply. I am going through the links but I am pretty illiterate so I may not solve it still.

In case you or anyone else finds time to download and modify (it won't take long) my file, here is the link again:

http://www.elitetrader.com/vb/attachment.php?s=&postid=975652

 

[travmont]

See here for evolution of problem. So far it's been a nightmare and it hasn't been solved:

http://www.elitetrader.com/vb/showthread.php?s=&threadid=63684

 

[travmont]

Problem Solved

It is a miracle! I had the right answer from a friend on an Italian forum. This time it seems to work perfectly. If you add to the top of my file this code, then everything works and it doesn't freeze:

Dim valori(1) As Double

Private Sub Worksheet_Calculate()
On Error Resume Next

If Cells(2, 3).Value + Cells(4, 3).Value = valori(0) + valori(1) Then Exit Sub
valori(0) = Cells(2, 3).Value
valori(1) = Cells(4, 3).Value

---------

Essentially, this code tells the sub routine "calculation" to not calculate unless there are changes on those two famous cells.