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

Use of array variables in VBA Monte Carlo simulations


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:


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.