How to use the DO LOOP statement
The Do Loop statement allows you to repeat specific code until a condition is met.
1. Introduction
What is VBA?
VBA (Visual Basic for Applications) is a programming language and development environment that is integrated into various Microsoft Office applications, such as Excel, Word, PowerPoint, Access, and Outlook. It allows users to automate tasks, create custom functions, and develop sophisticated applications within the context of these Office programs.
Do I need VBA?
The latest version of Microsoft Excel, Excel 365, has introduced new built-in functions that allow users to create loops and recursive functions directly within the spreadsheet, without the need for VBA (Visual Basic for Applications) programming.
These new Excel functions provide a more native and user-friendly way to implement iterative and recursive logic which were previously only achievable through VBA code. This can be particularly useful for users who want to perform complex calculations or automate repetitive tasks without having to delve into the complexities of VBA.
This article: How to replace part of formula in all cells provides examples that demonstrate a recursive LAMBDA function. You can find more recursive LAMBDA functions here.
2. Syntax
There are several possible ways you can use Do Loop with the While statement, here is the first one.
Statement Syntax 1
DO WHILE [condition]
[CODE]
LOOP
As you can see the While statement is after the Do statement. This makes it possible to avoid the Do Loop altogether if the condition is not met.
Statement Syntax 2
DO
[CODE]
LOOPÂ WHILE [condition]
You can also use the While statement after the Loop statement, this makes the macro go through the Do Loop statement at least one time even if the condition is not met.
Statement Syntax 3
DO WHILE [condition]
IF [condition] THEN EXIT DO
[CODE]
LOOP
The IF [condition] THEN EXIT DO statement within a DO WHILE loop in VBA is useful when you want to provide an additional condition to exit the loop, beyond the main loop condition.
This can be useful in situations where you need to check for a specific condition that should cause the loop to terminate, even if the main loop condition is still true. For example, you might have a loop that iterates through a range of cells, but you want to exit the loop if you encounter a specific value in one of the cells.
By using the IF [condition] THEN EXIT DO statement, you can add an additional layer of control to your loop, allowing you to exit it more precisely when a specific condition is met, without having to restructure the entire loop logic.
Statement Syntax 4
DOÂ UNTIL [condition]
[CODE]
LOOP
The DO UNTIL [condition] loop is useful when you want to run a block of code repeatedly until a specific condition is met. This can be helpful in scenarios where you need to perform an action or retrieve data until a certain condition is satisfied such as waiting for user input, checking for the existence of a file, or processing data until a specific value on the worksheet is found.
3. Example 1 - Do While
The macro below, demonstrated in above picture, concatenates values from cell range B2:B6 as long as variable r is smaller than 7. Cell range B2:B6 contains A, B, C, D, and E.
The macro displays a message box containing the values in cells B2 to B. It has an OK button that allows you to dismiss the message box after reading it.
Sub Macro1() ' Initialize row counter r = 1 ' Start a loop that continues while r is less than 6 Do While r < 6 ' Concatenate the value of the current cell to the result string result = result & Range("B2:B6").Cells(r, 1) ' Increment the row counter r = r + 1 Loop ' Display the final result in a message box MsgBox result End Sub
The condition is at the beginning of the DO ... LOOP which makes the it skip the statements all together if the condition is not met.
4. Example 2 - Loop While
The macro below, demonstrated in above picture, concatenates values from cell range B2:B6 as long as variable r is smaller than 7. Cell range B2:B6 contains A, B, C, D, and E.
The macro displays a message box containing the values in cells B2 to B. It has an OK button that allows you to dismiss the message box after reading it.
Sub Macro2() ' Initialize row counter r = 1 ' Start a loop Do ' Concatenate the value of the current cell to the result string result = result & Range("B2:B6").Cells(r, 1) ' Increment the row counter r = r + 1 'Continue the loop as long as r is less than 6 Loop While r < 6 ' Display the final result in a message box MsgBox result End Sub
The condition is at the end of the DO ... LOOP which may go through the statements at least one time even if the condition is not met.
5. Example 3 - Exit Do
The Exit Do allows you to stop a Do Loop before the condition is met. The macro then continues with the line right below Loop, if there is one.
Statement Syntax
DO WHILE [condition]
IF [condition] THEN EXIT DO
[CODE]
LOOP
The following macro stops the Do Loop if cell value is empty, it then continues with the line just under Loop which is Messagebox.
This is why the message box displays only two concatenated values on the picture above, the third cell is empty.
Sub Macro3() ' Initialize the row index to 1 r = 1 ' Start a loop that continues until the row index is less than 6 Do While r < 6 ' Check if the cell in the current row is empty If Range("B2:B6").Cells(r, 1) = "" Then Exit Do ' Concatenate the value in the current cell to the result variable result = result & Range("B2:B6").Cells(r, 1) ' Increment the row index by 1 r = r + 1 Loop ' Display the result in a message box MsgBox result End Sub
6. Example 4 - Do Until Loop
The While statement allows you to iterate through the Do Loop while the condition is true.
The Until statement runs the code until the condition is met.
Statement Syntax
DOÂ UNTIL [condition]
[CODE]
LOOP
If the Until condition is not met the entire do loop structure is ignored, the macro continues with the row below Loop.
Example macro
Sub Macro4() ' Initialize the row index to 1 r = 1 ' Start a loop that continues until the row index is equal to 6 Do Until r = 6 ' Concatenate the value in the current cell to the result variable result = result & Range("B2:B6").Cells(r, 1) ' Increment the row index r = r + 1 Loop ' Display the result in a message box MsgBox result End Sub
7. Example 5 - Loop Until
Statement Syntax 2
DO
[CODE]
LOOPÂ UNTIL [condition]
In this case the code between Do and Loop is rund at least once.
Example macro 2
Sub Macro5() ' Initialize the row index to 1 r = 1 'Start loop Do ' Concatenate the value in the current cell to the result variable result = result & Range("B2:B6").Cells(r, 1) ' Increment the row index by 1 r = r + 1 'Continue loop until variable r = 6 Loop Until r = 6 ' Display the result in a message box MsgBox result End Sub
Get Excel *.xlsm file
How to use the Do Loop statement.xlsm
More than 1300 Excel formulas
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form