Updated May 23, 2023
VBA While Loop
VBA While loop is an essential and powerful concept you might have seen in most programming languages. If you master this concept under VBA, you can prepare powerful scripts that work with spreadsheet data in different and convenient ways. In this article, get ready to explore the world of difference while loops, along with multiple examples coming for your help.
The purpose of a While loop is to repeat a specific block of statements subject to a condition being met. More precisely, while loop repeats the corresponding statement while a condition is True. Once the condition becomes False (Does not meet), the loop terminates itself.
There are two types of while loops in Microsoft Excel VBA:
- VBA While-Wend Loop
- VBA Do-While Loop
VBA While-Wend Loop exists to make the code compatible with an older version of codes. The VBA Do-While loop represents the updated version of the while loop, offering enhanced flexibility and structure compared to its predecessor. The Microsoft community recommends utilizing it.
If you are wondering what Wend means, it is a short form of While End and works similarly.
Examples of VBA While Loop
Let’s dive deep into the step-by-step guide to different types of VBA While Loops.
Example #1 – VBA While-Wend Loop
Syntax:
Let’s take an example to see this in a better way.
- Define a sub-procedure to create a macro under the new module.
Code:
Sub WhileEx1() End Sub
- Set two variables Number to One and Sum to Zero.
Code:
Sub WhileEx1() Number = 1 Sum = 0 End Sub
- Set a while condition for the Number variable.
Code:
Sub WhileEx1() Number = 1 Sum = 0 While Number <= 10 End Sub
- Add statements to be executed for this while condition.
Code:
Sub WhileEx1() Number = 1 Sum = 0 While Number <= 10 Sum = Sum + Number Number = Number + 1 End Sub
- Add Debug.Print Sum so that the sum of the first 10 natural numbers can be printed one by one in Immediate Window.
Code:
Sub WhileEx1() Number = 1 Sum = 0 While Number <= 10 Sum = Sum + Number Number = Number + 1 Debug.Print Sum End Sub
- Finally, end the While loop with Wend statement.
Code:
Sub WhileEx1() Number = 1 Sum = 0 While Number <= 10 Sum = Sum + Number Number = Number + 1 Debug.Print Sum Wend End Sub
- Hit F5 or Run Button to run this code and see the output.
In this code, the sum variable is set to zero. So every time the number is increased by 1 until it reaches 10 (using a while loop), the sum variable will add the previous sum with the current number and display it under the Immediate Window row by row or stepwise sum; you can say.
Example #2 – VBA Do-While Loop
Do-While Loop When Condition is checked before the loop starts
There are two ways in which a do-while loop can be executed.
- You can add a condition before the loop starts (It is the same as the While-Wend loop). There will be no loop iteration if the condition fails for the first time.
- You can add a condition at the last of the loop to check. In this case, there will be at least one iteration of the loop before the condition gets failed.
Syntax:
Let’s take one example to make things clearer.
- Insert a new module and define a new sub-procedure to define a macro.
Code:
Sub WhileEx2() End Sub
- Define a new variable I as an integer. And initiate its value to 1.
Code:
Sub WhileEx2() Dim i Dim Integer i = 1 End Sub
- Use Do-While to add a condition at the beginning of the loop.
Code:
Sub WhileEx2() Dim i Dim Integer i = 1 Do While i <= 10 End Sub
- Add statements to be executed as long as the condition is true. Add the below line of code to the macro.
Code:
Sub WhileEx2() Dim i Dim Integer i = 1 Do While i <= 10 Cells(i, 1).Value = i * i End Sub
This statement allows the ith row of the first column to store the squared value of numbers.
- Add one more statement which allows an increment in i by 1 at every iteration of the loop.
Code:
Sub WhileEx2() Dim i Dim Integer i = 1 Do While i <= 10 Cells(i, 1).Value = i * i i = i + 1 Loop End Sub
- Complete this loop by adding a Loop statement at the end of your code.
Let’s see the workaround of this code:
For the values of i from 1 (i = 1 initially) to 10, it calculates i * i (i.e., squared value for each integer value of i) and inputs it under rows 1 to 10 of column 1 (i.e., column A), respectively. The output is nothing but the squared values of integers 1 to 10.
- Run this code by hitting the F5 or Run button and see the output.
You can see the squared values of natural numbers 1 to 10, which are stored separately from column A in each cell.
Example #3 – Do-While Loop When Condition is checked at the end of the loop
Syntax:
Let’s see an example to see the looping in a better way.
- Insert a new module and define a new sub-procedure to store a macro.
Code:
Sub WhileEx3() End Sub
- Define a new integer I, same as in the previous example, and assign a starting value to it.
Code:
Sub WhileEx3() Dim i Dim Integer i = 1 End Sub
- Use a Do condition to add the statements to be executed as long as the condition is true.
Code:
Sub WhileEx3() Dim i Dim Integer i = 1 Do Cells(i, 2).Value = i * i i = i + 1 End Sub
Using white space (Tab key) as an indentation shows in the above code and allows the code to run smoothly, defining a good structural format for the code.
- Use a loop while adding a condition to be checked for each statement to get executed.
Code:
Sub WhileEx3() Dim i Dim Integer i = 1 Do Cells(i, 2).Value = i * i i = i + 1 Loop While i <= 10 End Sub
- Run this code manually by hitting the F5 or Run button and see the output.
You’ll see the output as shown in the figure above.
How Does this Code Work in the Backend?
The system multiplies the value of i (starting from 1) to itself and stores in the second column (Cells(i, 2)) for each row one by one. The code stops execution as soon as the value of the variable i reaches 10. The output might be the same, which logically means both the codes are working as same. But the main difference is in compiling the code. In the last example, the system already had the condition and knew the number it needed to go to. However, in this code, the system is unaware of the condition at the time of execution of statements and runs the iteration one by one. As long as the condition is false, the code stops executing.
Things to Remember About VBA While Loop
- The While-Wend loop is obsolete and is just to be compatible with older versions of codes. You should use a Do-While loop instead.
- The do-while loop surpasses the while-wend loop in terms of flexibility and structure.
- Do-while loop can be used in two ways; where in one case, you can add a condition first to be checked and then add statements satisfying the condition. An alternative approach is to add statements first and then check the condition later.
- We recommend using indentations in loops to make the code more dynamic and easier to read.
Recommended Articles
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 a downloadable Excel template. You can also go through our other suggested articles –
