VBA Loop

What is a Loop in VBA?

Loops are one of the most powerful programming tools in VBA, and they allow users to repeat the same code block multiple times until a specific point is attained or a given condition is met. Once the condition is fulfilled, the program executes the next section of the code.

Download the VBA Cheat Sheet

Before you continue, use the form below to get access to our comprehensive VBA cheat sheet for an overview of key codes and macros, terminology, and best practices in Excel VBA.

The Loop command allows users to go through a set of values in a statement or groups of statements and analyze each value individually. Rather than repeat a block of code multiple times, it enables users to write a few lines of code and achieve a significant output without the need to write code for each of the repetitive tasks.

Types of Loops in Excel

There are various types of VBA loops in Excel that help users automate tasks that would otherwise require long lines of code. The following are the main types of loops in Excel:

1. Do Until Loop

The Do Until loop continues repeating until the condition is met or the specified condition evaluates to TRUE. The Do Until statements will continue to be executed as long as the condition is false. The criteria are specified immediately after the “Do Until” statement, and it ends with the loop statement.

The syntax for Do Until loop can take the following two options:

Option 1:

Do [Until Condition]

[Code block to execute]

Loop

Option 2:

Do

[Code block to execute]

Loop [Until Condition]

The primary difference between options 1 and 2 above is when the Until Condition is checked. In the first option, the Until Condition is checked first before the code blocks are executed. On the other hand, in the second option, the code block is executed first, and the Until Condition is checked.

In the first option, if the Until Condition is True, the code block will not be executed, whereas, in the second option, the code block will be executed at least once since the Until Condition is checked after the code has been executed.

Example of Do Until Loop

In the example below, we add the first ten positive integers using Do Until Loop in VBA. The code will be executed until the next number is less than or equal to 10. Once the number is higher than 10, the loop will stop. The VBA code for the loop will be as follows:

Sub AddFirst10PositiveIntegers()

Dim i As Integer

i = 1

Do Until i > 10

Result = Result + i

i = i + 1

Loop

MsgBox Result

End Sub

2. Do While Loop

The Do While Loop allows users to continuously execute a section of code while the condition is met or evaluated to TRUE.

The syntax for a Do While Loop is as follows:

Do while [Condition Statement]

[Code block to execute]

Loop

Alternatively, the syntax for the Do While Loop can be as follows:

Do While

[Code block to execute]

Loop [Condition statement]

The main difference between the two syntax options is when the condition statement is checked. In option one, the condition statement is checked before the start of the loop. Once the statement is checked, the program moves to execute the code written below the Do While statement to help control the number of loops. The code to be looped will only be executed if the condition statement has not been satisfied. If the condition statement is satisfied, the code will stop looping.

In the second option, the code block will be executed first, while the condition statement will be checked next. If the condition is not met, the code block will run at least once as the condition statement is checked. It is opposite to Option 1 syntax, where the code block below the condition statement will not be executed even once if the condition is satisfied.

Example

Suppose we want to add 10 positive integers using the Do While Loop in VBA. In this case, we can use the Do While Loop until the next number is less than or equal to 10. When the number becomes greater than 10, the loop will stop. Here the VBA code for the loop is as follows:

Sub AddFirst10PositiveIntegers()

Dim i As Integer

i = 1

Do While i <= 10

Result = Result + i

i = i + 1

Loop

MsgBox Result

End Sub

3. For Loop

The For loop is a control structure that allows the user to write a loop that will be executed a specific number of times. In this case, the user specifies the number of times they want the loop to run. For loop is classified into Next Loop and For Each Loop.

The syntax for a For Loop is written as follows:

For counter = start To end [Step value]

[Code block to execute]

Next [Counter]

More Resources

To continue developing your knowledge and advancing your career, the additional CFI resources below will be useful:

• Developer Tab
• Excel VBA Examples
• How to Debug Code
• VBA Workbook Events
• See all Excel resources