|
When your simulation model prints the entire iteration dataset to your worksheet
as in the previous two examples (FastSlow
and LotsaData), you'll want
to limit the number of possible iterations to the space available for output.
Since Excel 2003 and earlier allow a maximum of 65,536 rows per worksheet, I
generally set the limit of iterations per column or panel of data output to
50,000 when using older versions of Excel.
In Excel 2007, the row limit per worksheet was increased to 1,048,576, so when
using this version and later I typically use 1,000,000 iterations as the limit
per column or panel of data output. (These limits apply only when
outputting the full data to the worksheet; otherwise, as seen is some of the
earlier examples, several million iterations executing in a matter of seconds is
not uncommon.) Using the multiple panels of output method illustrated in
the LotsaData example can enable higher numbers of total iterations despite the
row limitations, but it is still important to make sure the total number of
iterations chosen by the user does not exceed the capabilities of the program.
Thus I often include at the beginning of my function a code block that checks
the number of requested iterations, and resets it to the maximum allowable if
the requested number is higher than that. The following code block from
the LotsaData example illustrates:
In this case, we determined that 4 million iterations was the rounded upper
limit of the program, as follows:
-
We wanted compatibility with older versions of
Excel, so 50,000 rows of output per panel was the limit.
-
Each panel would contain three columns: one for
the Counter, another for the Random Number, and another to leave a column of
white space between data sets. Since Excel 2003 allows a maximum of
256 columns, we could have a maximum of 256 / 3 = 85 panels of input.
-
85 panels x 50,000 rows per panel allows us a
total of 4,266,666 iterations. We rounded this down to 4 million for
simplicity.
Note that we overwrite the worksheet values in lines
7 - 8, in addition to updating the variables in the VBA code in lines 9 - 10.
We also notify the user via a message box (line 11) that the number of
iterations has been reset.
All of the examples in this tutorial are designed for compatibility with older
version of Excel and are therefore limited to 50,000 rows of data output and 256
columns.
| 
