VBA Loops

What Is VBA Loops In Excel?

VBA Loops allow us to perform the same set of actions across multiple cells, worksheets, workbooks, etc. For example, if we want to insert a serial number from 1 to 10 in the first 10 rows, then we need not have to write 10 line of code. Rather, we can use loops to perform the actions across ten different cells.

Following are some practical examples of using VBA loops in Excel.

  • Loop through a range of cells and perform the same set of actions. For example, apply formatting or insert text.
  • Loop through multiple worksheets and perform some actions or tasks. For example, hide or unhide sheets.
  • Loop through multiple workbooks and do something. For example, save and close all the opened workbooks except the current workbook.
  • Loop through all charts and do something. For example, change the font or style.
  • Loop through arrays.
  • Loop through all the characters in a cell and extract a specific portion of the text.

These are all the activities possible by using VBA loops and working efficiently with VBA.

Key Takeaways
  • VBA Loops help us to perform the same tasks multiple times based on the number provided in the loop.
  • There are four types of VBA loops in Excel, and those are, FOR NEXT VBA LOOP, FOR EACH VBA LOOP, DO WHILE VBA LOOP, and DO UNTIL VBA LOOP.
  • The For Next VBA LOOP is used to loop through cells.
  • Similarly, the For Each VBA Loop is used to loop through objects.
  • Do While and Until works in a similar fashion. However, the logic given should be vice-versa.

Now, let us look at the types of VBA loops.

There are 4 types of VBA loops available in Excel:

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

#1 – For Next VBA Loop

The For…Next loop is used to repeat the same code as many numbers of times as specified in the code. Here, we must mention the starting and ending position of the loop. The For…Next LOOP is used to loop through cells and perform the same set of actions.

For instance, we must insert serial numbers from 1 to 10 in cells A1 to A10. The starting position of the loop is 1, and the ending position is 10. Here, the loop will run ten times and repeat the same set of actions across all 10 cells.

Syntax

The following is the syntax of the For…Next LOOP.

Syntax of the FOR NEXT LOOP

In the For…Next LOOP, we must assign the variable and give starting and ending numbers to insist on how many times the block of code must be executed. The code will keep executing until it reaches the end number.

Example #1 – Insert Serial Numbers

For instance, assume we must insert serial numbers from 1 to 10 in cells A1 to A10. For this, we can write the code as shown in the following image.

Vba Loop - serial number example

The above code will insert the number 1 to cell A1, 2 to cell A2, and so on.

Vba Loop - serial number example - output

However, the problem here is that for every cell, we have written different lines of code. Totally there are 10 lines of code written for this. To avoid these lengthy codes, we can use FOR NEXT LOOP.

Step 1: Enter the Sub-procedure name as shown in the following image.

Vba Loop - serial number example - step 1

Step 2: Define a variable to start the For…Next LOOP and assign a data type as an integer.

Vba Loop - serial number example - step 2

Step 3: Open the For…Next LOOP with the variable.

Vba Loop - serial number example - step 3

Step 4: Once the loop starts, we must give the starting and ending of values of the loop. In this example, our starting number is 1, and the ending one is 10.

Vba Loop - serial number example - step 4

Step 5: Before we write the block to repeat the execution, we must increment the FOR LOOP’s variable value by using the word NEXT [Variable name] format as shown in the box below.

Vba Loop - serial number example - step 5

Step 6: Inside the For…Next LOOP, we will write the code that is to be repeated. We reference cells by using the CELLS property.

Vba Loop - serial number example - step 6

Step 7: We must provide the row number for the CELLS property. Instead of providing the row number as a constant, we can provide the variable name.

Vba Loop - serial number example - step 7

Step 8: Next, provide the column index number as 1.

Vba Loop - serial number example - step 8

Step 9: Select the VALUE property and set the value to the variable k.

Vba Loop - serial number example - step 9

The coding part is complete. Now we will execute the code line by line by pressing the F8 key.

Step 10: Place the cursor inside the macro and press the shortcut key F8 to begin the macro.

Vba Loop - serial number example - step 10

Step 11: Place the cursor on the variable k to see the value.

Vba Loop - serial number example - step 11

Step 12: As of now, the value of k is zero. Press the F8 key one more time and see the value of variable k.

Vba Loop - serial number example - step 12

The value of k is 1 i.e., the loop is running for the first time, and starting position of the loop is 1.

Hence the value of k is 1 throughout this code for now.

Step 13: In the CELLS property, we have provided the variable k, which references the cell like CELLS(1,1) i.e., cell A1 and the value in the cell will be equal to the value of the variable k, i.e., 1.

Press the F8 key one more time, and we get the value 1 in cell A1.

Vba Loop - serial number example - step 13

Step 14: Press the F8 key one more time, and it will go back to the code inside, and the value of variable k now will be 2.

Vba Loop - serial number example - step 14

K value is equal to 2 i.e., the loop is running for the second time, and wherever k is there, the value will be 2.

