Page 69 - Excel for Scientists and Engineers: Numerical Methods

46 EXCEL: NUMERICAL METHODS

A One-Dimensional Array
Assigned to a Worksheet Range
Can Cause Problems
Arrays can cause some confusion when you write the array back to a
worksheet by assigning the value of the array to a worksheet range.
VBA considers a one-dimensional array to have the elements of the array in
a row. This can cause problems when you select a range of cells in a column and
assign an array to it, as in the following:
Range("E1 :EIO").Value = TestArray
The preceding statement causes the same value, the first element of the
array, to be entered in all cells in the column. However, if you write the array to
a row of cells instead of a column, e.g.,

Range("E1 :NI").Value = TestArray
each cell of the range will receive the correct array value.
There are at least three ways to "work around" this problem caused by a
"horizontal" array and a "vertical" destination range. One obvious way is to use
a loop to write the elements of the array to individual worksheet cells in a
column.
A second way is to specify both the row and the column dimensions of the
array, so as to make it an array in a column, as illustrated in the Sub procedure
shown in Figure 2- 16.

Sub ArrayDemol()
'Second method to "work around" the row-column problem:
'specify the row and column dimensions.
Dim TestArray( 10, 1 )
statements to populate the array
'Then writes the array elements to cells El :El 0.
Range("E1 :El O").Value = TestArray
End Sub
Figure 2-16. A "work around" for the row-column problem.

A third way is to use the TRANSPOSE worksheet function (Figure 2-17):