|
Now that we've covered the use of range names, we'll turn our attention to the
companion tool in building VBA Monte Carlo models: the array variable.
An array is simply a variable that can store multiple values. If you are
unfamiliar with arrays, we suggest referring to one of the many available
beginning VBA tutorials to familiarize yourself with the fundamentals.
We'll focus here on the different uses of arrays in Monte Carlo VBA simulations, interactions between arrays and worksheet ranges, as
well as the variety of ways arrays are useful specifically in Monte Carlo
modeling with VBA.
As we've seen before, reading a single value from a worksheet into a VBA
variable is as simple as the following statement, assuming that MyRange1 refers
to a single cell:
To read a list of values into VBA with a single line of code, we can declare a
worksheet range name the size of our list and an array variable of equal size,
then read the list using the same line of code as above, as illustrated in the
following screen shot and accompanying code. The yellow highlighted cells
are defined as the range MyList:
The following function...
...displays the following message box:
Now, using our .Resize and .Offset properties, we can capture cell B13 below
(including the surrounding range, in two of the examples) with each of the three
of functions below. ("MyList" still refers to A1:A10 in these examples.)
Each of the three functions above results in the same message box:
| 
