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)
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
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.