LotsaData example - VBA code

ShareThis

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