VBA Do While Loop and Do Until Loop

The diagram of Do While Loop

The While loop … Wend , which we met in the previous chapter is the simplest loop in the Excel VBA. The loop that gives us more options is Do … Loop. It gives us many more possibilities. We can, for example, finish the instructions earlier, by entering the exit condition. Our control over the execution of instructions is much greater. Below is the basic flowchart of Do … Loop:

 

 

Loop types Do … Loop

Loops To … Loop can be divided into 5 categories. I have an exact description of them in the table below.

The type of the loop Description
Do While Loop Loop with entry condition. The Do While Loop loop runs for the first time when the condition is met. This means “recreate the instruction when the condition is met”.
Until Loop Loop with the condition of exit. The loop is activated for the first time and works until the condition is met. This means “recreate the instruction until the condition is met”. When the condition is met, the loop exits
To Loop While Loop with entry condition. The loop is activated and executed once, after which it is checked whether the instruction can enter the loop once again. When the condition is met, the loop will start again.
To Loop Until Loop with the condition of exit. The loop is activated and executed once, after which it is checked whether the instruction can enter the loop once again. When the condition is met, the loop will exit.
To Loop Loop without a specified condition. We can stop it, eg with the help of the Exit Loop instruction and the If Then Else statement.

We can terminate our loop before the condition is met. The Exit Do manual is used for this . We use it, for example, if we want an additional condition to finish the loops. A condition that we did not write after While or Until .

An example of a loop in a loop

Let’s try to make a simple multiplication table in our VBA. We will use the Do Until Loop and Do loop Until loops we have learned for this purpose . First, let’s create a simple loop that fills 10 columns of the first row in our sheet:

 

In the above example, we intentionally introduced both variables for columns and rows at once . We will use them fully ending our example. Once we have the macro filling the first row, we loop it, increasing the counter for the rows by 1 each time. In this way, a multiplication table up to 100 will be created in our sheet.

An example of using loops

In the example with the use of a loop, we will create a simple database of people. After creating the database, we will deal with the data analysis using the previously known functions and capabilities of VBA. For starters, let’s create a simple table, in which our columns will be: Order number, index number, date of the exam, exam result, verbal assessment. Additionally, the sequence number column should be filled in with numbers from 1 to 30.

In this way we have obtained a simple table to which we can enter data. Next, using the function of generating random numbers, modify our script so that:

  • The Index number column was filled with random numbers between 90000 and 100000
  • The date of the exam column was filled with the date of today – 10 days
  • The result of the exam contained random numbers from 1 to 100
  • The data contained in the Verbal evaluation column should refer to the results as follows: results from 0 to 33 are given the “correction” value. Values ​​from 34 to 67 receive the value “Sent.” Values ​​greater than 67 receive the value “Pass with distinction”.

Random numbers are created using the RND () function . We can generate random numbers from 0 to 1 in this way. The number can be converted into integers using the INT () function . These functions are described in more detail in the article on the mathematical functions of VBA . To work !

 

I put the file with the solution solution below:

 

Tasks (You can enter the solution in a comment)

  1. Create a multiplication table using the Do While Loop loop.
  2. Using a loop, list all even numbers from 0 to 100 on the sheet.
  3. Use the loops to list all numbers between 0 and 100, divisible by 4.
  4. Modify the example with the people database by adding a color in column 5. Depending on the result of the exam, the result of the pass corresponds to yellow, the result is the red color. For the result he passed with distinction assign green color.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: