|
When you begin writing entire data sets from VBA code to your worksheet, rather
than summarized data only, you can quickly bump into Excel's upper limits on the
number of available columns and rows.
The following table shows the maximum possible number of rows and columns in
Excel versions 2003 (and earlier), and Excel 2007:
| |
Rows |
Columns |
|
Excel 2003 |
65,536 |
256 |
| Excel 2007 |
1,048,576 |
16,834 |
So, if you're simply writing your
data to your worksheet in one row per iteration, your models will be limited
to ~65,000 iterations in Excel 2003, and ~1,000,000 iterations in Excel
2007.
It would be possible to write VBA
code which would insert new worksheets to store the additional data.
However, having data stored across multiple worksheets makes analysis
cumbersome.
The workaround we'll demonstrate
here is to use the range
.Offset property discussed earlier in combination with some "For" loops
to output the data into multiple columns, as seen in the
LotsaData example screenshot below:
Let's look at the VBA code behind
this model.
| 
