|
In order to illustrate concepts of writing arrays to worksheet ranges, we'll use
a worksheet with the range name MyData referring to the single cell A1:
We'll also use a declared array variable MyArray, which in this first example
has a single dimension with five elements (0 to 4). In the function
PrintMyArray_1() below, even though the array elements are assigned the values
0, 1, 2, 3, and 4, only the first element (0) is transferred to the worksheet:
 
This is because the range MyData is only 1 x 1 cells in size. So, the
lesson here is that regardless of the size of the array, the output to the
worksheet will be limited to the size of the range to which data is transferred.
The rest of the array output is truncated.
The logical assumption if we wish to write all five array elements into range
A1:A5 of the worksheet is that we can resize the range MyData through VBA code
as follows:
 
The disappointing surprise is that we get a column of zeros rather than the
expected 0, 1, 2, 3, 4. What went wrong?
The answer is that single-dimension arrays are stored as rows, not columns.
Thus if we resize the range MyData as in PrintMyArray_3() below to be five
columns wide rather than five rows high, all five elements of the array are
copied to the worksheet in row 1.
As it turns out, the example PrintMyArray_2() above was simply printing the
first element of the array five times.
 
On the next page we'll demonstrate how to transpose the array so that we can
print the output to the worksheet in column format.
| 
