Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

Precise code order in Excel VB

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:

xxxxx
yyyyy
zzzzz

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!


























0
OptionsMM
Asked:
OptionsMM
2 Solutions
 
JohnMcCannCommented:
Can i suggest

StopTimer

call xxxxx
call yyyyy
call zzzzz


startTimer

0
 
jayeshshahCommented:
what i can suggest is that after X is completed then let the X job itself start the y job on the completion of which would start the z job. In this way you would be able to start the next job only if that job is completed

K'Regards

Jayesh
0
 
CleanupPingCommented:
OptionsMM:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
 
DanRollinsCommented:
Moderator, my recommended disposition is:

    Split points between: JohnMcCann and jayeshshah

DanRollins -- EE database cleanup volunteer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now