VBA For Loop

What is a VBA For Loop?

A VBA Loop is a portion of the process that will repeat until the specified criteria are met. The criteria depend on the type of loop used. Loops generally begin with a specific statement describing what type of loop it is. It will end with an ending statement that is based on the type of loop. In other words, the type of loop dictates the beginning and end statements.  This guide focuses on the VBA For Loop.

The process to repeat will be contained in these two statements. Loops can also contain more loops inside themselves.

To learn more, launch our VBA Excel course now!

VBA For Loop Diagram

A VBA For Loop is best used when the user knows exactly how many times the loop process needs to repeat. The criteria set in the for loop automatically creates a counter variable, and will add 1 to the loop until the counter reaches the last value.

This process can be performed using the do until or do while function. However, under do loops, the user needs to perform three additional steps manually:

• Declare a counter variable
• Initialize (store an initial value) in the counter variable
• Increase the counter variable by 1 (or another integer) every time the loop runs.

The VBA For Loop automatically does this and saves the user time by eliminating the need to manually code these three processes. Instead, VBA will implicitly perform these three when the user sets the for loop criteria.

To learn more, launch our VBA Excel course now!

VBA For Loop Structure

The structure in the for loop is as follows. The loop procedure is stored between the For

and Next.

    For [variable name] [start number] to [end number]

    Next [variable name]

As an example, let’s say we wanted to fill the first 10 rows of the A column with the text “Company counterVar”. We can set the variable counter name as counterVar in the for loop as follows.

    For counterVar 1 to 10

        Range(“A” & counterVar).Value = “Company “ & counterVar

    Next counterVar

In this process, the For loop will declare a variable named counterVar (implicitly as an integer data type). It will also initialize the variable with the value 1. When the loop starts, counterVar is 1, so the first iteration of the process will look as follows:

        Range (“A1”).Value = “Company 1”

After the loop process ends, it runs into the “Next counterVar” statement. This tells the macro to repeat the process for the next counterVar if the criteria have not been met. The next counterVar after 1 is 2. As such, the loop process will repeat since 2 is not 10. The process should fill Cell A2 with the text Company 2. This repeats until counterVar reaches 10 and Cell A10 is filled with the text Company 10.

To learn more, launch our VBA Excel course now!

More VBA Resources

We hope this has been a helpful CFI guide to making a VBA For Loop. To continue learning and building your Excel VBA skills, we recommend taking our VBA course online and exploring these additional free CFI resources below:

• VBA If Else
• VBA Referencing
• VBA Methods
• VBA Macros
• See all Excel resources