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

Modeling normal distributions with the Excel Monte Carlo VBA hybrid approach - SAT example #2

ShareThis

Now we'll demonstrate how to model normally distributed data using VBA code, applying the hybrid VBA - worksheet approach to the same problem we just solved with the worksheet-only approach.

This is the first of two VBA approaches we'll demonstrate.  The current example will use a VBA array to summarize the data, reporting back to the worksheet only the summary histogram data table used for generating our chart.  In the next example that follows, we'll move a step closer towards the minimal VBA approach by writing the raw data to the worksheet, and using the worksheet functionality to generate the histogram data table in the same way we did in the previous example.

As seen below, the input screen for the VBA approach is virtually identical to that of the worksheet-only approach, with the exception that we've added cells for # of iterations and screen refresh interval.

This example illustrates the value of the screen refresh technique.  By visually observing the histogram, we can easily estimate how many iterations are necessary for consistent results.

 

Our Outputs region here too is similar to that of the worksheet only approach, with the obvious exception that it doesn't include the raw data table.  This version also contains the "Iterations Processed" progress indicator.

Now let's look under the hood at the VBA 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.