What Are VBA “For Loops” in Microsoft Excel?

You might be wondering how to loop in Excel VBA because you know how Macros are a good and practical way to automate your Excel spreadsheets. 

With the help of Visual Basic for Applications (VBA), Macros, which are automated scripts, are written. This makes it much easier for you to perform a particular action in Excel. For example, if you want to perform a calculation, you can use VBA For Loop to simplify this. 

But what is VBA exactly? It’s a functional programming language to analyze new data. Excel can be more than you think, making it easier to perform specific tasks automatically. 

You can use VBA For Loop to repeat an action in Excel as many times as you want to. A VBA For Loop is used to loop a particular action through cells once the criteria are seen.

What Are VBA Loops Used For?

When you want a program to repeat itself, you can use a programming concept called Loops to do so. What do they help us with? They help us improve the efficiency and speed of the applications we use. We can reduce the amount of time we spend on certain actions and use VBA For Loops to speed it up.

Loops are a very flexible tool and can be used to be combined with other statements so you can set and determine how often and for how long a particular script runs. 

How to set up a For Loop in Excel?

If we assume that you know how VBA macro should work, we should be able to set code to repeat itself as often as we want to, which is done with the help of For Loop. 

For example, if you want a pop-up to appear a set number of times after you press a button, you should use the variable varButton to enter the end number. When you do this, the pop-up box is shown, starting with zero, then repeating it for 9 times until your varButton variable is reached. 

As you know, to begin the Loop, you need to press the button. The Loop has a finite number of runs before it finishes. But, if you add a Step value, you can change the default mode where, for example, if the values increase by 1, you can change it to any number you like. 

When the end value is reached, the macros stop, and the Loop exits. 

But this is not the end exactly as you can add extra actions to perform. For example, if you want to change another cell’s value, you could use the For Loop with other logical tests, such as a Do While or If statement.

Bear in mind that the only optional part of this performance is the button you use to start the Macro. You can easily begin the Loop manually and activate it by selecting the following options:

Developer > Macros > Run instead. 

Different Types of Loops in Excel 

There are different types of Loops in Excel that you can use to automate your actions and tasks that take a lot of your time to write long lines of code for them. 

There are 2 other main types of Loops besides VBA For Loop

• Do Until Loop
• Do While Loop

1. Do Until Loop 

This Loop continues to repeat itself as long as the condition is false. There are two options ahead of you to execute the Do Until Loop:

Option 1

Do [Until Condition]

 [Code block to execute]

  Loop

Option 2

Do

[Code block to execute]

Loop [Until Condition]

There is a primary difference between option one and option two. The first option executes the code blocks after the Until Condition is checked. This is entirely the opposite in the second option where the code block is executed first, and then Until Condition is checked.

Example of Do Until Loop

In this example, we’re trying to add the first ten positive integers. This code will repeat until the next number is less than 10. How does this code work? If the number reaches higher than 10, the Loop stops. 

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 

This Loop allows users to execute a set of codes while a certain condition is met. 

The Do While Loop syntax is:

Do while [Condition Statement]

[Code block to execute]

Loop

The other syntax we can use for the Do While Loop is: 

Do While

[Code block to execute]

Loop [Condition statement]

But what is the difference between the two options? 

In the first option, before starting the loop, the condition statement gets checked and the exact opposite happens in the second option. But when will the Loop stop? If your condition statement is satisfied, the loop will stop. 

Here’s an example of a Do While Loop:

Sub DoWhileLoop()

    Dim n As Integer

    n = 1

    Do While n < 11

        MsgBox n

        n = n + 1

    Loop

End Sub

How to Add For Loops in Excel with the Help of Visual Basic Editor 

The VBA (Visual Basic for Applications), which is Excel’s built-in editor, allows you to create and test your own macros or edit them with the help of the Macro Recorder tool. You have to follow some steps to begin creating a VBA macro. 

Step 1: 

Open your Visual Basic Editor in your Excel workbook. If you want an easier way to open it, press Alt+F11. Another way to open it is to enable the Developer tab in your Excel’s settings menu, then press Developer > Visual Basic to open the editor.   

Step 2:

Now, all you have to do is insert your VBA For Loop as a new VBA module. But what are modules even used for? Modules are used for organizing VBA code into groups so they can be a lot more efficient. How can you do this? You must right-click your workbook on the left. Select Insert > Module. 

Step 3:

Right when you see a new window on the right, insert your For Loop code. 

For example, if you want sequential values into cells A1 to A20, insert this code on any active worksheet you like.

Sub LoopVal()

Dim i As Integer

For i = 1 To 10

Range(“A” & i).Value = i

Next i

End Sub

Step 4:

In this step, we rename our module, which contains the code, so it can be easier to refer to it later on. On the tree menu on the left, rename the module name by typing a new name into the Properties box. 

Once you’re ready to test your code, press the Run Sub. This way you can see the macro in action. If the VBA For Loop code is as you intended, proceed to save your workbook with the macro it has. If it isn’t exactly what you wanted it to be, you can troubleshoot the issue, and then it will be fixed. 

Step 5: 

When your VBA macro is ready, save your Excel workbook in the XLSM format. To save, you have two options. You can either press Ctrl + S or press File > Save As.

Step 6:

Once you’ve saved your workbook, your macro is ready to run. When you press Alt + F8, the Macro window opens. Select the wanted macro that also matches the Module name, then press run. 

Things to Remember Before Using a For Loop 

• Sometimes, you may want your Loop to end before the last value is reached. For you to be able to do this, you need to add an Exit For statement to your code. Once you do this and press a specific button, the Loop stops and moves on to the next line of codes. 
• If your VBA For Loop doesn’t work and your code has errors, VBA will go into debug mode with an error, and then you can troubleshoot your code to fix this problem. 
• One good thing is that other logical statements such as an If or Do While can be nested with VBA For Loops, and this allows you to come up with much more complex decision-making codes. 

Final Words

Once you know how to work with VBA For Loops, you can then begin to experiment with Do Until Loops and more. There are some VBA tricks that enable data analysts to create complex and difficult applications inside Excel workbooks. Once you master VBA, you can speed up your calculations and automate the actions and tasks you desire. 

FAQ