Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

Home   >  Blogs

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 article)
  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 | 5 comments

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.

 

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 article)
  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.

Comments on this blog

This blog has 5 comments:

Comment added by Gotfocus on 25 July 2012 at 23:26 GMT
NICE AND SIMPLE TUTORIAL!
I WANT TO CONVERT DOLLARS IN TO WORDS. CAN YOU SUGGEST ANY WAY OTHER THAN "SPELL NUMBER" CODE
Reply from Andy Brown (blog author)

Apologies for the delay in replying (I've been on a well-earned holiday).

Not sure I understand the question ...

 
Comment added by Gotfocus on 06 August 2012 at 07:23 GMT
There are times when it is beneficial, or even mandatory, to spell numbers out. For instance, you may want to spell out "1234" as "one thousand two hundred thirty four."
Can you plz suggest any macro which can do that work for me! (other then the macro which is provided on micro soft web site called "spell number")
Reply from Andy Brown (blog author)

'fraid not.  Don't think it would be anything to do with looping over cells (the subject of this blog) even if I knew one.  Suspect it would have to be a messy macro involving arrays of all the possible words, with lots of SELECT CASE conditions for the exceptions.

 
Comment added by Smallman on 20 August 2012 at 01:54 GMT

Yeah Andy is right the application of this task is a reasonably messy bit of code.  It is possible though and there are a number of examples posted online.  One of which is here;

http://www.vbaexpress.com/kb/getarticle.php?kb_id=735

If you follow the instructions diligently you will get the result you are after.

Take care

Smallman

Reply from Andy Brown (blog author)
That looks good - thanks.
 
Comment added by Old Grey Mare on 24 August 2012 at 07:30 GMT
Many thanks for the clear instructions, they have helped me immensely.
Enjoyed the looping but a bit disappointed the cream cakes weren't edible! :)
Reply from Andy Brown (blog author)

Sorry about that ...

Incidentally, I've corrected the cell reference you referred to in a previous comment - many thanks for taking the time to let me know about this.

 
Comment added by TimR on 30 October 2012 at 16:02 GMT

You mention that you don't like any of the alterntaive syntaxes for the WHILE and UNTIL loops, however the structures where the condition is tested at the bottom of the loop operate with a distinct difference than those that test the condition at the top of the loop.

Specifically, if you test at the top of the loop, the loop may never execute if the condition is not satisfied, whereas if you test at the bottom of the loop the loop will always execute at least once.

The distinction is important and I've often needed to use top test or bottom test as the situation required.

Thanks

Reply from Andy Brown (blog author)
True!  Thanks for that.
Add a comment to this blog

BLOGS BY TOPIC

BY AUTHOR

BY YEAR

 

All content copyright Wise Owl Business Solutions Ltd 2013. All rights reserved.