This test works in most cases, and would have worked in the previous example, had it been used.
However, problems can occur if you are testing cells that contain formulas that can produce zero-length
strings, such as the following:
The zero-length string test does not distinguish between an empty cell and a zero-length string resulting
from a formula. It is better practice to use the VBAIsEmpty function when testing for an empty cell.
Tr ansferr ing Values between
Ar rays and Ranges
If you want to process all the data values in a range, it is much more efficient to assign the values to a
VBA array and process the array rather than process the Range object itself. You can then assign the
array back to the range.
You can assign the values in a range to an array very easily, as follows:
vSalesData = Range(“A2:F10000”).Value
The transfer is very fast compared with stepping through the cells one at a time. Note that this is quite
different from creating an object variable referring to the range using:
Set rngSalesData = Range(“A2:F10000”)
When you assign range values to a variable such as vSalesData, the variable must have a Variant
data type. VBA copies all the values in the range to the variable, creating an array with two dimensions.
The first dimension represents the rows and the second dimension represents the columns, so you can
access the values by their row and column numbers in the array. To assign the value in the first row and
second column of the array to sCustomer, use:
sCustomer = vSalesData(1, 2)
When the values in a range are assigned to a Variant, the indexes of the array that is created are always
one-based, not zero-based, regardless of the OptionBasesetting in the declarations section of the mod-
ule. Also, the array always has two dimensions, even if the range has only one row or one column. This
preserves the inherent column and row structure of the worksheet in the array and is an advantage
when you write the array back to the worksheet.
For example, if you assign the values in A1:A10 to vSalesData, the first element is vSalesData(1,1)
and the last element is vSalesData(10,1). If you assign the values in A1:E1 to vSalesData, the first
element is vSalesData(1,1) and the last element is vSalesData(1,5).
You might want a macro that sums all the Revenues for Kee in the previous example. The following
macro uses the traditional method to directly test and sum the range of data:
Chapter 4: Using Ranges