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

Screen refresh & graph updating example: Weather1

ShareThis

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.

 

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.