Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
On my system, using the loop method to fill a 1000 x 250–cell range (250,000 cells) took 10.05
seconds. The array transfer method took only 00.18 seconds to generate the same results —
more than 50 times faster! The moral of this story? If you need to transfer large amounts of data
to a worksheet, avoid looping whenever possible.
The timing results are highly dependent on the presence of formulas. Generally, you’ll
get faster transfer times if no workbooks are open that contain formulas, or if you set
the calculation mode to Manual.
A workbook that contains the WriteReadRange , LoopFillRange , and
ArrayFillRange procedures is available on the companion CD-ROM. The file is named
loop vs array fill range.xlsm .
Transferring one-dimensional arrays
The example in the preceding section involves a two-dimensional array, which works out nicely
for row-and-column-based worksheets.
When transferring a one-dimensional array to a range, the range must be horizontal — that is,
one row with multiple columns. If you need the data in a vertical range instead, you must first
transpose the array to make it vertical. You can use Excel’s TRANSPOSE function to do this. The
following example transfers a 100-element array to a vertical worksheet range (A1:A100):
Range(“A1:A100”).Value = Application.WorksheetFunction.Transpose(MyArray)
Excel’s TRANSPOSE function doesn’t work with arrays that exceed 65,536 elements.
Transferring a range to a variant array
This section discusses yet another way to work with worksheet data in VBA. The following
example transfers a range of cells to a two-dimensional variant array. Then message boxes display the
upper bounds for each dimension of the variant array.
Dim x As Variant
x = Range(“A1:L600”).Value
MsgBox UBound(x, 1)
MsgBox UBound(x, 2)