Word Document Properties

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

---

Using the For...Next statement
The 
For...Next
statement is very handy for performing a task a specific
number of times. As long as you can determine how many times to do some-
thing in advance, this is the best looping option to use because there’s less
chance of creating an infinite loop. You can create absurdly large loops, but
they eventually end.
The 
Sub
created in the earlier section “Using the Select Case statement” and
augmented in other areas of this chapter works fine for determining the stor-
age room for a single entry. Figure 5-2 shows multiple entries, however, so
creating a method for processing all of them would make it easier to assign
storage rooms to a new worksheet. The code shown in Listing 5-8 does just
that — it makes changing all rows possible with a single program execution.
(You can find the source code for this example on the Dummies.com site at
http://www.dummies.com/go/vbafd5e
.)
Figure 5-3:
Create
programs
that help
you perform
repetitive
tasks,
such as
formatting
text.
127
Chapter 5: Creating Structured Programs
10_046500 ch05.qxp 12/5/06 5:35 PM Page 127

---

Listing 5-8
Changing Datasheets with the For...Next Statement
Public Sub ChangeAllRooms()
Dim ActiveRows As Integer ‘ Number of active rows.
Dim Counter As Integer ‘ Current row in process.
‘ Select the first data cell in the worksheet.
Range(“A5”).Select
‘ Use SendKeys to select all of the cells in the
‘ column.
SendKeys “+^{DOWN}”, True
‘ Get the number of rows to process.
ActiveRows = ActiveWindow.RangeSelection.Rows.Count
‘ Reset the cell pointer.
Range(“C5”).Select
‘ Keep processing the cells until complete.
For Counter = 5 To ActiveRows + 5
‘ Call the Sub created to change a single cell.
MakeChoice3
‘ Move to the next cell.
Range(“C” + CStr(Counter)).Select
Next
End Sub
The 
SendKeys
function works with the active window. Unlike with many of
the other examples in this book, you must run this example from the Excel
worksheet. Make sure that the Excel worksheet is open, open the Macro dialog
box by clicking Macros on the Ribbon or choosing Tools
➪
Macro
➪
Macros,
highlight ChangeAllRooms, and click Run. If you run this particular macro
from within the Visual Basic Editor, you’ll experience an error.
Of course, the need to run the macro in the active window begs the question
of how to debug a macro that has this particular problem. You’ll run into it
relatively often when a macro has to perform tasks with the active window
but could just as easily perform the task in the Visual Basic Editor. You need
to use two techniques in this case. First, never set breakpoints on the
SendKeys
function. Second, use message boxes as often as possible to
report the content of variables and potential errors.
This example shows several new functions that you can use. The code begins
by selecting the first data row in the worksheet. This selection is important
because you want to start at the very beginning of the entries.
128