Using a VBA Do Loop

In this lesson we will learn how to use a VBA Do Loop.

Do ' Your code would go here ' another line Loop

What the VBA Do Loop will do is run the lines between the Do and the Loop repeatedly, depending on the VBA keyword we place after the word Do. This word can either be Until or While.

The following will type the numbers 1 through 10...

MyCounter = 1
Do Until MyCounter = 11
     Selection.TypeText MyCounter
     Selection.TypeParagraph
     MyCounter = MyCounter + 1
Loop
Selection.TypeText "End"

On the first line, MyCounter = 1, we created a variable called MyCounter and assigned it a value of 1.

On the second line, Do Until MyCounter = 11, we are saying to Do the Loop Until the value of MyCounter equals 11. At this stage MyCounter equals 1 which isn’t 11, so the three lines in the loop are executed. The counter is typed at the insertion point (cursor position) followed by and Enter (TypeParagraph). Then we say that MyCounter should now become equal to the value of MyCounter (which is 1) plus 1, which makes it now 2 and the sixth line, Loop, sends the execution back to the Do line.

MyCounter is now 2, which is not 11 so the three lines are run again. After we print the number 10 and add 1 to it now becomes 11. because MyCounter = 11 is now true, the loop won’t run and the code execution skips down to the line after the Loop line and types the word End.

We coud have used a Do While Loop to accomplish the same thing...

MyCounter = 1
  Do While MyCounter < 11
     Selection.TypeText MyCounter
     Selection.TypeParagraph
     MyCounter = + 1
Loop
Selection.TypeText "End"

The difference here is in the condition we have stated. Now we are only running the Loop if MyCounter is less than 11. That is, the Do While runs as long as the condition stated after it is true, whil Do Until runs only as lonf as the condition stated after it is false. You would use whichever makes more sense logically for what the code in a particular Loop is doing.

Now that you know what a VBA Do Loop does and how to use it, in the next lesson we will see how we can "debug" it if something isn't working as expected.

Go to the next tutorial: "An Excel VBA Code Example"

Return from VBA Do Loop to "Free VBA Tutorials"

Return to our "Home Page"