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
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
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
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