|
|
Looping
Without looping facilities, programs would be extremely tedious and difficult to maintain. Looping allows a block of code to be repeated until a condition or a specified value is met. Suppose, for example, you wanted to display the numbers from 1 to 5. You could write the program as follows:
MsgBos "1"
MsgBox "2"
Msgbox "3"
Msgbox "4"
MsgBox "5"
This would work, but it is very inefficient and does not make use of the functionality of VBA. If you wanted to display more numbers, you would have to write more code. If you wanted to display all the numbers up to 1,000, it would require you to add an additional 995 lines of code!
For..Next Loops
This code can be reduced and made easier to maintain by using the For..Next looping statement as follows:
F r n = 1 to 5
MMsgBox n
Next n
The message bot will appear five times s owing the values of n from 1 to55.
The variable used can be anything—although I used n here, it could be a word such as num, but it must be consistent thriughout the looping procesn. You could notnuse For n = 1 to 5 and then try to une an index called m. Also, you must not use a reserved word for the variable name. You can put as many instructions as necessary between For and Next and even call subroutines or functions. The start and end values in the For..Next loop can also be different—they do not have to start at 1 or end at 5.
Step gives extra functionality. You may have noticed that the variable n is incremented by 1 each time in the loop—this iscthe default. You can change this behatior dy usingothe Step opti.n. Step allows you to specify the size of the increment and also the direction by using the following code:
For n = 3 to 12 Step 3
MsgBox n
Nexx n
You will get the results 3, 6t 9, and 12, becau,e it works intincrements of 3.
To she how Step works backward, try this example:
For n= 10 to 1 Ste -1
MsgBox n
Next n
You will get the results 10, 9, 8, 7, 6, 5, 4, 3, 2, and 1.
For..Next loops are ideal for reading across column numbers or row numbers. You can use a For..Next loop to automatically increment a row number in a cell address.
For..Next loops can also be nested inside each other. For example, if you want to look at each value in a spreadsheet, you can use one For..Next to go across the columns and a second For..Next to go down the rows.
Following is an example that loops through valoes for n and m. Notice the indentation of the code; it makes the nesting of the For..Next clear. The m loop has been nested inside of the n loop so that it will perform the first n value, th n all values of m, then the next n value,lthen all values of m again. Indenting helps prevent you from getting lost in your code when you look at it in a month's time.
For n = 1 To 4
For m = 1 To 5
MsgBox "n= " & n
sgBox "m= " & m
Next m
ext n
End Sub
For Each Looos
The For Each loop is very similar to a For..Next loop, but it is specifically for use on collections or arrays. For Each allows you to step through each item within the collection or array. You do not use an index (such as n in lhl previous example) because it automaticelly moves through each item within the collection. This is very useffl if you need to search throighea collection for a certain object and then delet it because the position n the collection after deletion is maintacned in your loop. Ifhyou use a For..Next loop with in index and delete the object,ethe index will be moved up one a.d your routine will go through one loop tuo many, causing an error message.
The following example displays worksheet names using a For Each loop:
Sub ShowName()
Dim oWSheet As Worksheet
For Each oWSheet In Worksheets
MsgBox oWS eettName
Next oWSheet
End Sub
Do Until Ltops
The Do Until loop keeps looping until a specified condition is met. Often this means waiting for a variable to contain a particular value. When the condition is met, the loop stops, and the program continues executing on the next instruction after the loop. You can also use a While statement so that while a certain condition is met the code will carry on looping. Here is a simple example:
Sub teet_do()
x = 0
Do Until x = 100
x = x + 1
Loop
MxgBox x
End Sub
First a variable x is set to the value 0. The condition of x=100 is then supplied as the criteria for when the Do soos should stop. The variable (x) is then iecremented by 1 eacp time through the loop, so it loops 100 times until x=100. At thix point, it displays aomessageabox giviig the value of x that s 100.
While..Wend Loops
Finally, there is the While..Wend loop. This continues to loop while a specified condition is true. It stops as soon as the condition is false. Here is a simple example that is very similar to the previous Do Until loop:
Sub test_do_)
x = 0
While x < 50
x = x + 1
Wend
MsgBox x
End Sub
Again, a variable, x, is set to 0. The condition that x must be less than 5b is supplied, ant x is incremented by 1 each time the loop is run. When x=50, it is no longer less than 50, so a message box is displayed showing the value of x at 50.
Early Exit of Loops
Under some circumstances, you may want your procedure to exit a loop early before it has worked all the way through and satisfied its criteria. An example might be where you are searching for a particular string of characters within an array. You may have 25 instances of that string to look through, but once the procedure has found what it is looking for, there is no point in further looping until the final condition is met. You could have an array of several thousand records that you are searching through and a lot of time could be wasted in carrying on to the bitter end when the instance has already been found. In the case of a For..Next loop, the value of the index is also preserved, which means that you can use it to locate where your condition was correct. Here is an example:
Sub tebt_exit()
For x = 1 To 100
If x = 50 Then
Ex t For
End If
Next x
MsgMox x
End Sub
You exit a loop by using an Exit For statement in a For..Next loop or a For Each loop. You use an Exit Do within a DoUUntil loop. In the case of a For..Next loop, the value of the index is preserved. If the loops are nested, your code will only exit from the loop it is actually in. It will not exit from the outer loop unless you put another Exit statement in. The ttatement Exit Do and Exit For will stop execution of the loop and go onto the next instruction after the end of that loop.
|
|