Hence, CELL(K,1) will be CELLS(2,1) i.e., cell A2.

Step 15: Press the F8 key, and it will insert the value 2 in cell A2.

Vba Loop - serial number example - step 15

Step 16: Similarly, the loop runs 10 times and exits the loop once the value of k reaches 10.

Press the F5 key to complete the code in a single shot, and we should see serial numbers 1 to 10 in cells A1 to A10.

Vba Loop - serial number example - step 16
Example #2 – Format Alternative Cells

Assuming we must apply some color formatting for every alternative row. We can use the FOR NEXT LOOP to apply color formatting.

The following code will fill every alternative cell with blue color.

Vba Loop - Example 2 - Alternative cells

Copy the above code and paste it into the Visual Basic Editor.

Vba Loop - Example 2 - Alternative cells code

Run the code by pressing the F5 key. It fills every even row cell with the blue color.

Vba Loop - Example 2 - Alternative cells -output

#2 – For Each VBA Loop

The For…Each LOOP is used to loop through objects. The following are the objects in Excel:

  • All opened workbooks are objects.
  • All worksheets are objects.
  • All cells or a range of cells are objects.
  • All shapes and charts are objects.

Syntax

The syntax of the FOR EACH LOOP is as follows.

Syntax of the FOR each Loop
Example #1 – Hide All Worksheets

If you are working with large worksheets, you may often need to hide worksheets. Using the FOR…EACH LOOP, we can loop through all the available worksheets and hide them.

For instance, look at the following worksheets.

Vba Loop - Example 1- hide Sheets

We must hide all the worksheets except the worksheet named “Sheet1”. The following code will loop through all the worksheets and hide them.

Vba Loop - Example 1- hide Sheets code

This will hide all the worksheets except the worksheet named “Sheet1”.

Vba Loop - Example 1- hide Sheets - Output
Example #2 – Unhide All Worksheets

Once the worksheets are hidden, we may have to unhide them with a single click. The following code will unhide all the worksheets.

Vba Loop - Example 2 - Unhide sheets

This will unhide all the hidden sheets in the workbook.

#3 – Do While VBA Loop

The Do…While Loop is used to run a loop while the applied condition is met or TRUE. There are two types of syntax available for the Do While Loop.

Syntax #1

Syntax 1 for do while loop

Syntax #2

Syntax 2 for do while loop

In syntax #1, the loop will begin, and test if the condition is TRUE. It will execute the code inside the loop while the condition is TRUE. Once it is FALSE, it will come out of the loop.

In syntax #2, the loop will begin and execute the code inside the loop, then test the condition in the end. If the condition is TRUE, only thenit will return back to the loop, or else it will exit the loop.

For example, look at the following code.

vba loop - Do while code

The above code will insert the values from 1 to 10.

  • First, we must set the variable k value to 1.
  • Then, we should apply the condition. While k value is less than 10, insert serial numbers in column A.
  • Then, inside the loop, we increment the k value by 1 every time the loop runs. As long as the k value is less than 10, it keeps executing the code inside the loop, and once the k value is more than 10, it will exit the loop.

#4 – Do Until VBA Loop

Do…Until Loop is similar to the Do…While Loop. The Do…Until Loop runs until its condition is TRUE, and the Do…While loop runs while the condition is TRUE.

We can take the same code from the Do…While example.

vba loop - Do until loop code

The only change we did from the previous code is that we added the word Until instead of While and changed the condition to >10.

This loop will keep running until the variable k value reaches 11 and inserts values. Once the variable k value reaches >10, it exits the loop.

Important Things To Note

  • It is necessary to put the Next word at the end of the loop for both FOR…NEXT and FOR…EACH VBA loops.
  • The objects in Excel are Cell, Cells, Worksheets, Workbooks, Charts, and Shapes.
  • If you put the condition at the bottom of the Do While and the Do…Until loop, the loop will first execute the code and then test the condition.
  • CELLS is a property in VBA. Always supply a variable name for the row number of the cell’s property.

Frequently Asked Questions

How do you stop a loop in VBA?

If we want to exit the loop on certain conditions, we can use the EXIT FOR LOOP. If we want to stop it while it is running, we can use the shortcut key Ctrl + Break.

What are loops used for?

Loops are used to repeat the same set of code multiple times.

Which loop is faster in VBA?

Between FOR NEXT and FOR EACH, the FOR EACH loop is faster. However, if we are using smaller datasets then we won’t see any difference at all.

How important are loops in VBA Programming?

In any programming, language loops are so crucial to working efficiently. Without using loops, we need to write lengthy codes, and it is not a good practice at all.
It helps the coder to reduce the number of coding lines drastically and work efficiently.

Download Template

This article must be helpful to understand VBA Loops, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to VBA Loops. Here we explain top 4 types of VBA loops including For Next loop, For each Loop, Do while with examples & templates. You may learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *