Microsoft Office Tutorials and References
In Depth Information
Looping blocks of instructions
MsgBox “Good Afternoon”
Case Else
MsgBox “Good Evening”
End Select
End Sub
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 the process of 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 constructs, but I
cover only two of them: For-Next loops and For Each-Next loops:
FOR-NEXT LOOPS
The following listing is an example of a For-Next loop. This procedure displays the
sum of the first 100 integers:
Sub SumIntegers()
total = 0
For num = 1 To 100
total = total + num
Next num
MsgBox total
End Sub
In this example, num (the loop counter variable) starts out with 1 and increases by
1 each time the loop repeats. The loop ends when num is equal to 100. The total
variable simply accumulates the various values of num as it changes during the looping.
You also can use a Step value to skip some values in the loop. Here’s the same
procedure rewritten to sum every other integer between 1 and 100 (that is, 1, 3, 5,
and so on):
Sub SumOddIntegers()
total = 0
For num = 1 To 100 Step 2
total = total + num
Next num
MsgBox total
End Sub
The previous examples use relatively simple loops. But you can have any number
of statements in the loop, and you can even nest For-Next loops inside other
ForNext loops. The following is VBA code that uses nested For-Next loops to initialize
Search JabSto ::




Custom Search