M.22
Extended Learning Module M
LOOPING
The term
looping
refers to repeating a block of statements or code numerous times.
You may know how many times your macro needs to loop, or variables used in your
programs may determine this. There are several looping statements to choose from: the
For-Next
loop, the
Do-While
loop, and the
Do-Until
loop.
FOR-NEXT LOOPS
The simplest type of loop is a
For-Next
loop. Here’s the syntax
for this structure:
For Counter
=
Start To End [Step n]
[statements]
Next [Counter]
The looping is controlled by a counter variable, which starts at one value and stops at
another value. The statements between the
For
statement and the
Next
statement are the
statements that get repeated in the loop.
The following example shows a
For-Next
loop that doesn’t use the optional
Step
value.
This Sub procedure loops 100 times and uses the VBA
Rnd
function to enter a random
number into 100 cells:
Sub FillRange()
Dim Count As Integer
For Count
=
1 To 100
ActiveCell.Offset(Count
−
1, 0)
=
Rnd
Next Count
End Sub
In this example,
Count
(the loop counter variable) starts with a value of 1 and increases
by 1 each time through the loop. Because you didn’t specify a Step value, VBA uses the
default value (which is 1). The
Offset
method uses the value of
Count
as an argument.
The first time through the loop, the procedure enters a number into the active cell offset
by zero rows. The second time through (Count
=
2), the procedure enters a number
into the active cell offset by one row (Count
–
1), and so on.
DO-WHILE
LOOPS
VBA supports another type of looping structure known as a
Do-While
loop. Unlike a
For-Next
loop, a
Do-While
loop continues until a specified con-
dition is met. Here’s the
Do-While
loop syntax:
Do [While condition]
[statements]
Loop
The following example uses a
Do-While
loop within a Sub procedure. This proce-
dure uses the active cell as a starting point and then travels down the column, multiply-
ing each cell’s value by 2. The loop continues until the procedure encounters an empty
cell.
Sub DoWhileExample()
Do While ActiveCell.Value <> Empty
ActiveCell.Value
=
ActiveCell.Value * 2
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Decisions, Decisions, Decisions
M.23
DO-UNTIL
LOOP
The
Do-Until
loop structure is similar to the
Do-While
structure.
The two structures differ in their handling of the tested condition. A macro continues to
execute a
Do-While
loop while the condition remains true. In a
Do-Until
loop, the macro
executes the loop until the condition is true. Here’s the
Do-Until
syntax:
Do [Until condition]
[statements]
Loop
The following example is the same one presented for the
Do-While
loop but recoded
to use a
Do-Until
loop:
Sub DoUntilExample()
Do Until IsEmpty (ActiveCell.Value)
ActiveCell.Value
=
ActiveCell.Value * 2
ActiveCell.Offset(l, 0).Select
Loop
End Sub
M.24
Extended Learning Module M
Wrap It Up
Figure M.11 displays a simple worksheet created to calculate the invoice amount of three
products based on several parameters. The lookup table in cells A5:D7 gives the price
of each product, the discount sales volume (above which a discount will be applied), and
the percent discount for units above the discount volume. Using normal spreadsheet for-
