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

Transitioning from worksheet models to VBA

ShareThis

In transitioning from the worksheet approach to VBA, you'll notice a few key differences in syntax between native worksheet functions and VBA.

Most notably, the random number generator function which is at the heart of Monte Carlo simulation is "rand()" in worksheets is "rnd()" in VBA.

There are a few basic building blocks of Monte Carlo simulations in VBA as well.  We'll cover them here, but if you're new to VBA you might want to study these in more detail.  The looping functions - FOR loops, WHILE loops, etc. - are at the heart of VBA Monte Carlo simulations as we generally use a loop function to run the iterations.  You'll also want to be familiar with arrays, since we use them extensively for storing data from the iterations.

You'll also find several commonly used worksheet functions do not have a VBA equivalent.  Two of the most commonly used in Monte Carlo are the worksheet functions min() and max().  Another function we haven't yet covered in the tutorial - but we're getting there - is norminv(), used in modeling normally distributed variables.  This function too is missing from VBA.

Fortunately VBA has a syntax which allows you to use any worksheet function in your VBA code.  The expression is:

 

application.worksheetfunction.[function name]([arguments])

 

For example, to calculate the minimum of 3 and 5, you would write the following line of code:

 

MyMin = application.worksheetfunction.min(3, 5)

 

Now we'll cover a few steps necessary for preparing to work with Visual Basic in Excel.  If you are already familiar with accessing the Visual Basic Editor and enabling macros through your Excel security settings, and if you already have the Analysis ToolPaks installed, you can skip the next few pages and go directly to the first Simple Coin Toss example.

 

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.