We have already covered an introduction to looping and the simplest type of loops, namely the For…Next Loop and the For Each…Next Loop, in previous tutorials. We discovered that the For…Next Loop and the For…Each Next Loop are utilized when the statements in the code, need to be repeated for a finite number of times. We are now going to look at how to use the Do While Loop in Excel VBA.
Excel VBA Do While Loop Example
The Do While loop repeats a statement whenever a condition becomes true. The statement is executed every time in a loop whenever the condition is found true. When a false result is found while performing the test the loop stops.
We will begin with a short example, where we will see how this Do While loop in Excel VBA actually works.
- Let’s put some numbers in a column.
- Now press on the Visual Basic under the Developer option. If there is any code written in the new sheet, erase that and copy the following code there.
Sub loopexample() Dim i As Integer i = 1 Do While Cells(i, 1).Value <> "" Cells(i, 2).Value = Cells(i, 1).Value - 5 i = i + 1 Loop End Sub
- Run the code and you will see the following result.
Here we can see the second column contains some numbers and those are the subtracted result from each row of the 1st column. The condition (Cells(i, 2).Value = Cells(i, 1).Value – 5) checks each cell of the 1st column. After the 9th row, there is no number for the rest of the 1st column the second column stops executing numbers.
You can follow the link below for more examples regarding the Do While Loop in Excel VBA.
Do While Loop in Excel VBA (Using a Command Button)
We have a worksheet with a list of items sold in one column. We would like to use a Do While Loop in order to evaluate the list. If the item sold was Apples in the Items Sold column, then we would like the actual cell containing the text Apples to receive a blue italicized font.
In the same way, if the item sold was cherries then we would like a dark pink italicized font and for oranges, we would like an orange italicized font. For every fruit with the different color italicized font, we would like the cell containing fruits to be double underlined. In the next column which states the Category, we would like to see the fruit’s name written just beside the fruits which have been sold.
If the cell at hand in the Items Sold column, has a vegetable listed, then we would like no formatting to be applied, and the cell next to it in the category column to receive a value of vegetables.
Now, let’s begin the Do While loop for the specified formatting that we discussed above.
The source data is shown below.
1) So, first things first go to Developer>Controls>Insert, and under the ActiveX Controls section, choose Text Box as shown below.
2) Draw a TextBox on the worksheet.
3) Using the Properties Window, name the TextBox, txtresultDoWhileLoop.
4) Now, go to Developer>Controls>Insert and under the ActiveX Controls section, choose Command Button and draw a button on the worksheet.
5) Using the Properties Window, name the Command Button, cmdsubmitResult and change the caption to Submit.
6) Right-click the button, you just created and select View Code.
7) Enter the following code for the button click event.
Private Sub cmdsubmitResult_Click() Application.ScreenUpdating = False Dim valueinText As String Dim itemSold As String valueinText = txtresultDoWhileLoop.Text If valueinText = "Populate" Then Range("A5").Select Do While ActiveCell.Value <> "" itemSold = ActiveCell.Value If itemSold = "Apples" Then ActiveCell.Font.Color = RGB(0, 114, 230) ActiveCell.Font.Italic = True ActiveCell.Font.Underline = xlUnderlineStyleDouble ActiveCell.Offset(0, 1).Value = "Fruit" ElseIf itemSold = "Cherries" Then ActiveCell.Font.Color = RGB(237, 55, 124) ActiveCell.Font.Italic = True ActiveCell.Font.Underline = xlUnderlineStyleDouble ActiveCell.Offset(0, 1).Value = "Fruit" ElseIf itemSold = "Oranges" Then ActiveCell.Font.Color = RGB(222, 148, 16) ActiveCell.Font.Italic = True ActiveCell.Font.Underline = xlUnderlineStyleDouble ActiveCell.Offset(0, 1).Value = "Fruit" Else ActiveCell.Offset(0, 1).Value = "Vegetables" End If ActiveCell.Offset(1, 0).Select Loop ElseIf valueinText = "Clear" Then Range("B5:B550").ClearContents Range("A5:A550").ClearFormats End If End Sub
8) Return to the worksheet and make sure Design Mode is not activated, enter the text Populate in the TextBox, on the worksheet as shown below.
9) Click on the Submit button in order to format the text in the Items Sold column and populate the Category column, simultaneously with the categories specified, in the code as shown below.
10) Then type Clear in the text box on the worksheet and click on the Submit button.
The Category column is now cleared of all the values, and the Items Sold column is cleared of all the specific formatting. And there you have it.
The Code Description of the Do While Loop in Excel VBA
The first thing we are doing in the code is turning off Screen Updating, this ensures that the screen does not flicker while the code is running.
We then declared two variables of the string data type. The first variable is called valueinText and the second variable is called itemSold. valueinText gets its value from the text input in the TextBox we created on the worksheet.
Then we enter a conditional logic block with the If statement. Using the If statement we check if the valueinText value is Populate.
If the user has entered the word Populate in the text box, then the first thing that needs to be done is that the cell A5 in the worksheet needs to be selected. This is the first cell containing data under the column heading Items Sold.
We then open the Loop Structure with the Do While ActiveCell.Value<>””.
Note: This is a very important line since if it is omitted, we would basically be creating what is known as an infinite loop. This line says that looping should occur as long as the active cell is not blank.
We then state that the second variable we declared itemSold will take on the value of whatever the active cell value is.
We then open the If & Else If options for conditional logic statement (for the fruits and vegetables) within the actual loop, which will have to be evaluated as part of the loop.
We end the If statement within the actual loop with an End if.
We then specify that we need to move on to the cell immediately below the former active cell once the statements have been evaluated and executed, using this line of code: ActiveCell.Offset(1, 0).Select.
We end the loop, and then we go back to the initial If statement and declare that if the value in the TextBox on the worksheet is Clear, then clear the contents in the category column and clear all the formatting in the Items sold column. We then end the overall conditional logic block with an end if statement.
Download Working File
The Do While Loop in Excel VBA is utilized when one wants to repeat statements or actions, as long as a certain specified condition evaluates to true. It is slightly more complex than the For…Next Loop Structure and the For…Each Next Loop Structure, however, it is more versatile and can be used when one is not sure of the number of times exactly that the statements in the loop have to be repeated.
Please feel free to tell us if you use the Do While Loop structure in your VBA code.
Review Section: Test your Understanding
1) When does one use a Do While Loop in Excel VBA?
2) What is an infinite loop?
3) How does one exit an infinite loop?
4) Using a Do While Loop, fill each cell in a range containing data, with a blue fill, up until one reaches a blank cell.