Vba - calculation function problem with dde data

QuestionEdit

, I have a vba macro that takes the value of cell (frequently updated by DDE from a program called metatrader 4)and copy it to a specific position in a table. This macro do it every minute with a loop and timer functions. I have no problems with these functions. It works. My problem is that the cells feed by DDE dont have their values updated by the vba calculation function. I've tried calculation, calculationfull, sendkeys method, linksources update, but nothing worked. In a simple macro with only calculation function, the dde cell value gets updated. I think that the loop + calculation + dde is the problem. Do you have any tip for me?

AnswerEdit

Andre,

You don't show your macro so I can only speak in generalities, but within the loop put in a

doevents

statement

this allows control to return to the parent program so it can do the internal processing it needs to do.

You might try putting it in in several places until it works as you want.

You also might look at Chip Pearson's page on using a timer http://www.cpearson.com/excel/ontime.aspx

there is also an event tied to a DDE update. SetLinkOnData. You use this to specify a macro to run for a specific DDE Link update. In my limited experience with it, it runs before the cell with the DDE link has its value updated, so you need to throw in a Doevents at the top or put in an

Application.Calculate

at the top. It has been quite a while since I played with this, but I recall that was a problem. Supposedly the Change event is supposed to fire when a DDE link updates. I have seen people having problems with that, but you might look into it. (the thought is that you tie an event to the update and you don't have to run a tight loop that updates every minute - only when something happens).