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

Multi Coin Toss example VBA code


Below is the code behind the VBA version of the Multi Coin Toss simulation.

Note that we do not retain the independent results of each iteration of the model.  This would consume large system resources for high numbers of iterations.  Instead, in each iteration we are simply summarizing and storing the cumulative results in the HeadCount(11) array.

The array has 11 elements, since there are 11 possible outcomes of tossing a coin ten times (zero to ten heads).

The code uses "nested loops" - one "for" loop for the number of iterations, and another "for" loop nested within the first one to toss the coin 10 times.

At the beginning of each iteration we reset the number of heads ("NumHeads") to 0.  Then we toss the coin ten times.  Each time we get heads we increase NumHeads by 1.  After tossing the coin ten times, we update the HeadCount array accordingly.  For example, if our previous iterations have resulted in 55 sets in which we got heads seven times, the the line of code "HeadCount(NumHeads) = HeadCount(NumHeads) + 1" will update the HeadCount(7) array element to hold the value 56.

The final line of code before End Function stores the output in the OccurencesMultiToss1 range of the worksheet.  On the next pages we'll explain more about copying arrays to worksheet ranges, the Transpose property used in this line of code, a major limitation of the Transpose property, and a workaround.


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.