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

Writing VBA arrays to worksheets - problems and workarounds


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:


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.