power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Screen refresh and real-time graphing of simulations while processing


Complex simulations with many iterations can take a long time to run.  In some applications it is even desirable to let the simulation run for many hours, calculating millions of iterations of a complex model with many inputs and variables.

In these situations it can be impossible to know if the program has hung up or is still processing normally.  It may also be desirable to see incremental results as iterations click by.  Here we will demonstrate some techniques for updating the screen, showing progress towards completion of the run, as well as showing preliminary results (numerical and graphical) while the simulation is running.

This can also be useful in determining how many iterations the simulation needs.  If you have specified 10 million iterations but find that the outcome does not vary after the 1 millionth iteration, you may decide that 1 million iterations are enough.  The complexity of the model will determine how many iterations are necessary for consistent results.

There are two basic techniques we will apply here:

  • Establishing an interval (# of iterations) at which we will update the worksheet with current status.  Current status can be as simple as the # of iterations processed so far, or can include a full update of all the output cells in the worksheet to let us monitor preliminary results.

  • Programming momentary pauses into the VBA code, freeing system resources to update graphs or other calculations within the worksheet that may otherwise not update due to the resource-intensity of the executing VBA code.

We'll demonstrate both of these in the example that follows.


Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.