VBA: Repeating Code with Control Structures

On occasion, you will need to write code that repeats some set of statements. Oftentimes, this will occur when you need to perform some calculation over and over or when you have to apply the same calculations or processing to more than one variable. This section shows you all the control structures you can use in VBScript to control code in your program that repeats.

For…Next

The first structure is often referred to as the For…Next loop. The syntax for this structure is

For counter = start to finish 
   ...code that gets repeated 
Next

For Example the following code will display a message box on the screen displaying the current value of x as it increments from 5 to 50.

For counter = 1 to 10
   x = x + 5
   msgbox x
Next

Do While…Loop

The basic syntax for this structure is

Do While condition 
   ...code within the loop goes here 
Loop

where the condition is either true or false. As long as the condition is true, the code within the loop gets executed. Once the condition becomes false, the loop stops and the code after the loop is executed. The only way for the program to break out of the loop is if the condition becomes false or if an Exit Do statement is encountered somewhere inside the loop. Consider the following example:

Again = True 
DoubleIt = 1 

' Keep doubling the number as long as the user desires 
Do While Again = True 
   ' Show current results and prompt to see if we should continue 
   If MsgBox("Current total is " & DoubleIt & ". Double it again ?", vbYesNo) = vbYes Then 
      DoubleIt = DoubleIt * 2 
   Else 
     Again = False 
   End If 
Loop

In this example, the first line of code sets the variable that gets tested in the loop equal to true. The second line sets the initial value of the variable that gets doubled to one. Setting the loop variable (“Again”) equal to true allows the loop to begin running because the third line says, “If Again is true, enter the loop.” The first statement in the loop displays the result to the user.

Because nothing has been doubled yet, the result is one-the initial value of the DoubleIt variable. When the results are displayed, the user is asked if they want to continue. If they choose to go forward, the variable DoubleIt is multiplied by two. When the program hits the Loop instruction, it will return to the top of the loop and once again check to see if the condition is set to true. Because the condition has not changed, the loop executes again. It will continue to execute until the user chooses not to continue. Once that happens, the variable Again is set to False.

Now, when Loop is reached, the code swings back up to the top of the loop and evaluates the condition once more. This time, the condition is false, so the loop does not execute again. The code moves on beyond the loop.

Do Until…Loop

This is very similar to the Do While…Loop except that rather than perform the operations within the loop while the condition is true, it executes the operations inside the loop until the condition is true. The Do Until…Loop structure takes the form

Do Until condition
   ...code within the loop goes here 
Loop

Contact Us