Microsoft Office Tutorials and References
In Depth Information
Copying Data Between Ranges and Arrays
All of the information within a workbook is easily available for manipulation through a VBA
macro. Why would you want to opy that information to someplae else before working with
it? Speed. It’s a ery time-onsuming process for VBA to ead or write information to a
worksheet. By minimizing the number of times that VBA needs to read or write to the worksheet,
you can greatly reduce the amount of time needed for your procedure to operate.
How can you reduce the number of read and writes to the worksheet? By reading or writing
a range of cells at a time. It’s the setup time that VBA needs to access a worksheet that takes
time. Unfortunately, VBA goes through the same setup process every time it needs to read or
write another range, whether the range consists of only one cell or several hundred cells.
Warning As with most things, there is a point of diminishing returns or outright failure.
VBA cannot transfer more than about 3000 cells at one time. As long as you stay well below
that number, you should have no problems.
So the question now becomes how can you read or write to multiple cells at one time?
Transferring multiple cells between a workbook and VBA is done through the use of variant arrays.
A variant data type can hold any other data type, including arrays. More importantly, the
variant variable does not have to be declared as an array to hold an array. In fact, for the
purposes of copying data to and from a worksheet, the variant variable cannot be declared as an
array. The variable needs to be declared as a simple variant type, as in the following statement.
Dim varA As Variant
Reading the cells into a variant variable is done through an assignment statement, that is,
varA = Range("January1PM") or varA = Range("H6:H36") . These two examples would
read the same range into the variable varA , with the individual cells accessed the same as a
single element of an array.
assgned values to
the vaiant, you can use the
the ange. For example, you could
use this ode to
dis lay a message box
containing just the sum of the values in cells E12:E15.
Dim varA As Variant, intSum As Integer
varA = Range("E12:E15")
intSum = WorksheetFunction.Sum(varA)
Note A range will always return a multi-dimensional array, usually two dimensions,
regardless of the number of columns. The second dimension will contain at least one ele
ment but could have more depending upon the number of columns within the range.
If you need to determine the size of an array, you can use the LBound and UBound functions
to give you the lower and upper boundaries of the array. LBound(var) returns the lower
boundary, usually a 0 or 1, whereas UBound(var) will return the upper boundary.