Word Document Properties

Part II: Learning the Ropes 
10_046500 ch05.qxp 12/5/06 5:35 PM Page 128

---

The 
SendKeys
function call comes next. This function is interesting because
it helps you duplicate keystrokes that you normally make in the worksheet
using code. The advantage of this method is that after you get used to the
special characters, you can make writing a program very similar to using the
program normally. This example sends a Shift (
+
), Ctrl (
^
), down-arrow
(
+^{DOWN}
) sequence to the worksheet to select the entire column. The
SendKeys Statement help topic provides full documentation of this function.
You can also find other 
SendKeys
examples in other parts of this book.
The 
SendKeys
function is extremely useful. However, don’t use it in place of
functions that VBA supplies for specific tasks. Using 
SendKeys
can make
your code difficult to read and can also slow execution of the program.
By using the 
ActiveWindow.RangeSelection.Rows.Count
property, the
code determines how many rows it must process. The code places this value
in 
ActiveRows
for later use. Unfortunately, now the worksheet has a range
of rows selected rather than a single cell. The code calls on the
Range(“C5”).Select
method to select a single cell again.
The 
For...Next
statement requires three inputs as a minimum. You must
provide a counter variable so that the statement knows the current count.
The second variable sets the counter to a specific value. This value can be
any integer. The code uses 
5
in this case because that’s the first data row in
the worksheet. The third input is the ending count. 
Counter
equals
ActiveRows + 5
when the loop completes.
The worksheet is ready to receive the first storage room value, so the code
calls 
MakeChoice3
. This is an augmented version of the code in the earlier
“Using the Select Case statement” section of this chapter. 
MakeChoice3
modifies a single cell at a time, so when it returns, only the first data cell has
a storage room value in it. The 
Range(“C” + CStr(Counter)).Select
method call moves the cell pointer to the next storage room cell. The code
continues until all the cells have appropriate storage room numbers.
Using the For Each...Next statement
The 
For Each...Next
statement is similar to the 
For...Next
statement in
operation. However, this statement doesn’t rely on an external counter. The
statement uses an object index as a counter. The advantage of using this
statement is that you don’t have to figure out how many times to perform the
loop — the object provides this information. The disadvantage of using this
statement is that you lose a little control over how the loop executes because
the counter is no longer under your control.
129