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

VBA source code for Monte Carlo Excel minimal VBA approach "Bad Day" example

ShareThis

As seen below, the VBA code for the "minimal VBA" approach is simple and straightforward.

Line 3 declares the array which will capture the iteration data.

Lines 7 - 12 make sure the data output will not exceed the limitations of the worksheet, with the limit set to 50,000 to accommodate older versions of Excel.  With Excel 2007 and 2010, this limit can be increased to just over 1,000,000.

Lines 15 and 16 clear any prior data remaining from the previous simulation run.

Lines 18 through 28 run the iterations.  Line 19 recalculates the worksheet; lines 21 - 23 store the outputs ("Bad Day" scores" into the array, and lines 25 - 27 update the progress indicator on the worksheet at the user-specified intervals.

Line 30 outputs the data to the worksheet, and line 31 makes sure the Progress field correctly reflects the final value.



Since this "minimal VBA" approach requires interaction with the worksheet with each iteration, it is generally much slower to execute than the approaches that require less worksheet interaction.  The chief benefit of this approach is that it requires minimal knowledge of VBA to master, and allows the greatest flexibility in modifying the model without updating 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.