VBA Loops

Publication Date :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

arrow

What is VBA Loops in Excel?

A VBA loop in excel is an instruction to run a code or repeat an action multiple times. This action (task) can be repeated for a collection of objects like a range of cells, worksheet or workbook, array, chart of a worksheet, and so on. Since a separate code for each task need not be created, loops work much faster than manual repetition of tasks.

For example, VBA loops can be used to highlight cells containing specific text strings, delete empty rows of a dataset, hide or unhide a worksheet, open or close a workbook, set colors of a chart, and so on.

With loops, one can go through the different objects (elements) one by one. Hence, it is possible to analyze and work with each element.

In VBA, statements are executed sequentially. This implies that the first statement is executed first, followed by the second, the third, and so on. However, a VBA loop executes and repeats a single statement or a group of statements till a particular point is attained.

Loops are usually used in all programming languages.

Table of contents
What is VBA Loops in Excel

Top 4 Types of VBA Loops

There are different types of loops in VBA. The top four are listed as follows:

  1. For Next loop
  2. For Each loop
  3. Do While loop
  4. Do Until loop

Let us discuss each type of Excel VBA loop one by one.

#1–For Next VBA Loop

For Next loop allows to loop through a list of numbers and a collection of objects. These objects include cell ranges, worksheets, workbooks, shapes or other objects on a worksheet.

While looping through a range of cells, the same task is performed for every cell specified in the loop. In the For Next loop, the starting and ending numbers need to be mentioned.

With the For Next loop, the block of code is executed for a specific number of times. Hence, this loop must be used when the number of iterations (repetitions) to be performed is known in advance.

Example #1

We want to insert serial numbers 1 to 10 in cells A1 to A10. This is to be done in the following ways:

a) With the traditional technique

b) With the For Next loop

a) The code to insert the serial numbers in the traditional way is written as follows:

Sub SerialNumber ()

Range("A1").Value = 1
Range("A2").Value = 2
Range("A3").Value = 3
Range("A4").Value = 4
Range("A5").Value = 5
Range("A6").Value = 6
Range("A7").Value = 7
Range("A8").Value = 8
Range("A9").Value = 9
Range("A10").Value = 10

End Sub

Here, we have written ten lines of code. However, if 100 or 1000 numbers are to be inserted, it would require several lines of the code. Since this would become very difficult, the For Next VBA loop is used to ease the process.

b) The steps to insert the serial numbers with the For Next VBA loop are listed as follows:

Step 1: Open the macro and declare the variable “i” as an integer.

Sub Insert_Serial_Number ()

Dim i As Integer

End Sub

Step 2: Open the For loop. Specify the start and the end of the loop using the variable “i.” The same is shown in the following image.

Vba For Loop

Step 3: Write the code for the task to be performed. Apply the cells property to insert the serial numbers.

Vba For Loop 1

Step 4: Enter “i” as the row number and “1” as the column number. The variable “i” has already been declared as an integer.

Vba For Loop 2

Step 5: The value of the first cell should be one. So, instead of writing “1,” enter “i” as the value. Every time the Excel VBA loop runs, the value of “i” increases by 1.

Vba For Loop 3

Step 6: Press the F8 key to execute one line of the code at a time. This key is to be pressed till the For loop is reached, as shown in the following image.

Note: With the F8 key, one can observe the impact of every line of the code on the worksheet.

Vba For Loop 4

In the following pointers (step 6a to step 6b), the different values of “i” are discussed.

Step 6a: Place the cursor on “i,” as shown in the following image. The value of “i” appears as zero. Moreover, a line is displayed in yellow with a yellow arrow pointing to it.

The yellow color indicates that this line (shown in the following image) of the code is about to run.

Vba For Loop 5

Step 6b: Press the F8 key again. Place the cursor on “i” and its value appears as 1, as shown in the following image. Since the value of “i” is 1, “cells (i, 1).value = i” implies “cells (1, 1).value = 1.”

i value=2

Step 7: Press the F8 key. The value “1” appears in cell A1, as shown in the following image.

result in A1

Step 8: Press the F8 key again. Excel VBA goes back to the For loop again. This is because the end limit of the variable “i” is set at 10. Place the cursor on “i” and its value appears as 2.

i value =2

Step 9: Likewise, the For Next loop runs ten times. Hence, the serial numbers 1 to 10 are inserted in the cells A1:A10, as shown in the following image.

Vba For Loop 9

#2–For Each VBA Loop

The For Each loop is also known as the For Each-Next loop.

The For Each loop of VBA helps in looping through a collection of objects. These objects include worksheets, charts, workbooks, shapes, range of cells, and so on.

With the For Each loop, one can go through every object of a collection and perform a task on it. For example, one can perform tasks on every worksheet of a workbook. This task can be to hide or unhide one or more worksheets.

The For Each VBA loop in Excel is used when the number of objects (or elements) in a collection is not known. Hence, the number of iterations (repetitions) to be performed is not known in advance.

Example #2

We want to perform the following tasks with the help of the For Each loop:

a) Hide all worksheets except the sheet titled “main.”

b) Unhide the hidden worksheets (hidden in task a)

