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.
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.
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.
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: