Adding Control Statements

You can specify a different value to increment the Counter variable. By default, the Counter variable for the For Next loop increments by one each time the loop executes. If you want to increment or decrement the Counter variable by a different numeric value, you can use the Step statement and specify the increment value. If you specify a positive value, the Counter variable increments by that value each time the loop cycles. If you specify a negative value, the Counter variable decrements by that value each time the loop cycles. In the following example, the For loop starts with an initial counter variable J of 2 and a maximum value of 20. Each time the loop cycles, the counter variable increments by 2. The TotalVal variable increments by the value of the loop. The loop executes ten times. When the initial and maximum values of the counter are equal, the loop executes a final time before it passes control to the next statement outside the loop.

Example:

For J = 2 To 20

Step 2

TotalVal =

TotalVal + J

Next

-Q Type the VBA statements for the body of the loop.

L-0 Type any additional code needed for your subroutine.

□ Switch to Excel and run the associated macro.

■ The macro executes the contents of the For Next loop the specified number of times.

-Q Type the VBA statements for the body of the loop.

Type Next to indicate the end of the loop.

L-0 Type any additional code needed for your subroutine.

□ Switch to Excel and run the associated macro.

■ The macro executes the contents of the For Next loop the specified number of times.

USING THE FOR EACH NEXT LOOP

yo ou can use the For Each Next loop to repeat a series of statements for each element in an array or each object in a collection. When you use the For Each Next loop, the statements you specify between the For and Next statements execute for each element in the specified array or collection. As soon as the looping structure finds the last element, control moves to the next statement outside the loop.

The For Each Next loop consists of four basic parts. The For Each statement initiates the loop. The statement

Element In Group follows the For Next statement.

The body of the loop contains a series of statements to perform for each element. Finally, the Next statement marks the end of the loop.

The Element In Group statement consists of two parts: Element represents a variable of the same data type as the items in the array or collection, and Group names the array or collection. For example, if you want to loop through the elements of an array you can have the statement For Each Student In StudentNames.

The For Each loop continues to execute as long as the specified group contains values. The Element variable contains a copy of a group element each time the loop executes, not a reference to the element in the array. Therefore, changing the Element variable does not modify the array. For example, when dealing with an array of student names, Excel copies the name of the student in the array element to the value specified as the Element. Because of that, changing the value of the Student variable does not modify the contents of the array.

USING THE FOR EACH NEXT LOOP

USING THE FOR EACH NEXT LOOP

—H Create a new subroutine.

'—0 Type a For Next loop to add values to the array.

—H Create a new subroutine.

—0 Type a Dim statement to declare an array, N.

LH Type a Dim statement to declare the count variable for the For Next loop.

□ Type Dim Element as Variant, replacing Element with the variable for the For Each loop.

^0 Declare any additional variables needed by the subroutine.

'—0 Type a For Next loop to add values to the array.

Note: For more information on creating For Next loops, see the section "Execute Tasks a Specific Number of Times. "

0 0

Post a comment