|
Now we'll create our first simple VBA Monte Carlo simulation. Remember the
Simple Coin Toss we used as the first example of a worksheet based simulation?
We'll now demonstrate how to develop that same simulation using VBA.
Note: If you'd like to download my Excel workbooks containing all the code
and examples contained in this tutorial (for a small donation), you can
do so here.
In this example, we'll toss a coin ten times, and report how many of the tosses
resulted in "Heads".
In this code, the variable "CountHeads" keeps track of how many heads have been
tossed.
The Rnd() function returns a random number between 0 and 1, inclusive, with each
iteration of the For loop. The Round() function rounds that number to 0 or
1. The value is then assigned to the SingleToss variable.
Treating 1 as "Heads", the CountHeads variable is then incremented up by one for
each time the Round(Rnd(),0) function returns 1.
Finally, after the For loop has resulted in ten tosses, the MsgBox() function
reports back the outcome as illustrated below:
To create this function in your Visual Basic Editor, first select the name of
your Excel file in the left sidebar.
Next, select "Module" from the "Insert" drop-down menu:
This inserts Module1 into your current workbook:
Now, type the code into the code window on the right:
Finally, to run the function, click the "Run" button on the toolbar:
This will run the code, and you should immediately see the message box
displaying how many tosses out of ten were "heads".
| 
