Precise code order in Excel VB
Posted on 2003-02-26
Hi I am working on a options risk management system. I have all my positions and theoretical inputs stored in an access database. There are a total of 50 stocks with options positions. I pull the risk information out of the database and do about 70,000 calcuations (option model functions) in excel. The excel is supposed to talk to a trading application through DDE and then updates the database in real time (well it updates every few seconds). IMPORTANT: Auto Calculation must be turned OFF the whole time because if it is on it will recalculate all 70,000 formulas every tick of the stock. (Unacceptable). I have a 3.06 HT processor on a P4PE board. The options calculations are simply too intense. So I need to manually calculate each part of the spreadsheet one portion at a time, do database queries only after certain sections of the spreadsheet have been recalculated, and pull in DDE at certain times as well.
The problem that I am having is that the code to do all of this is sticky, as in sometimes it is fast, sometimes it is slow, and sometimes it doesn't execute in the order I need it to. Sometimes it skips ahead.
I have been trying to use a timer:
Application.OnTime Now + TimeValue("00:00:02"), "QueryDatabase""
Application.OnTime Now + TimeValue("00:00:04"), DDEUpdate"
however it doesn't work all the time the way i want to because it sometimes doesn't finish one task and moves to the next one.
What is the best way to code something so that if i write:
The computer executes x and only x, and only when x is finished it executes y and only y, and only when y is finished it executes z IN THAT ORDER, everytime.
Thanks for your help!