We've now considered how to deal with conditions but, sometimes, you need to go through a portion of code several times to arrive at a certain condition. Performing repetitive tasks that would otherwise drive the user crazy is one of the best programming tricks on offer. You don't want to know if your computer has to check a database with 10,000 entries, adding an extra 1 to every international phone number each time the codes are changed; you only want to know when it's finished.
VBA provides the For ... Next and Do ... Loop statements for this purpose. A loop is a piece of code that is executed repeatedly while or until a certain condition is met. We'll have a look at both these structures.
The For ... Next loop is useful when you know how many times you want to execute the statements within the loop:
For intLoop = 1 To 10 ... Next
This starts the intLoop at 1 and then executes the code between the For and Next statements. When the Next is reached, VBA moves you back to the For statement and adds 1 to intLoop . This continues until intLoop is greater than 10 .
The basic syntax is shown below:
For counter = start To end [Step increment]...Next [counter]
where the terms are defined as:
The variable you assign to the loop
The number with which you wish to start the loop
The number with which to stop the loop
The number you add to start each time round the loop (this is optional, and defaults to 1 if you omit the Step section)
The argument increment can be either positive or negative, allowing loops to count both up and down. (If you leave out the Step and increment part of a For ... Next loop, Access will assume that you just want to increment the value by one each time). For example, to get just the odd numbers you could do this:
For intLoop = 1 To 10 Step 2
Here, instead of adding 1 to intLoop every time the loop is run, VBA adds 2 .
The counter after the Next statement is also optional and is usually left out. This isn't a bad thing but, if you have loops within loops, you may find that using the complete format is clearer, since you can easily see to which loop a Next statement refers. For example, imagine three loops within each other:
For intX = 1 To 10 For intY = 1 To 10 For intZ = 1 To 10 ... Next intZ Next intY Next intX
Here you can see exactly which Next statement belongs to which For statement. If you indent the code as a disciplined programmer (as shown above), even in nested loops it is easy to see which Next statement belongs to which For statement. Ultimately it boils down to personal preference, but do follow a partcular style consistently.
The For ... Next loop is ideal if you know how many times the loop is to be executed. There are occasions, however, when you want to perform loops until a certain condition is met. For those cases, you should use Do ... Loop :
intSpeed = 0 intAcceleration = 5 Do Until intSpeed > 55 intSpeed = intSpeed + intAcceleration Loop
This executes until the variable intSpeed is greater than 55 . If the variable is already greater than 55 , the loop isn't entered and the code isn't executed. There is a second form of this loop, however, which allows you to test the condition at the end of the loop instead of at the beginning:
Do intSpeed = intSpeed + intAcceleration Loop Until intSpeed > 55
This is basically the same as the previous example, but the code in the loop is always executed at least once. Even if intSpeed is greater than 55 when the loop is first started, the code in the loop is still executed once. This may cause errors if intSpeed is being used in other expressions.
You can also replace the Until with While , which allows loops to be performed while a condition is True , rather than until it is True . For example:
Do intSpeed = intSpeed + intAcceleration Loop While intSpeed < 55
This performs the same task as the Until version, but the condition is reversed , because now we want to continue the loop While the expression is True , not Until it is True . Which version you use is up to you, and may depend upon the expression and how easy it is to read.
You should always be careful about the conditions, because you can generate endless loops. This occurs when the loop test never becomes True (for Do Until loops) or never stops being True (for Do While loops). If you suspect that this is happening you can press Ctrl-Break to halt your code.
We have only looked at single control structures so far, but VBA does allow you to nest them ( putting one structure inside another). Here is a trivial function that executes a loop ten times and uses the Mod function (which divides two numbers and returns the remainder) to determine whether the loop counter intLoop is odd or even:
Sub OddEven() Dim intLoop As Integer Dim intMainCount As Integer Dim strOutFinal As String intMainCount = 10 For intLoop = 1 To intMainCount If (intLoop Mod 2 = 0) Then strOutFinal = "Even" Else strOutFinal = "Odd" End If Debug.Print intLoop, strOutFinal Next End Sub
Here, the If structure is nested inside the For ... Next loop. The code for both structures is indented to make it clear where they start and end. There's no limit to the amount of nesting that can be performed, but if you nest too many loops you'll find that your code becomes almost impossible to read (unless you've got a very wide monitor). If you need more than three or four levels of nesting, you should consider restructuring your code - perhaps by creating a new procedure, or maybe just by adjusting your tab width.
The loops that we have looked at so far have all started and stopped at set places. Suppose, though, that because of some action that took place in a loop, you need to exit it straight away, without reaching the condition that normally terminates it. In this case, you can use the Exit structure statement. For example:
For intLoop = -1 To 10 If (intLoop = 6) Then Exit For End If Debug.Print intLoop Next
If the condition on the If line is true, (that is, if intLoop equals 6 ), the Exit For statement immediately exits from the loop, rather than waiting until intLoop is larger than 10.
You can also use the Exit Sub or Exit Function statement to immediately exit a procedure:
If strInName = "" Then Exit Function
This will directly exit the function if strInName is an empty string.
You'll now have a good idea of the power and versatility that a loop can offer. However, loops can also serve another very useful purpose which we will look at now. They can be used to populate arrays .