VBA Loops (Beginner to Advanced) – A Guide

Last Updated: February 22, 2024
puneet-gogia-excel-champs

- Written by Puneet

Important Points

  1. Using Loops in VBA can be helpful when you want to perform a repetitive task.
  2. You can also use a loop inside another loop (nesting).
  3. Make sure to be cautious while creating an infinite loop.

What is a LOOP in VBA?

In VBA, a LOOP is a structure that can repeat a statement. In simple words, a loop can perform an activity or multiple activities and go back to the starting to do it again for a number of times or until a condition is met. This cycle of execution of a loop is called an iteration.

vba-loop-structure1

Types of LOOPS in VBA

In VBA, there are six different types of loops that you can use but you can also describe them in two basic categories.

  1. For Next: To use FOR NEXT LOOP you need to define a number as a counter for the loop run number of times. If you want to run a loop for 10 times then you need to define that value for the counter.
    vba-for-next-loop
  2. For Each: With FOR EACH, you can loop through all the objects in a collection of objects. As every collection has a defined number of objects that makes it a fixed loop.
  3. Do While Loop: It tests a condition and then keeps performing the activity while that condition is TRUE. In simple words, it will first test a condition and only execute the statement if that condition is met.
  4. Do Loop While: It works same as “Do While”, but it performs one iteration of the activity first and then test the condition and if that condition is TRUE then keeps performing that activity.
  5. Do Until Loop: It tests a condition and then keeps executing the statement you have defined until that condition is FALSE and the moment that condition turns TRUE it will stop. Basically, it’s an opposite of “Do While”.
  6. Do Loop Until: it works same as “Do Until” but it performs one iteration of the activity first and then test the condition and if that condition is FALSE then keeps performing that activity.