Looping in Visual Basic for Applications macros
Part one of a four-part series of blogs

There are 4 types of loop in Visual Basic for Applications - this blog shows how and when to use each type of VBA loop structure.

  1. Looping in VBA Macros - an introduction to the 4 types of loop (this blog)
  2. Looping a set number of times or iterations (FOR … NEXT)
  3. Looping with conditions (DO UNTIL / WHILE ... LOOP)
  4. Looping over collections (FOR EACH ... NEXT)

This series of blogs is part of our Excel VBA online tutorial.  We also run training courses in Excel and VBA.

Posted by Andy Brown on 15 November 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Looping in VBA Macros

There are essentially three types of loop that you can write in Visual Basic for Applications:

  • Use FOR ... NEXT when you want to loop a given number of times.
  • Use DO UNTIL ... LOOP when you want to loop until a condition is true, or the very similar DO WHILE ... LOOP or WHILE ... WEND when you want to loop while a condition is true.
  • Use FOR EACH ... NEXT when you want to loop over the objects in a collection.

These are in ascending order of usefulness.  If you're an experienced VBA programmer, looping over the objects in a collection is nearly always the way to go.

The rest of this blog gives examples of each type of loop, with the possible forms of syntax.


This blog has 0 threads Add post