Page 211 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

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 copy that information to someplace else before working with
it? Speed. It’s a very time-consuming process for VBA to read or write information to a work-
sheet. 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
Chapter 8
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? Trans-
ferring 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 pur-
poses 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. Once you have assigned values to the varant, you can use the vari-
i
able as a reference to the range. For example, you could use this code to display a message box
containing just the sum of the values in cells E12:E15.

Sub VariantSum()
Dim varA As Variant, intSum As Integer
varA = Range("E12:E15")
intSum = WorksheetFunction.Sum(varA)
MsgBox (intSum)
End Sub

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.

185