VBA While Loop

VBA While Loop

VBA While Loop is an important and powerful concept which you might have seen in most programming languages. If you master this concept under VBA, you’ll be able to prepare powerful scripts that work with spreadsheet data in totally different and convenient ways. In this article, get ready to explore the world of different while loops along with multiple examples coming for your help.

Purpose of While loop is to repeat a certain 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 basically two types of while loop 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 is the updated version of while loop which is more flexible and structured as compared to the previous and is recommended to be used by Microsoft community.

Just in case, you are wondering what Wend means, it is a short form of While End and works as the same.

Examples of VBA While Loop

Let’s dive deep into the step by step guide of 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 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 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 while loop), the sum variable will add the previous sum with 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 do while loop can be executed.

• You can add condition before the loop starts (It is the same as While-Wend loop). Here, there would be no iteration of the loop if the condition fails for the first time.
• You can add 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 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 F5 or Run button and see the output.

You can see the squared values of natural numbers 1 to 10 which are stored in each cell separately of column A.

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

It is recommended to use white space (Tab key) as an indentation shown in the above code, which allows the code to run smoothly and also defines a good structure format for the code.

• Use Loop while to add 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 by hitting F5 or Run button manually and see the output.

You’ll see the output as shown in the figure above.

How Does this Code Work in the Backend?

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 execute 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 condition was already there, and the system knew up-to what number it has to go. 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 stopped executing.

Things to Remember About VBA While Loop

• The While-Wend loop is obsolete and is there just to be compatible with older versions of codes. You should use a Do-While loop instead.
• Do-While loop is more flexible and well structured as compared to the While-Wend loop.
• Do-while loop can be used in two ways where in one case you can add condition first to be checked and then add statements satisfying the condition. In another way, you can add statements first and later add the condition to be checked.
• Indentations are recommended to use in loops so that the code can be 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 downloadable excel template. You can also go through our other suggested articles –

1. How to Use FIND Function in Excel?
2. How to Use the VBA TRIM Function?
3. VBA Arrays | Complete Tutorial
4. How to Use the VBA Select Case?