Page 212 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

To determine the boundaries of all dimensions in a multi-dimensional array, specify
the dimension you want the boundary of. For example, using the statement varA =
Range("JanuaryAllValues") in the Y2001ByMonth.xls workbook would read all the
cells from B3:Q38. To find the number of rows, you could use UBound(varA, 1) or
simply UBound(varA). Retrieving the number of columns would use the command
UBound(varA, 2).
When reading the values from a named range, you can also shrink or expand the number of
cells being read by using the Resize method. As an example, to read only the first seven rows
of data, but three hours worth of data from 1:00 P.M. in the Y2001ByQuarter.xls workbook,
you could use the following statement:

varA = Range("January1PM").Resize(7, 3)
Chapter 8
Writing information back into the range is a simple reversal of the statement,
Range("January1PM") = varA. However, some care should be taken when writing
information back into the cells. You should ensure that the range is the same size as the
array to prevent overwriting the wrong cells. This is to preserve data integrity and is easily
done by combining the UBound function with the Resize method, as follows:

Range("January1PM").Resize(UBound(varA, 1), UBound(varA, 2)) = varA.

A simpler method of ensuring the integrity of your data is to fill the array with the values
from the range first. This method will actually serve two purposes: it will size the array to
match the range, and it will fill the array with the current values contained within the range
so that when the data is written back Excel will maintain the values that have not been
changed within the procedure.
The following procedure helps illustrate the difference in processing times between using
direct access to the cells and copying the cells to an array first. (Each method is repeated 50
times so that the time needed to perform the reads is high enough to measure.)

Sub ProcessTime()
Dim rgeCells As Range, intCount As Integer
Dim strPrompt As String, strTitle As String
Dim sglStart As Single, sglEnd As Single
Dim rgeCell As Range, varCells As Variant
Dim intRows As Integer, intColumns As Integer
Dim intLoop As Integer

intCount = 0
strTitle = "Microsoft Office Excel 2003 Programming Inside Out"
sglStart = Timer
Debug.Print sglStart
For intLoop = 1 To 50
For Each rgeCell In Range("JanuaryAllValues")
intCount = intCount + 1
Next rgeCell

186