Precise code order in Excel VB

Posted on 2003-02-26
Medium Priority
Last Modified: 2010-05-01
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!

Question by:OptionsMM
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

JohnMcCann earned 500 total points
ID: 8027189
Can i suggest


call xxxxx
call yyyyy
call zzzzz



Assisted Solution

jayeshshah earned 500 total points
ID: 8027825
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



Expert Comment

ID: 8901067
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?
LVL 49

Expert Comment

ID: 8983445
Moderator, my recommended disposition is:

    Split points between: JohnMcCann and jayeshshah

DanRollins -- EE database cleanup volunteer

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question