Page 105 - Microsoft Office Excel 2003 Programming Inside Out

VBA Programming Starter Kit

● element Required object or variant variable that is used to point to each member of
the group. Array loops require a variant variable regardless of the data type of the array.
● group Required collection of objects or array containing the elements that will be
affected by the loop.
● statements One or more optional statement lines that are executed during each iter­
ation of the loop.
● Exit For Optional statement used to exit the loop prematurely. Code execution
resumes at the first line following the Next counter statement.
● Next element Required statement marking the end of the For…Next loop.
The For Each…Next loop is a handy method of performing the same action to a collection of
objects. (You will learn more about object collections and how to work with them in Chapter
6, Chapter 7, and Chapter 8.) If you wanted, for example, to rename all the worksheets in a
workbook, you could use a For Each…Next loop to ask the user for a name for each work-
sheet, rename it, and continue on to the next one until all of the worksheets were renamed.

Sub RenameAllWorksheets()
Dim myWorksheet As Worksheet
Dim strPrompt As String, strResult As String
Dim intCounter As Integer

intCounter = 0
strPrompt = "Please enter the new name for worksheet "
For Each myWorksheet In Application.Worksheets
strResult = InputBox(strPrompt & myWorksheet.Name)
myWorksheet.Name = strResult
intCounter = intCounter + 1
Next myWorksheet
Chapter 4
strPrompt = "Total worksheets renamed =" & Str$(intCounter)
MsgBox strPrompt
End Sub

Logical Loops
Logical loops have no predetermined number of iterations. Instead, they rely on a logical
expression that tests for a particular condition and then repeat the loop until the condition is
either met or cleared, depending upon the type of loop.

Although there are four forms of logical loops, they can be simplified into two styles: those
that test the condition before performing an action and those that test the condition after
performing an action. Within each style, the two loops differ in that one loops when the con­
dition is true and the other loops when the condition is false.
The Do While…Loop and Do Until…Loop both test the condition before performing the
action within the loop. The difference between the two is that Do While loops perform the
action when the condition is true and Do Until loops perform the action while the condition
is false. To decide which one to use, you need to find the simplest way to express the condi­
tion and then pick the loop that will best match the condition.

79
Part 2: Visual Basic for Applications