Excel VBA For Loop: Explained

What You’ll Learn

1. What Excel VBA for loops are and how they work.

2. How to create and run a VBA for loop in Excel.

3. How For...Next, Do While, Do Until, and For Each loops differ.

4. How to use loop counters, start and end values, and step.

5. How to work with cells, ranges, and collections using loops.

Microsoft Excel is one of the most widely used spreadsheet applications worldwide. Numerous organisations use this MS Excel software for performing administrative, project management, accounting, operational, and many other tasks.

As these industries have enormous data to handle, they use various measures to ease the management process. Hence, powerful tools like VBA are used to automate repetitive tasks, helping save time and reduce manual effort. One of the most effective programming methods for repeating instructions is the loop. With just a few lines of code, a loop in Excel VBA lets you iterate through a set of cells.

What are VBA For Loops in Excel and Why are They Used?

We know that loops are a concept in a programming language that allows a block of instructions to repeat. They help you simplify your code by reducing the need to write the same actions multiple times, improving readability and making repetitive tasks easier to manage.

Loops are flexible, giving the option to repeat certain actions a set number of times. They can also be combined with conditional statements, such as If, to determine whether particular actions should be performed during each iteration.

A ‘For’ loop, in VBA, repeats an action or a set of actions using a counter that moves between specified start and end values. For instance, if there is a macro (coded in VBA) to insert values into a column, then you can use a ‘For’ loop by filling each cell (ex., A1, A2, A3, etc.) sequentially until the loop reaches its stopping condition.

For example, the VBA code shown above shows such an action. In this case, the VBA macro is designed to output even numbers from 2 to 20 into cells A1 to A10, increasing each value by 2. Once the 10th iteration is completed, the loop stops running. The example shown here is a basic one, but the ‘For’ loops are powerful enough to automate a wide range of repetitive actions in Excel.

How do VBA for Loops in Excel Work?

Let us say that you have an idea in your mind as to how your VBA macro should actually work. By introducing a ‘For’ loop into the mix of code, you can set a part of your code to repeat a specified number of times based on the start value, end value and step.

With the help of a simple example, we will explain this process. As VBA for loops in Excel come in handy for performing finite repetitive actions, let us consider that when you press a button, a pop-up has to appear.

On pressing the button, a pop-up box gets displayed a definite number of times in sequence using a defined variable Var_button as the loop counter. For example, the following code displays a message box five times, showing the values 1, 3, 5, 7 and 9.

By pressing the button, the action of the loop begins. Since the loop has a starting value, ending value and increment, it eventually completes a finite number of runs before it finishes. By default, the counter increases by 1 after each iteration. However, this increment can be changed using the optional Step keyword. For example, changing the step value to 2 means that the loop moves through the values 1, 3, 5, 7 and 9, resulting in five pop-ups.

Once the counter moves beyond the specified end value, the loop will exit, allowing the macro to continue executing any remaining instructions. If you want, you can also add additional actions that need to be performed at this point. For instance, changing the value of another cell or creating a different pop-up message. You can also use a nested “For” loop within another loop or place a “For” loop inside conditional statements such as “If...Then.”

The example discussed above contains all typical criteria required to complete a “For” loop using VBA. The code will determine the starting and ending values of the counter (Var_button) and the amount by which it changes during each iteration (Step). The only exception to this example is the button that is needed to start the macro. You do not have to link your macro code to a button press, as you can simply begin this loop and activate the macro manually by going to Developer > Macros > Run.

Bonus Tip

The step keyword does more than increase a counter by one. Using step 2 helps you skip alternate values, while step -1 allows you to process a sequence backwards. Always ensure the step direction matches your starting and ending values, as an incorrect combination may prevent the loop from executing.

How to Create VBA for Loops in Excel Using the Visual Basic Editor?

In the Visual Basic Editor, you can create, test and run a VBA for loop in Excel. It is Excel’s built-in VBA editor that enables you to create your own macros with the help of VBA or edit existing macros, including those generated using the Macro Recorder. To start creating a VBA macro in the Visual Basic Editor using a “For” loop, you can follow these steps:

Step 1: Open VBA Editor in Excel

