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! This would not be very efficient.
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. If you changed the line "Next n" with "Next m," you would get an error because it does not match your original loop. 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 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 a multidimensional array
and a second For..Next to go down the multidimensional array.
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 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