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

Weather example VBA code


Below is the code for the weather example.

Lines 3-6 read the input from the worksheet.

Lines 8 - 10 initialize the variables.

The "for" loop beginning on line 16 runs the main iterations of the model.  The "if" block beginning on line 17 sets the value of rain, snow, or clear according to the value of the Rnd() function.

The "if" block beginning on line 26 is triggered each time the refresh interval is reached.  It uses the Mod function (modulo division).  Modulo division returns the remainder after dividing one number by another.  So, for instance, if we wanted to refresh the screen every 50 iterations, we would wait for the total number of iterations to be evenly divisible by 50.  As the program iterates, the function "CurrentIteration Mod Interval" would return the following values:

Iteration 98:

98 Mod 50 = 48
Iteration 99: 99 Mod 50 = 49
Iteration 100: 100 Mod 50 = 0

So when Iteration reaches 100, the formula evaluated by the If statement is TRUE, and the refresh interval code is executed.  The same occurs again at Iterations 150, 200, and so on.

Lines 27 - 30 simply update the output section of the worksheet using methods we've described before.  What's new here is lines 32 - 37.  These cause the program to pause execution for 1/10th of one second and explicitly allow other processes on the computer to execute.  This 1/10th of a second is enough time to allow the worksheet to refresh the bar chart, but is short enough that it only adds a total of 1 second to the overall execution time assuming refresh intervals equal to 1/10 the total number of iterations.  We'll explain that code section in a little more detail below.

Here's the explanation of lines 32 - 37:

  • Line 32 simply declares the two variables used, and is not really an essential line.

  • Line 33 establishes the length of time, in seconds, that the program execution will be paused, and stores that value in the PauseTime variable.

  • Line 34 reads the value of VBA's internal timer and stores it in the variable named Start.

  • The Do Loop in lines 35 - 37 keeps repeating as long as the time lapsed since reading the Timer is less than the established PauseTime.

  • Line 36, "DoEvents", tells the computer it can perform other tasks while the current function is paused.

  • Once the PauseTime has lapsed, the program exits the Loop on line 37, and on line 40 returns to process the next simulation iteration.


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.