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

Simple Coin Toss 2 example VBA code


This VBA code block highlights three new concepts:

  • How to read input from a worksheet

  • How to write from VBA code to a worksheet

  • How to translate a worksheet simulation formula into its VBA equivalent

Note how we read input from the worksheet in the first line of the function.  While we could have referenced the cell using A1 notation - i.e., Range("D15"), it's a very good practice to use worksheet range names whenever you're passing data to or from your VBA code.  We'll explain this in more detail later.

Writing the results back to the worksheet is simply a matter of reversing the order of the statement.  The worksheet cell named "HeadsCoinToss2" receives the number of heads we got through tossing the coin the specified number of times.

In our worksheet, the "tails" output cell D24 simply contains the worksheet function "=IterationsCoinToss2 - HeadsCoinToss2"; the calculation is performed in the worksheet rather than by the VBA code.  The percent values in E23 and E24 are also calculated by the worksheet.

Now we'll take a more detailed look at using range names in the interaction between worksheets and VBA.


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.