a) The code to hide all the worksheets except the sheet “main” is written as follows:

Sub To_Hide_All_Sheet()

       Dim Ws As Worksheet

       For Each Ws In ActiveWorkbook.Worksheets

              If Ws.Name <> "Main" Then
              Ws.Visible = xlSheetVeryHidden
              End If

       Next Ws

End Sub

b) The code to unhide all the hidden sheets (hidden in task a) is written as follows:

Sub To_UnHide_Specific_Sheet()

       Dim Ws As Worksheet

       For Each Ws In ActiveWorkbook.Worksheets

                  Ws.Visible = xlSheetVisible

       Next Ws

End Sub

#3–Do While VBA Loop

The Do While loop repeats a statement or a block of statements while a given condition is true. In other words, the loop runs as long as the condition is true. Once the condition becomes false, looping stops and VBA exits the loop.

The While condition can be tested either at the start or the end of the loop. This implies that the condition may be checked before the execution of the code block or after it.

The testing of the While condition determines the following:

  • If the condition is false and is tested at the beginning of the loop, the statements inside the loop might not run even once.
  • If the condition is false and is tested at the end of the loop, the statements inside the loop always run at least once.

Note: A condition is any expression which evaluates to the Boolean values, true or false.

Example #3

We want to insert the serial numbers 1 to 10 in cells A1:A10. Use the Do While VBA loop.

The code to insert the specified serial numbers with the Do While loop is written as follows:

Sub Do_While_Example()

         Dim i As Integer

         i = 1
         Do While i < 11
              Cells(i, 1).Value = i
              i = i + 1
Loop

End Sub

The loop runs as long as “i” is less than 11. So, the code keeps inserting the serial numbers till i<11. The moment “i” becomes 11, looping stops.

#4–Do Until VBA Loop

The Do Until VBA loop in excel repeats a statement or a block of statements until a specified condition is true. In other words, the loop runs as long as the condition is false. Looping stops once the condition becomes true.

Like the While condition, the Until condition can also be tested either at the start or the end of the loop.

The testing of the Until condition determines the following:

  • If the condition is true and is checked at the beginning of the loop, the statements inside the loop might not run even once.
  • If the condition is true and is checked at the end of the loop, the statements inside the loop will always run at least once.

Note 1: To specify a condition, either While or Until can be used. However, both of them cannot be used together.

Note 2: The Do VBA loop is used when a statement or a block of statements need to be repeated for an indefinite number of times. This repetition of statements is subject to the fulfillment or non-fulfillment of a condition.

Example #4

We want to insert serial numbers 1 to 10 in cells A1:A10. Use the Do Until VBA loop.

The code to insert the given serial numbers with the Do Until loop is written as follows:

Sub Do_Until_Example()

         Dim i As Integer

         i = 1
         Do Until i = 11
              Cells(i, 1).Value = i
              i = i + 1
Loop

End Sub

The loop runs as long as “i” is less than 11. Once “i” becomes 11, the looping ends. This is because, at i=11, the Until condition becomes true.

The difference between the codes (written in examples #3 and #4) of the Do While and Do Until loops is in the use of the logical operator. In the former, the “less than” operator (<) is used, while in the latter the “equal to” operator (=) is used.

Frequently Asked Questions

1. Define VBA loops.

A VBA loop is an instruction which helps repeat an action multiple times. In other words, the statements are executed more than one time. The number of iterations (repetitions) may be fixed or flexible depending on the kind of loop used.

For example, the iterations of the For Next loop are usually fixed, while those of the Do loops are not fixed.

A loop helps in going through a number of objects and analyzing each one of them. These objects can be cell ranges, workbooks, worksheets, charts, and so on. Loops are preferred in VBA because of their speed. It is much faster and easier to work with loops than repeating tasks manually.

2. What are the various types of loops in VBA and when should they be used?

The various kinds of loops and their uses are stated as follows:

a. For Next loop: It is used when a statement or a group of statements are to be repeated a specified number of times.
b. For Each loop: It is used when a statement or a group of statements are to be repeated for every object (element) in a collection.
c. Do While loop: It is used when a statement or a group of statements are to be repeated as long as the condition is true. The looping ends when the condition ceases to be true.
d. Do Until loop: It is used when a statement or a group of statements are to be repeated as long as the condition is false. The looping ends when the condition becomes true.
e. While Wend loop: It is used when a statement or a group of statements are to be repeated as long as a specified condition is true. This loop was created for compatibility with the earlier languages.

Note: The While Wend loop is not as flexible as the Do While loop. Hence, it is recommended to use the Do While loop instead of the While Wend loop.

3. How to exit the Do loop of VBA?

To exit the Do loop, place the Exit Do statement anywhere inside the Do loop. Usually, an Exit Do statement is inserted after a condition has been evaluated.

The loops Do While and Do Until can be exited with the help of the Exit Do statement. The Exit Do statement is often used in the following situations:

• When it is not required to continue the iterations further
• When a condition has caused the loop to run infinitely

With the Exit Do statement, control is transferred to the statement that immediately follows the loop. The Exit Do statement creates an alternate way to exit the Do loop.