Last Updated on January 11, 2023 by token
The diagram of Do While Loop
The While…Wend loop, which you learned in the previous chapter is the simplest loop in the Excel VBA. The loop that gives you more options is Do Loop. It gives you more possibilities. You can, for example, finish the instructions earlier, by entering the exit condition. Your control over the execution of instructions is much greater. Below is the basic scheme of Do…Loop statement:
Do [Condition type] [Conditions] [Code to execute] Loop
VBA Do Loop types – Do While and Do Until
Do Loop can be group in 5 categories. Description of them you can see in this table:
| The type of the loop | Description |
| Do While [conditions] [code] Loop | Loop with entry condition. The Do While Loop loop runs for the first time when the condition is True. It means “run the code when the condition is True”. |
| Do Until [conditions] [code] Loop | Loop with the exit condition. The loop is activated for the first time and works until the condition is True. It means “run the code until the condition is True”. When the condition is True, the loop exits |
| Do [code] Loop While [conditions] | 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 True, the loop will start again. |
| Do Loop [code] Until [conditions] | 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. |
| Do Loop | Loop without a specified condition. You can stop it, ie. using the Exit Loop instruction and the If Then Else statement. |
You can terminate loop before the condition is True. The Exit Do statement is used for this. You use it, for example, if you want to put an additional condition to finish the loop. A condition that you didn’t write after While or Until.
Do While Loop and Do Until Loop Examples
Let’s try to create a simple table with VBA code. We will use the Do While Loop, Do Until Loop and Do loop Until loops we’ve learned for before. First, let’s create a simple loop that fills 10 columns of the first row in our sheet using Do While Loop:
Sub LoopExample1() Dim intRow As Integer Dim intCol As Integer intRow = 1 intCol = 1 Do While intCol <= 10 Cells(intRow, intCol) = intCol * intRow intCol = intCol + 1 Loop End Sub
You can do the same operation using Do Until Loop:
Sub LoopExample2() Dim intRow As Integer Dim intCol As Integer intRow = 1 intCol = 1 Do Cells(intRow, intCol) = intCol * intRow intCol = intCol + 1 Loop Until intCol = 10 End Sub
VBA – An example of a loop in another loop
In the examples, we intentionally introduced both variables for columns and rows. 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 your sheet.
Sub LoopExample3() Dim intRow As Integer Dim intCol As Integer intRow = 1 Do Until intRow > 10 intCol = 1 Do Cells(intRow, intCol) = intCol * intRow intCol = intCol + 1 Loop Until intCol > 10 intRow = intRow + 1 Loop End Sub
How to create simple table with data using Do While Loop or Do Until Loop?
In the example, you will create a simple table of people data. After creating the table, 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 columns will be:
- Order number,
- student number,
- exam date,
- exam result,
- verbal result.
Additionally, the sequence number column (order number) should be filled in with numbers from 1 to 30.
Sub LoopExample() Dim intCounter As Integer Dim intColumn As Integer Dim intRow As Integer 'colmn names Cells(1, 1) = "Order nubmer" Cells(1, 2) = "Student number" Cells(1, 3) = "Exam date" Cells(1, 4) = "Exam result" Cells(1, 5) = "Verbal result" 'order numbers from 1 to 30 intCounter = 1 Do While intCounter <= 30 Cells(intRow + 2, 1) = intCounter intCounter = intCounter + 1 intRow = intRow + 1 Loop End Sub
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 fill with random numbers between 90000 and 100000
- The date of the exam column fill 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 result column should refer to the results as follows: results from 0 to 33 are given the “Failed” value. Values from 34 to 67 receive the value “Pass.” Values greater than 67 receive the value “Pass with good result”.
Random numbers are created using the VBA RND function . We can generate random numbers from 0 to 1 in this way. The number can be converted into integers and round using the VBA INT function . These functions are described in more detail in the article on the VBA mathematical functions.
Sub LoopExample5() Dim intCounter As Integer Dim intColumn As Integer Dim intRow As Integer 'column names Cells(1, 1) = "Order nubmer" Cells(1, 2) = "Student number" Cells(1, 3) = "Exam date" Cells(1, 4) = "Exam result" Cells(1, 5) = "Verbal result" 'order numbers from 1 to 30 intCounter = 1 Do While intCounter <= 30 Cells(intRow + 2, 1) = intCounter intCounter = intCounter + 1 intRow = intRow + 1 Loop 'student number using RND function intCounter = 1 'set counter 1 intRow = 0 'set row counter 0 Do While intCounter <= 30 Cells(intRow + 2, 2) = Int(90000 + Rnd() * 10000) 'random value from 90k to 100k intCounter = intCounter + 1 intRow = intRow + 1 Loop 'exam date column intCounter = 1 'set counter 1 intRow = 0 'set row counter 0 Do While intCounter <= 30 Cells(intRow + 2, 3) = Date - 10 ' dates in 3rd column intCounter = intCounter + 1 intRow = intRow + 1 Loop 'exam result column intCounter = 1 'set counter 1 intRow = 0 'set row counter 0 Do While intCounter <= 30 Cells(intRow + 2, 4) = Int(Rnd() * 100) 'multiplication random value by 100 and round intCounter = intCounter + 1 intRow = intRow + 1 Loop 'verbal result - result in words Dim StrResult As String 'var for result in words intCounter = 1 'set counter 1 intRow = 0 'set row counter 0 Do While intCounter <= 30 'Verbal result If Cells(intRow + 2, 4) <= 33 Then StrResult = "Failed" ElseIf Cells(intRow + 2, 4) > 33 And Cells(intRow + 2, 4) <= 67 Then StrResult = "Passed" ElseIf Cells(intRow + 2, 4) > 67 Then StrResult = "Passed with good result" End If Cells(intRow + 2, 5) = StrResult intCounter = intCounter + 1 intRow = intRow + 1 Loop End Sub
The file with code:
Tasks (You can enter the solution in a comment)
- Create a multiplication table using the Do While Loop.
- Using a loop, list all even numbers from 0 to 100 in the sheet.
- Use the loops to list all numbers between 0 and 100, divisible by 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 corresponds to failed will be red. For the result he passed good result assign green color.
