WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
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 (this blog)
  2. Looping N times (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. You can also learn about looping in VBA on our two-day introduction to VBA course and (in more detail) on our two-day advanced VBA course, both live online courses.


This blog has 0 threads Add post