Do Loop

For loops repeat a fixed number of times that is known in advance. For example, For count = 1 to 10 will loop exactly 10 times. This is known as definite iteration. What happens when we need a program to loop without knowing the number of repetitions in advance? For example, suppose we wanted to create a loop that asked a sales person to input the number of sales during a day. If an unacceptable value, such as a negative number, was input then we would want the program to repeat the prompt until acceptable data was input. The problem is that we don't know in advance how many times it will take before they input an acceptable value. This is known as indefinite iteration. The Do ... Loop structures allow you to write code that loops an unspecified number of times.

The basic idea about a Do .Loop is that the code in the loop will execute as many times as needed Until the test is true, or While the test is true. There are four variations of Do .Loops in VBA, which have slight differences in the way they work.

Do Until. Loop

Do Until condition statements Loop

Example:

Do Until sales > 0

Sales =InputBox (Prompt:="Please input a positive number ") Loop

The condition is applied before entry so the loop may never execute. With an Until condition the statements are executed until the condition is true. In the example, the loop will repeat until the user inputs a value >0.

Do While .Loop

Do While condition statements

Loop

Example:

Sales =InputBox (Prompt:="Please input a positive number ") Loop

The condition is again applied before entry so the loop may never execute. With a While condition, the statements are executed while the condition is true. In this example, the condition has been negated to achieve the same effect as in Do Until...Loop.

Do...Loop Until

Do statements Loop Until condition

Example:

Sales =InputBox (Prompt:="Please input a positive number ") Loop Until sales > 0

The condition is applied after the statements and so at least one entry to the loop is guaranteed. Here, the InputBox statement will execute at least once. During design stage, think about whether you want this to happen.

Do .Loop While

Do statements Loop While condition

Example:

Sales =InputBox (Prompt:="Please input a positive number ") Loop While sales < 0

The condition is again applied after the statements and so at least one entry to the loop is guaranteed. This is the same problem as in Do . Loop Until except that because we are using While, we need to negate the condition to achieve the same effect.

0 0

Post a comment