Microsoft Office Tutorials and References

In Depth Information

End Select

End Function

Any number of instructions can be written below each Case statement; they all execute if that case evaluates to

TRUE.

Looping blocks of instructions

Looping
is repeating a block of VBA instructions within a procedure. You may know the number of times to

loop, or it may be determined by the values of variables in your program. VBA offers a number of looping con-

structs:

• For-Next loops

• Do While loops

• Do Until loops

For-Next loops

The following is the syntax for a For-Next loop:

For counter = start To end [Step stepval]

[instructions]

[Exit For]

[instructions]

Next [counter]

The following listing is an example of a For-Next loop that does not use the optional Step value or the optional

Exit For statement. This function accepts two arguments and returns the sum of all integers between (and in-

cluding) the arguments:

Function SUMINTEGERS(first, last)

total = 0

For num = first To last

total = total + num

Next num

SUMINTEGERS = total

End Function

The following formula, for example, returns
55
— the sum of all integers from 1 to 10:

=SUMINTEGERS(1,10)

In this example, num (the loop counter variable) starts out with the same value as the first variable, and in-

creases by 1 each time the loop repeats. The loop ends when num is equal to the last variable. The total variable

simply accumulates the various values of num as it changes during the looping.