Repetition

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.

Loops

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.

For...Next

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:

Important 

For counter = start To end [Step increment]...Next [counter]

where the terms are defined as:

Argument	Description
counter	The variable you assign to the loop
start	The number with which you wish to start the loop
end	The number with which to stop the loop
increment	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.

Do...Loop

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.

Important 

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.

Nested Control Structures

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.

Exiting a Control Structure

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 .