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