The VBA programming language supports the Do While Loop. In this article, we will learn how to use the Do While Loop in Excel VBA.
The Syntax of Do While Loop
The VBA Do While Loop has two syntaxes:
Entry Control Do While Loop
Do While Condition 'Statement1 'Statement2 '-- '-- 'StatementN Loop
In this syntax, the condition is first checked. If the condition is matched, the control enters the loop, else loop is terminated.
Exit Control Do While Loop
Do 'Statement1 'Statement2 '-- '-- 'StatementN Loop While Condition
In this syntax, control enters the loop first. After executing each task, VBA checks the condition in the end. If the condition is True, the Do While Loop continues, else the loop terminates immediately. Use this syntax, when you want your loop to be executed at least once.
In this example we will try to delete each sheet one by one until only 2 sheets are left in the workbook using VBA Do While Loop:
Sub WhileTest() Application.DisplayAlerts = False Do While Sheets.Count > 2 ActiveSheet.Delete Loop Application.DisplayAlerts = True End Sub
The above subroutine will first check if the workbook has more than 2 sheets. If the workbook has more than 2 sheets, the control will enter the loop and will delete the current activesheet. The loop will continue until only 2 sheets are left.
If the workbook already has only 2 or 1 sheets, the control will not enter the loop and nothing will happen.
Now if you wanted to delete at least 1 sheet at starting of the loop, even if the workbook only has 2 sheets, then use the exit control do while loop.
Sub WhileTest() Application.DisplayAlerts = False Do ActiveSheet.Delete Loop While Sheets.Count > 2 Application.DisplayAlerts = True End Sub
The above subroutine will first delete 1 sheet and then it will check how many sheets are there. If they are greater than 2 sheets then the loop will continue. If the workbook is left with 2 sheets or less then the loop will exit.
The major takeaway here is that this loop will execute once at least. You can use this to repeat some tasks by asking the user. For example, you can ask a user for the password. And loop him until he enters the correct password or exits. In this scenario, you have to ask the user for the password at least once.
Note: I have used DisplayAlert property of Application object to disable the alerts. You can read about it here.
So yeah guys, this was the Do While Loop in Excel VBA. I hope it was explanatory enough. If you have any doubts regarding this or any other Excel 365/2019/2016 related query. Ask in the comments section below.
Related Articles:
7 Examples of For Loops in Microsoft Excel VBA | The for loop is the most used looping technique in any programming language. In VBA the For loop can be used in several ways.
The If ElseIf Statement in VBA | The If ElseIf is a basic condition checking technique for returning conditional outputs.
Delete sheets without confirmation prompts using VBA in Microsoft Excel: While deleting sheets you get an confirmation prompt. To disable it we use the Application...
Display A Message On The Excel VBA Status Bar | To display message on the status bar we use the StatusBar property of the Application object.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
