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 :

 MsgBox "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:

 For n = 1 to 5 
 MsgBox n 
 Next n 

The message box will appear five times showing the values of n from 1 to 5.

The variable used can be anything ‚ although I used n here, it could be a word such as num , but it must be consistent throughout the looping process. You could not use For n = 1 to 5 and then try to use 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 is the default. You can change this behavior by using the Step option. 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 
 Next n 

You will get the results 3, 6, 9, and 12, because it works in increments of 3.

To see how Step works backward, try this example:

 For n= 10 to 1 Step -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 values 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, then all values of m , then the next n value, then 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.

 Sub test_loop() 
 For n = 1 To 4 
 
 For m = 1 To 5 
 
 MsgBox "n= " & n 
 MsgBox "m= " & m 
 Next m 
 Next n 
 End Sub 

For Each Loops

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 the previous example) because it automatically moves through each item within the collection. This is very useful if you need to search through a collection for a certain object and then delete it because the position in the collection after deletion is maintained in your loop. If you use a For..Next loop with an index and delete the object, the index will be moved up one and your routine will go through one loop too 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 oWSheet.Name 
 Next oWSheet 
 End Sub 

Do Until Loops

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 test_do() 
 x = 0 
 Do Until x = 100 
 x = x + 1 
 Loop 
 MsgBox 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 loop should stop. The variable ( x ) is then incremented by 1 each time through the loop, so it loops 100 times until x=100. At this point, it displays a message box giving the value of x that is 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 50 is supplied, and 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 test_exit() 
 
 For x = 1 To 100 
 If x = 50 Then 
 Exit For 
 End If 
 Next x 
 MsgBox 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 Do Until 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 statement Exit Do and Exit For will stop execution of the loop and go onto the next instruction after the end of that loop.