VBA Loops

Excel VBA Loops

Loops are commonly used in all of the programming languages, where there is a certain need or a criteria when we need a certain code to run a certain times we use loops for those codes, now there are many types of loops in VBA such as Do while, Do until, For Loop and For each loop, these loops help code to execute until the condition is met.

Let me make this statement at the very beginning itself. “If you want to master in VBA, you need to complete the master the concept of loops in VBA macros.”

Loop is nothing but going through a range of cells, going through a range of objects to repeat the same task for the collection of cells or objects. Instead of doing the task in a separate set of lines of codes by using loops, we can shorten the length of the code to the least possible.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Loops (wallstreetmojo.com)

List of Top 4 Types of VBA Loops

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

Let’s see each of these types in detail.

Type #1 – For Next Loop

For Next loop allows us to loop through the range of cells and perform the same task for every cell specified in the loop. Here we need to tell the starting number & end number.

Example

For example, if you want to insert serial numbers from 1 to 10, below is the traditional way of inserting serial numbers.

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

Looks fine, isn’t it? But the problem here is only 10 times we need to perform this task. But imagine what if you want to insert 100 or 1000 numbers, can you write the code 100 or 1000 lines. It is impossible, and that is where the beauty of For Next loop comes handy.

Step 1: Open macro and declare the variable i as Integer.

Sub Insert_Serial_Number ()

Dim i As Integer

End Sub

Step 2: Now open the For Loop. Here mention the start and end of the loop using the variable i.

Vba For Loop

Step 3: Now write the code that you want to perform. We need to insert numbers from 1 to 10 in A1 to A10 cells. Instead of applying the Range method, apply the Cells method.

Vba For Loop 1

It is asking the row number since we have already declared the variable i as an integer at first. Its value is 1. So mention i like your row number and 1 as your column number.

Vba For Loop 2

Now the value of the current cell value should be one, instead of mentioning the number one mention i as the value. Because every time loop runs i value will be increasing by 1.

Vba For Loop 3

Now press the F8 key to step into one by one line. Press F8 until it reaches For loop.

Vba For Loop 4

This yellow color is the indication that the selected line of code is about to run. Place a cursor on I, and it will show the value as zero.

Vba For Loop 5

Now press one more time F8 key and place a cursor on i & now the value is 1.

i value=2

So the values of i in everywhere is now equal to 1. “Cells (I, 1).value = I” means Cells (1, 1).value = 1.

Press the F8 key and see the value in cell A1. You must see 1 in cell A1.

result in A1

Now, if you press the F8 key, it will go back to the For loop one more time because the end limit of the variable i is 10. This time I value will be 2.

i value =2

For the Next loop will run for 10 times and insert serial numbers from 1 to 10.

Vba For Loop 9

Type #2 – For Each Loop

For Each loop in VBAFor Each Loop In VBAVBA For Each Loop helps the user to inspect and analyze the groups of objects or values individually. It even facilitates performing the specific activity for every object or value by passing a statement or group of statements in this reference.read more is for looping through a collection of objects. For next loop loops through the cells and perform the task, and For Each loop loops through objects like Worksheets, Charts, Workbooks, Shapes.

Using this loop, we can go through all the worksheets and perform some tasks. For example, you can loop through all the worksheets to hide & unhide worksheets.

Example #1

If you want to hide all worksheets except the sheet which you are working on, how will you do it? If you have 20 sheets in the workbook, it is a time-consuming process. But with FOR EACH loop, we can perform this task.

I have 5 sheets, and I want to hide all the sheets except the sheet “Main” below code would do the task for me.

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

Example #2

If you want to unhide all the hidden sheets, then the below code will do the job for you.

Sub To_UnHide_Specific_Sheet()

       Dim Ws As Worksheet

       For Each Ws In ActiveWorkbook.Worksheets

                  Ws.Visible = xlSheetVisible

       Next Ws

End Sub

Type #3 – Do While Loop

Do While loop performs the task while the given condition is TRUE, and once the condition becomes FALSE, it will stop looping. Unlike the other two loops, Do While tests the condition at the end of the loop, not at the beginning.

I will show you the example of inserting serial numbers with Do While loop.

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 above loop will run while i is less than 11 and keeps inserting the serial numbers. The moment I become greater than 11, it will stop looping.

Type #4 – Do Until Loop

Unlike Do While loop, the Do Until loop does not run while the condition is TRUE; rather, it loops until the condition is FALSE. For example, look at the below code.

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 only difference between the Do While & Do Until loop is the operator we specify. In Do, While we mentioned running the loop while i is less than (<), 11 but in do until loop, we mentioned to run the loop Until i is equal to (=) to 11.

Things to Remember

  • There is a more advanced Excel VBA loop example, but we will cover each one of them in a separate article in detail.
  • Each loop is for object variables.
  • Though Do While & Do Until looks the same mentioning the condition is very important.
  • Loops are very important concepts.

Recommended Articles

This has been a guide to VBA Loops. Here we discuss the list of top 4 types of excel VBA loops (For Next, Do While, Do Until, and For Each) with examples and practical uses. Other useful articles related to VBA can be found here –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>