Loops are are common feature of most programming languages, and VBA is no exception. Loops enable lines within in a defined section of code to be repeated as many times as is necessary, until a specific point is reached when the program flow may move on to the next section of code. There are different kinds of loops in VBA. The FOR ... NEXT loop, for example, repeats the loop a set number of times. We would use this if we wanted the loop to repeat, say, 10 times, and then move on. With the DO ... UNTIL loop, on the other hand, the defined section of code would repeat indefinitely, until a specified condition arises. This may be something to use if you want to repeat the loop until a user enters a specific value into an input box.
The FOR ... NEXT Loop
Let take a look at how the FOR ... NEXT loop is constructed.
For I = 1 To 10
lines of code to be repeated
The FOR NEXT loop has a counter which is referred to above as I. This is basically a variable name - we could have called it a different name if we had wanted. The first line of the statement (For I = 1 to 10) sets the counter at 1, and tells the procedure to continue repeating the lines of code until the counter reaches 10. Each time the program flow executes the NEXT keyword at the end of the statement, the counter is increased by 1. As it does so, it also checks whether the counter has reached 10 (or whatever level it had been set). If it has not, the lines of code repeat another time until the Next keyword executes again, increasing the count by another 1. Once it reaches the level set, the program flow continues past the NEXT keyword into the next section of code.
So let's try an exercise to see how the FOR ... NEXT loop works in practice.
- Create a new unbound form in Design View.
- Add a Command Button.
- Add the VBA code (listed below) to the button's OnClick Event (please see the first Learning VBA Tutorial if you need help with this, or any of the previous steps):
- Click the SAVE icon.
Dim varNumber As Integer
Dim varResponse As Integer
varNumber = 9 * Rnd() + 1
Do Until varResponse = varNumber
varResponse = InputBox("Enter a Number Between 1 and 10", "Guess A Number")
If varResponse > varNumber Then
MsgBox "Your Guess is too High"
ElseIf varResponse < varNumber Then
MsgBox "Your Guess is too Low"
MsgBox "Congratulations: Your Guess is Correct."
MsgBox "Game Over!!!"