How to use WHILE statement in VBA?

The “While” statement is a control structure used to create a loop that continues executing as long as a specified condition remains true. Here’s a breakdown of the components and usage of the “While” statement in VBA:


While condition
    ' Code to execute while the condition is true

condition: This is the expression that determines whether the loop should continue. It is evaluated before each iteration, and as long as it remains true, the loop will continue.

How It Works

  • The “While” loop begins by evaluating the condition. If the condition is true, the code inside the loop is executed.
  • After each iteration of the loop, the condition is evaluated again. If it’s still true, the loop continues; if it’s false, the loop terminates, and control moves to the next statement after the “Wend” keyword.
  • It’s important to ensure that the condition eventually becomes false to avoid infinite loops. You typically use a counter variable that changes with each iteration to ensure the condition will eventually be false.

Let’s modify the previous example with some additional comments:

Sub ExampleWhile()
    Dim counter As Integer
    counter = 1
    ' Start of the While loop
    While counter <= 10
        ' Code that will execute while the condition is true
        MsgBox "The value of counter is: " & counter
        counter = counter + 1 ' Increment the counter by 1 in each iteration
    ' Control flow continues here after the condition becomes false
End Sub

In this example, the loop continues to execute as long as counter is less than or equal to 10. Inside the loop, a message box displays the current value of counter, and then counter is incremented by 1 in each iteration.

Exiting the Loop

It’s crucial to ensure that the condition inside the “While” loop will eventually become false to prevent infinite loops. You can use various methods to exit the loop when needed, such as changing the value of the control variable (counter in the example) or using the “Exit While” statement within the loop when a specific condition is met.

Remember that “While” loops are just one type of loop in VBA, and there are other loop structures like “For” loops and “Do…Loop” loops that you can use depending on your specific requirements.

Switch the language


Help us grow the project

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Join our Facebook Group!