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

VBA source code for modeling normal distributions in Excel Monte Carlo simulations

ShareThis

The VBA source code for the SAT2 example is provided below.

Note that VBA does not have a native equivalent of the worksheet function NormInv(), so we are required to use the

Application.WorksheetFunction(NormInv(...))

construct.  The same applies in this example to the min() and max() functions.

Line 12 generates the raw SAT score in a way similar to the worksheet function used in the previous example.  The exception here is that we do not first round it to the nearest 10 before rounding again to the nearest 100 for purposes of building our histogram - that step would be redundant.

Line 13 performs the rounding, assigning the score to the bracket that it will belong to in the output.



The array SAT_Histogram stores the cumulative results, and is finally printed back to the worksheet at intervals (line 19) and at the end of the program (line 32).

The rest of the function uses concepts we've already covered and should be easy to follow.

 

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.