Start by opening the Visual Basic Editor in the MS Excel workbook. You can open this by using the shortcut Alt + F11 on your keyboard. If the “Developer” tab is already enabled on the ribbon bar in the settings menu of your Excel, you can also go to Developer > Visual Basic to open the editor.

Step 2: Create a New VBA Module in VBA Editor

A new Window will be opened by the VBA Editor at this point. The next step should be to insert the VBA “For” loop (and any additional code) into a new VBA module. The purpose of the modules is to organise VBA code into groups. To do this, select the workbook that is listed on the left tree menu and right-click with your mouse. From the drop-down menu, select Insert > Module.

Step 3: Insert Your VBA For Loop Code in VBA Editor

A new window will appear on the right, where you can either write or insert your VBA code that contains the “For” loop. For example, the code below will output sequential even numbers into cells A1 to A10 on the active worksheet:

Sub Loopval()

Dim i As Integer

For i = 1 To 10

Range("A" & i).Value = 2 * i

Next i

End Sub

Here, the counter “i" starts at 1 and increases by 1 until the loop completes 10 iterations. During each iteration, the code multiplies “i" by 2 and inserts the result into the corresponding cell in column A.

Step 4: Run Your VBA Macro in Excel

Once you have input the code, you can optionally rename the module containing it so that it is easier to identify later. To perform this, select the module name present on the left of the tree menu, and then rename it to a new module name in the “Properties” box as shown below.

Once you are ready to run the code to show the output, click on the “Run Sub/User Form“ button. This will execute your code and update the active worksheet.

Alternatively, open the “Macro” window using the keyboard shortcut Alt + F8, select the macro by its procedure name and then press “Run.” You can also press F5 while the cursor is inside the appropriate procedure in the VBA editor. If your macro runs in response to an event, such as a button click or worksheet change, you will need to trigger that event.

Once your VBA macro is ready, you will need to save your Excel workbook as a Macro-enabled workbook in XLSM format. To perform this, click on File>Save As or press Ctrl +S on your keyboard. If the code does not work, a syntax or runtime error may appear, depending on the issue. However, some logical errors can produce incorrect results without displaying an error, so checking the output is equally important.

You can use F8 in the VBA editor to run the code one statement at a time and identify potential problems.

Step 5: Save the Workbook in Excel

Once your VBA code is working correctly, save the workbook in a format that supports macros. Go to File > Save As, choose Excel macro-enabled workbook (*.xlsm) from the file type options, and then save the file. This ensures that your VBA code is retained when you close and reopen the workbook.

Make every cell work smarter for you by joining the Microsoft Excel Course now!

Types of VBA Loops in Excel

If you want to get the most out of both Excel and VBA, you should know how to use different loops effectively. Loops in VBA enable you to look through a set of objects or values and analyse them one at a time. In addition, you can also carry out a specific task for each loop. We will take a couple of examples of using VBA loops in Excel.

Let us assume that there is a dataset available to you, and you wish to highlight all even row cells. You can make use of loops in VBA to go through the range and analyse the row number of each cell. If the row number turns out to be even, then give any suitable colour (say green), output any other colour. The above logic of the loop sounds simple in Excel VBA; one can also do this using conditional formatting.

In a real-life scenario, you can build many codes using VBA loops in Excel that can help automate tasks. For this, you need to know about the different types of loops that exist in Excel VBA and their correct syntax. This section will demonstrate the different types of loops in Excel VBA with an example for each of them:

Trainer Insight

When choosing a VBA loop, consider what controls the repetition. If you need to process rows by number, For Next is often suitable. If you're working with worksheets or cells in a collection, For Each is usually more convenient. When a task needs to continue while a condition remains true or until a condition becomes true, Do While and Do Until provide greater flexibility.

1) For Next Loop

The For Next loop has a logic that repeats a block of code using a counter that changes with each iteration. You can also provide the starting and ending values of the counter and specify what the code has to do each time during the running of the loop. The syntax of the For Next loop is given below:

For counter = Start to End [Step Value]

[Code Block to Execute]

Next [counter]

The counter (or any other numeric variable) in the For Next loop is used to run the loop. This variable controls the progression of the loop. The start and end values specify the range of values that the loop variable will take. The step keyword is optional and allows you to specify the increment or decrement by which the loop variable will change on each iteration. If step is not specified, its default value is 1.

Example: Here is an example of a For Loop that displays odd numbers from 1 to 20:

