|
The example PrintMyArray_4() below introduces the function Application.Transpose().
Supplying the name of an array variable as the argument to this function
transposes the rows and columns of the array, as seen in the worksheet to the
right of the code:
There's a problem in using the Application.Transpose() function in many Monte
Carlo applications, though: Transpose() can only handle arrays with up to
5461 elements. Exceeding that number results in the "Type mismatch" error
message below.
Thus, if you're using an array to capture and print to the worksheet the results
of each iteration of a simulation run, you would either have to limit your
number of iterations to 5461 or less, or store the data in rows rather than
columns. The latter solution would limit you to the number of columns
allowed by Excel (256 in Exel 2003 and earlier, and 16,384 in Excel 2007).
Neither of these alternatives may suffice.
An easy workaround is to declare the array as having two dimensions rather than
one, even if only one dimension is required. An array with two dimensions
treats the first dimension as a column rather than a row.
This workaround does consume system resources but is still effective under most
circumstances. Because output to the worksheet can be constrained by
resizing the range, only the desired single column of output can be copied to
the worksheet:
| 
