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

Simple Coin Toss - VBA version

ShareThis

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

 

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.