How to Use Do While Loop in Excel VBA

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 Structure.

The Do While Loop Structure is utilized when the loop needs to be repeated for an indefinite number of times, it is not fixed by a counter variable like the For…Next Loop is, nor is it determined by the number of objects in a collection like the For Each…Next Loop is. The Do While Loop very simply repeats a statement or action, while a certain condition specified is true, so an element of conditional logic comes into play here. It repeats the statement or action, while the specified condition is true and until the condition evaluates to false.

Let’s look at a simple example illustrating how to use the Do While Loop In VBA.

Using the Do While Loop in Excel VBA

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 italicised font, with a double underline and the cell next to it, in the category column, to get a value of fruit. If the item sold was Cherries in the Items Sold column, then we would like the actual cell to receive a dark pink italicised font, with a double underline and the cell next to it in the category column to get a value of fruit. If the item sold was Oranges in the Items Sold column, then we would like the actual cell to receive an orange italicised font, with a double underline and the cell next to it in the category column to get a value of fruit.  Otherwise, 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.

We can thus use a Do While Loop, to help us apply the specified formatting in the Items Sold Column and to populate the Category column with the needed values.

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 Text Box on the worksheet.

3) Using the Properties Window, name the Text Box, 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

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. This is especially useful for loops where repetitive actions are being looped through and thus if screen updating is not turned off the screen would flicker consistently as the loop is evaluated.

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 Text Box we created on the worksheet. We then enter into a conditional logic bloc with the If statement. Using the If statement we check if the valueinText value is Populate, in other words, has the user entered the word Populate in the Text Box on the worksheet.

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<>””. 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 (i.e the statements within the Do While Loop should be repeated) as long as the active cell is not blank. This is part of what is required for a Do While Loop as well, since when we use a Do While Loop, we are saying an action or set of statements should be repeated as long as the condition (which in this case is that the active cell is not blank) is true, once the condition is false the repetition stops.

Click on the image to see a larger version

An infinite loop is a loop that just goes on executing the repetitive statements without stopping. If for some reason, one has accidentally constructed an infinite loop, one can press Ctrl-Alt-Break on the keyboard, in order to stop the program from executing. 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 an If conditional logic statement within the actual loop, that will have to be evaluated as part of the loop. If itemSold or the active cell, in other words, is Apples then a dark blue font color will be set, the font will be italicised and the text will be double underlined. In addition, the cell in the column next to the Apples text will receive a value of Fruit, which ActiveCell.Offset(0, 1).Value = “Fruit” specifies.

So in this way we are formatting the values in the Items Sold column and also populating the Category column on the worksheet.  If the active cell value is Cherries, then a dark pink font color will be set, the font will be italicised and the text will be double underlined. In this case, as well the cell, in the column next to the Cherries text will receive a value of Fruit. If the active cell value is Oranges, then an orange font color will be set, the font will be italicised and the text will be double underlined. In addition, the cell in the column next to the Oranges text will receive a value of Fruit. In all other cases, no formatting will be applied to the actual cell, the only thing that will happen is that the cell next to the active cell will receive a value of vegetables. 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. So, in other words, the new active cell, is the cell in the row immediately below the former active cell. Then the loop executes the statements using the new active cell.

We end the loop, and then we go back to the initial If statement and declare that if the value in the Text Box 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.8) Return to the worksheet and

8) Return to the worksheet and making sure Design Mode is not activated, enter the text Populate in the Text Box, 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.

Download Working File

DoWhileLoopExcelVBA

Conclusion

The Do While Loop Structure 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 a 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?

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.

Useful Links

Do While Loop

VBA Loops

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

Leave a Reply

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

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.