Excel VBA Do While Loop
Do While Loop means to do something while the condition is TRUE. It is like a logical function which works based on TRUE or FALSE. So if the condition is TRUE it will keep executing the statement inside the loop but if the condition is FALSE straight away it will exit the Do While statement. The working of VBA Do While Loop is shown in the figure given below.
Syntax of Do While Loop in VBA Excel
Do While Loop has two kinds of syntax in Excel VBA.
Both look very similar and there is one simple differentiation in them.
In the first syntax “Do While” loop checks the condition first and gives the condition result as TRUE or FALSE. If the condition is TRUE it will execute the code and perform a specified task and if the condition is FALSE then it will exit the loop.
In the second syntax “Do” loop firstly will execute the code and then it tests whether the condition is TRUE or FALSE. If the condition is TRUE it will again go back and perform the same task. If the condition is FALSE then it will straight away exit the loop.
Example of Excel VBA Do While Loop
If you have not understood anything in the theory part nothing to worry. I will explain to you the simple set of examples to have a fair knowledge about this loop.
Now we will perform the task of inserting the first 10 serial numbers from cell A1 to A10. Follow the below steps to apply “Do While” loop.
Create a macro name first.
Sub Do_While_Loop_Example1() End Sub
Define a variable as “Long”. I have defined “k” as long data type.
Sub Do_While_Loop_Example1() Dim k As Long End Sub
Now enter the word “Do While”. And after starting the loop name enter the condition as “k <=10”.
Sub Do_While_Loop_Example1() Dim k As Long Do While k <= 10 End Sub
Now using CELLS property lets insert serial numbers.
Sub Do_While_Loop_Example1() Dim k As Long Do While k <= 10 Cells(k, 1).Value = k End Sub
Note: Here variable “k” starts from 1, so at first k value is equal to 1. Wherever “k” is there is equal to 1.
Now close the loop by entering the word “LOOP”.
Sub Do_While_Loop_Example1() Dim k As Long Do While k <= 10 Cells(k, 1).Value = k Loop End Sub
Ok, we are done. For better understanding lets test this code one by one by pressing F8 key once.
First press F8 key, it will highlight the macro name by yellow color.
Now one more time press F8 key it will jump to Do While Loop. Place the cursor on “k” and see what the value is.
So, K=0. The reason why “k” is equal to zero now because loop is not started to executed here. Press F8 one more time and see what the value is.
Still, the “k” value is zero. Ok, let’s do one thing now, stop the running of macro and assign the value of “k” as 1 before the loop starts.
Now inside the loop reassign the k value as k = k +1.
Now start the process of executing the code line by line by pressing the F8 key. Start pressing the F8 and see what the value is when it executes the “Do While” loop.
Dim k As Long
Do While k <= 10
Cells(k, 1).Value = k
k = k + 1
So “k” value is equal to 1. Wherever “k” is there is equal to the value of 1. So the line of code Cells (k, 1). Value = k is equal to:
Cells (1, 1).Value = 1 i.e. Row 1 & Column 1 (A1 cell) value is equal to 1.
Press the F8 key and execute action inside the loop.
Now, look at the value in cell A1.
So we got the value of 1 in cell A1.
Now execute the next line by pressing F8 key and see what the value of “k” is.
So now k value is equal to 2. So wherever k is there is equal to 2.
Press F8 key, now the code will return to test the value of k.
Now press two more times F8 key and see what the value is in cell A2.
Like this Do While Loop keeps executing the task of inserting serial numbers until the value of k reaches 11. Now I have already executed line by line until k value becomes 10.
Now if I press one more time F8 key it will go back to test the condition but will not execute the line of code because k value is more than 10 now.
If I press F8 key it will straight away exit the loop and goes to End Sub.
The final output is given as follows.
Like this VBA Do While Loop can be used to perform the same set of tasks until the given condition is TRUE.
Things to Remember
- VBA Do While Loop executes the task until the condition is TRUE.
- We can also test the condition at the beginning of the loop or also at the end of the loop.
- The movement condition is FALSE it will exit the loop and will not perform the task.
This has been a guide to VBA Do While Loop. Here we discussed how to use Excel VBA Do While Loop along with some practical examples and downloadable excel template. You can also go through our other suggested articles –