To illustrate the screen refresh and real-time results graphing techniques,
we've constructed a simple model called Weather1.
In this simplistic simulation, we input the chance of rain and chance of snow
for any given day. The chance of clear weather is presumed to be 1 - (ChanceRain
+ ChanceSnow). The simulation then generates a random number in each
iteration and determines the current iteration's weather base on where that
random number falls within the probability set of rain, snow, or clear.
As seen in the screenshot below, we now enter not only the desired number of
iterations but also an interval (cell D16) at which we want the Output data to
be updated and the screen to be refreshed. We presently have the formula
"=D15 / 10" in this cell. Thus regardless of how many iterations we run,
the screen will update with every 10% increment towards completion.
Our Outputs section also has a field for "Iterations processed". The VBA
code will write to this cell every time it reaches the screen update interval.
Below that field, in cell D26, we show "Progress". This cell simply
contains the formula "=D25 / D15". (Actually, if you
download the spreadsheet examples, you will see we
use range names rather than A1 cell references, but the effect is the same.)
Cells D28 - D30 contain the results of the run, and the bar graph displays the
results graphically. Now we'll take a look at the code.