Once you open a VBA editor in Excel, you can go to Insert > Modules and rename the modules to differentiate the actions used to apply the code in the Excel sheet. As you can see in the image below, we have written a code in the VBA editor to apply a For Loop - to display odd numbers from 1 to 20 in column B of the specified Excel sheet.

Make sure the code you wrote is correct, and run the procedure using the “Run” command or F5 in the VBA editor.

Once the code runs, you can see the changes executed in Excel. As shown in the image below, the specified odd number values from 1 to 19 have been automatically updated using the For loop.

2) Do While Loop

The second loop to discuss is the Do While loop, which helps to check for a condition and run the loop as long as the condition remains “True.” There are two types of syntax in the Do While loop:

a) Condition Checked Before Execution

Do [While condition]

[Code block to Execute]

Loop

b) Condition Checked After Execution

Do

[Code block to Execute]

Loop [While condition]

From the above two syntaxes, it is evident that in the first case, the While condition is first checked before the execution of any code block. Whereas in the second case, execution of the code block happens first, and the condition for While is then checked. That implies that even if the While condition is False in both cases, the code will run once in the second case.

Example: We use the same example to demonstrate the use of a Do While loop for displaying odd numbers between 1 and 20.

As you can see in the image below, we have written code in the VBA editor to apply a Do While Loop to display odd numbers from 1 to 19 in column B of the specified Excel sheet.

Once the code runs, you can see the changes executed in Excel. As shown in the image below, the specified odd number values (1 To 20) have been automatically updated using the (Do While function) VBA code.

3) Do Until Loop

Do Until loop is very similar to the Do While loop. In a Do While loop, the code continues running as long as the specified condition remains True. On the other hand, a Do Until loop runs until the specified condition becomes “True.” Here are two types of syntax in the Do Until loop:

a) Condition Checked Before Execution

Do [Until condition]

[Code block to Execute]

Loop

b) Condition Checked After Execution

Do

[Code block to Execute]

Loop [Until condition]

From the above two syntaxes, it is evident that in the first case, the Until condition is first checked before the execution of any code block. Whereas in the second case, execution of the code block happens first, and the condition for Until is then checked. That implies that even if the Until condition is True in both cases, the code will run once in the second case.

Example: We refer to the same example used above to demonstrate the use of a Do Until loop for displaying odd numbers between 1 and 20.

As you can see in the image below, we have written code in the VBA editor to apply a Do Until Loop to display odd numbers from 1 to 19 in column B of the specified Excel sheet.

Once the code runs, you can see the changes executed in Excel. As shown in the image below, the specified odd number values (1 To 20) have been automatically updated using the (Do Until function) VBA code.

4) For Each

The fourth loop in VBA is the For Each loop that processes each element in a collection or supported array. We have some examples of collections in Excel VBA:

a) A collection of all open workbooks.

b) A collection of all the worksheets in a workbook.

c) A collection of shapes, including embedded chart objects, on a worksheet.

d) A collection of all cells present in a specified range.

By using the For Each loop, you can look through each of the objects present in a collection and perform a particular action on it. For instance, you can see all the worksheets in a workbook and protect them or go through all the cells in a specified range. With the For Each loop (also known as For Each-Next loop), you do not have to know the number of objects in a collection beforehand. For Each loop will automatically go through each of the objects and perform the required action.

The syntax of the For Each loop is given below:

For Each element In collection

[Code Block to Execute]

Next [element]

Example: The example below will demonstrate the use of the For Each loop to go through all the cells in a specified range and make those cells that contain negative numbers red. We have an existing Excel sheet which contains a list of numbers from cell A1 to A11.

As you can see in the image below, we have written code in the VBA editor to highlight cells in red which contain negative numbers present in column A of the specified Excel sheet.

In the code written above, the For Each-Next loop will go through the collection of cells in the the specified range in column A. Further, the “IF” statement is used to find whether a particular value in the cell has a negative number or not. In case, it finds a negative number, that cell will be given a red colour or else it will go to the next one.

Once the code runs, you can see the changes executed in Excel. As shown in the below image, the cells having the negative numbers have been automatically highlighted in red using the For Each loop.

Apply for the Visual Basic For Applications VBA Training course to learn how to automate routine and repetitive tasks.