.jpg "Microsoft Certified Professional")
IX - Program Loops
Sometimes, when writing a procedure, you might want to perform a certain operation repeatedly, such as if you had a group of records or other objects on which you wanted to carry out the same tasks or if you wanted to give the user a set number of chances to enter the correct password. Languages like Visual Basic for Applications (VBA) include keywords that enable an operation to run in a loop for a given number of times. In this section, I'll explain two of these structures in VBA.
FOR ... NEXT
You would use these keywords if you knew how many times you wanted an operation to run or if it could be determined through a formula. In VBA, it uses a counter variable to track which iteration of the loop is currently running. Here's the basic syntax and an example:
For <Counter> = <Start Value> to <End Value>
<actions>
Next <Counter>
Dim Properties(1 To 10, 1 To 5) as Variant
Dim Counter as Integer
For Counter = 1 to 10
Properties(Counter,1) = Counter
Next Counter
The above example creates a Properties array with 10 rows and 5 columns. It then uses the For ... Next loop to count from one to ten. For each loop, it populates the matching position in the array's first column with the current value of the Counter variable, effectively numbering the rows in the array. In this way the Counter variable can be used for other things in addition to regulating the loop.
It is not absolutely necessary to specify the Counter variable in the Next statement at the end of the loop but it does make the code easier to read. For ... Next loops can also be nested just like If ... Then statements and if you were to do this, you would want to specify which loop you were closing.
The For...Next loop can count forwards and backwards as needed, i.e.
For Counter = 10 to 1
As I said earlier, you can also use a variable or formula to specify the start and end values for the counter variable. The above example also could have been written this way:
For Counter = 1 to UBound(Properties, 1)
This would start at one and set the end value to the upper boundary of the Properties array's first dimension.
For...Next loops can also count forwards and backwards by values other than one by using the Step keyword on the first line:
For Counter = 0 to 500 Step 5
For Year = 2000 to 1900 Step -10
If needed, you can also exit from a For loop before the counter reaches its end value as shown in the following function that accepts a full path reference and returns the file name.
Public Function GetFileName(PathReference As String) As String
'Declare and set variables
Dim PathLength As Integer
Dim Counter As Integer
Dim NameOfFile As String, FileChar As String
NameOfFile = ""
PathLength = Len(PathReference)
'Parse PathReference and return file name at the end.
For Counter = PathLength To 1 Step -1
FileChar = Mid(PathReference, Counter, 1)
If FileChar <> "\" Then
NameOfFile = FileChar & NameOfFile
Else
Exit For
End If
Next Counter
GetFileName = NameOfFile
End Function
This function uses the For...Next loop to count its way backward through the PathReference string passed into the function, using the length of the string in characters as the starting value of the For loop. It looks at each character and so long as that character is not "\", the function adds the character to the beginning of the NameOfFile string that it's building. If the character does equal "\", the code exits the loop. Either way, after the end of the loop, the code returns the NameOfFile variable as the result.
There are often many ways to do
something in VBA and you can often find ways to optimize the code. As
it stands, the code above uses more variables and steps than necessary so let's
try something different.
Dim PathLength As Integer
Dim Counter As Integer
Dim NameOfFile As String
PathLength = Len(PathReference)
For Counter = PathLength To 1 Step -1
If Mid(PathReference, Counter, 1) = "\" Then
NameOfFile = Right(PathReference, (PathLength - Counter))
Exit For
End If
Next Counter
GetFileName = NameOfFile
This change eliminates one of the variables and the string concatenation steps.
The Mid() function which inspects a character at a specified location in the
string now uses a positive test rather than a negative test to check for the "\"
character. If it finds it, everything after that point is treated as the
file name by the Right() function and the loop exits. Otherwise, it keeps
going.
FOR EACH ... NEXT
The For Each loop is useful when you want to iterate through a collection of objects or an array without regard to the number. The statement is limited to items that can be treated as a collection such as forms and array elements and you will probably not use it much until you are more familiar with the concepts of object-oriented programming. Here's a simple example that demonstrates the syntax with an array:
Dim BusinessTypes(25) as String
Dim Business as String
For Each Business in BusinessTypes
<statements>
Exit For
<statements>
Next Business
The above is a stripped down example of the statement which is very similar to the For...Next loop of the previous section. You can also use the Exit For statement to get out of a For Each statement if a condition is met.
Another use for the For...Each statement is with objects in a collection. The below example references the controls collection on a form which includes text boxes, command buttons, check boxes and all the other elements of the form that are used to work with data.
Dim ctrl As
Control
Dim btnCommand As CommandButton
For Each ctrl In Me.Controls
If ctrl.ControlType = acCommandButton Then
Set btnCommand = ctrl
btnCommand.Enabled = False
End If
Next ctrl
This example iterates through all the controls on the form, selects those that are command buttons and disables them one by one. This might be useful if you wanted to lock down a form under certain conditions.
For another example of the For ... Each loop, refer back to the section on procedures which includes example code of For ... Each being used to iterate through all the available forms in an Access application.
Do Loops
While the loops explained in the last section were based on numbered iterations or collections of items, the Do Loop repeats a section of code based on whether a condition is True or False. This removes the necessity for declaring counter or object variables but it also introduces a certain amount of risk. When creating a conditional loop, you must be certain that the condition you specify in the loop will actually enable the loop to exit at some point. Otherwise, you will have an endless loop that causes the program to become unresponsive or crash.
There are two types of Do loops; Do While and Do Until. The first would repeat an action while a certain condition is met and the second until a condition is reached.
To try the following example, type the method and function shown below into a new module in VBA, position the cursor within the TestPrime method, press CTRL-G to show the Immediate window (or select Immediate Window from the View menu) and press F5 to run the code.
Public Sub TestPrime()
Dim TestValue as Long
TestValue = 1
'Test every value up to 10000 to
'determine if it's a prime number.
Do While TestValue <= 10000
If IsPrime(TestValue) Then
Debug.Print TestValue
EndIf
TestValue = TestValue + 1
Loop
End Sub
Public Function IsPrime(Candidate As Long) As Boolean
'Determine if the number passed in is a prime number.
Dim TestLimit As Long, TestValue As Long
'Assume it is prime until proven otherwise.
IsPrime = True
'If the number is higher than 3, test against every
integer up to
'the square root of the number.
If Candidate > 3 Then
TestLimit = Int(Sqr(Candidate))
For TestValue = 2 To TestLimit
If Candidate Mod TestValue = 0 Then
IsPrime = False
Exit For
End If
Next
Else
IsPrime = False
End If
End Function
After you run the program a couple times, I would recommend that you use this code to get familiar with the Access help system. Click on a function such as Int, Sqr or Mod and press F1. If the help files are properly installed on your machine, you should see more information on these functions.
In the TestPrime() method, the code uses a Do While loop in order to test all values up to 10,000 to find prime numbers. If the IsPrime function returns a True result, the code prints the value in the Immediate window in the VBA environment which is used to display information while debugging the code. (If you don't see this window, press CTRL-G to show it or select it from the View menu in VBA.) This window is scrollable so you'll be able to see the entire list of prime numbers that result.
After the If ... Then statement, the code increments the number to be tested and loops back to run the code again with the new value. Updating the condition being tested or using one that changes automatically is also an important part of using Do loops as testing the same condition over and over again would result in an endless loop and a hung program.
"Insanity: doing the same thing over and over again and expecting different results." - Albert Einstein
To show the flexibility of the Do loop, the statement in the TestPrime() method could be written multiple ways:
Do
TestValue = TestValue + 1
Loop While TestValue <= 10000
In this variation, the looping decision is placed at the end of the loop, after the incrementing of the variable. The decision of whether to test the condition at the beginning or end of the loop depends on the actions being performed in the loop and how the condition might change between the Do and the Loop.
Do Until TestValue >= 10000
TestValue = TestValue + 1
Loop
Do
TestValue = TestValue + 1
Loop Until TestValue >= 10000
In the Do Until / Loop Until variations, the code tests the changing condition until it reaches a certain state. Again, it's important to test for a condition that actually can occur or else you'll have an endless loop. Where I'm incrementing by 1 in these examples, I'm comfortable that the value will actually hit the exact value of 10000 but for the sake of form and good habits, I've changed the formula to greater than or equal to 10000
Public Sub Pause(Seconds as Integer)
Dim CurrentTime As Date
CurrentTime = Now()
Debug.Print CurrentTime
Do Until Now >= DateAdd("s", Seconds, CurrentTime)
DoEvents
Loop
Debug.Print Now()
End Sub
The example above is just what the name implies; a method that pauses the execution of code for the requested number of seconds. It could be inserted before carrying out an action. It uses the Now() function from VBA to assign the current date and time to a date variable and then uses a Do Until loop to loop until the Now function returns a value greater than or equal to the specified number of seconds after the time that was stored. The method uses Debug.Print to show the start and end times. The DoEvents command allows any background tasks such as screen refreshes to complete while this loop is running. Without it, the loop would bog down your system.
You can also use the Exit Do command to exit a Do loop before it finds the specified condition.
Do Until <condition>
If <condition> Then
Exit Do
End If
Loop
While ... Wend
A simpler form of While loop is While ... Wend which loops until a condition is found. This is an older syntax that has even more potential for endless loops and is not as flexible as Do loops.
Public Sub Pause(SpecTime as Date)
While Now() < SpecTime
DoEvents
Wend
