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

LotsaData example - VBA code


Now let's look at the code behind the LotsaData example to demonstrate how to use loops and arrays to write Monte Carlo simulation results into multiple columns of a spreadsheet.

Note that in this and all other examples provided in this tutorial, we limit the number of rows of output to 50,000 and columns to 256.  This is to insure compatibility with older versions of Excel.  If you are using Excel 2007 or later, you can modify the code to allow approximately 1,000,000 rows of output and 16,000 columns.

  • Lines 3 - 4 read user input from the worksheet.

  • Lines 6 - 12 prevent the user from exceeding maximum allowed values.  We'll look more closely at this technique on the next page.

  • Lines 14 - 15 declare two arrays.  These are at the heart of this technique.  BigArray stores all the data from the simulation run, with up to 4 million iterations.  SmallArray will store "chunks" of BigArray, 50,000 records at a time, for transferring data to the worksheet in adjacent columns of 50,000 rows each.

  • Line 17 clears the worksheet of any data remaining from prior runs.

  • Lines 19 - 26 run the simulation iterations.  In this example we're simply storing a random number as the output of the simulation.  Line 23 updates the Progress cell in the worksheet at the specified interval.

  • Line 28 determines how many "panels" (sets of columns) of output will be required.  (In this example, we're outputting two values for each iteration: a counter, and a random number.  Thus, each "panel" of output will require two worksheet columns.)

  • Line 29 adds one more panel of output to capture remaining data if the number of iterations is not divisible by 50,000.

  • The major For loop in lines 31 - 41 transfers blocks of data from BigArray to SmallArray, 50,000 records per sub-loop (lines 33 - 37), and prints them to the worksheet (line 39).


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.