Learn 100% online from anywhere in the world. Enroll today!

VBA Loop

Enables users to repeat the same code block multiple times until a specific point is attained or a given condition is met

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.

 

VBA Loop

 

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.

 

 

Quick Summary

  • A loop allows users to repeat the same task multiple times without having to write code for each of the tasks.
  • The main types of loops in VBA include Do Until Loop, Do While Loop, and For Loop.
  • The type of loop determines the beginning and ending statement of a block of code.

 

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

CFI is the official provider of the global Certified Banking & Credit Analyst (CBCA)™ certification program, designed to help anyone become a world-class financial analyst. To keep advancing your career, the additional CFI resources below will be useful:

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

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now

to take your career to the next level and move up the ladder!