VBA HOME PAGE

Menu

Recording macros

Looking at the code

Ways of running macros

Where macros are stored

Reasons to write macros

Writing macros

Procedure types

Visual Basic editor (VBE)

Rules & conventions

Excel objects

Range/Selection objects

Object hierarchy

Object browser

Chart objects

Pivot Table objects

Formulas

Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables

Arrays

Collections

Message Box

VBA Input Box

Excel Input Box

Making decisions (If)

Making decisions (Case)

Looping (Do...Loop)

Looping (For...Loop)

With...End With blocks

User defined functions

Event handling

Error handling

Debugging

Creating User Forms

DAO/ADO Objects

Input/Output Files

Other links

Example code snippets

Userform input example

This is one of the 'control flows' VBA provides which is required to make your VBA code procedures more flexible, reduce the amount of code required and make the system think for itself!

Without any of these 'control flows', your code is very linear and rigid which never is really suitable when trying to mimic 'real world' processes.

The four 'control flows' are:

1.  If...Then...Else...End If

2.  Select Case...End Select

3.  Do...Until/While...Loop

4.  For...Counter/Each...Next

 Do...Until/While...Loop

A Do...Loop is used when you wish to repeat a piece of code a number of times.

This type of loop works by using a logical test to determine if the loop should repeat or terminate and move onto the next calling procedure.

There four variations that can be used and they all have slight differences:

Structure (syntax):

UNTIL Keyword

Do

    Code executed here...

    ....

    [Exit Do]

Loop Until Condition [= True]

Do Until Condition [= True]

    Code executed here...

    ....

    [Exit Do]

Loop

WHILE Keyword

Do

    Code executed here...

    ....

    [Exit Do]

Loop While Condition [= True]

Do While Condition [= True]

    Code executed here...

    ....

    [Exit Do]

Loop

Whichever keyword structure (UNTIL or WHILE) you use is a personal choice as there is no difference in performance or structure; one is the logical inverse of the other.

You can loop While a condition is True/False or Until a condition is True/False - it's a simple choice and hopefully the following two examples can make it very clear:

The above will run and repeat a message box function while the user has chosen 'Yes' to keep the loop going.

Or putting it another way, until the user chooses the 'No' option.

Both the above examples displays a message box and will respond in the same way.

Notice the other two variations being very similar other than where the condition is. It's tested first and not at the end of the loop block. In practical terms, a structure which tests a condition may never run the code in the block if the condition is not satisfied.

Therefore, the question is "Do you want the procedure to run at least once or potentially not at all?" This answer is where you place the condition at the beginning or the end of the loop structure.

The next example, will test a condition before running the code in the block based on if the ActiveCell is empty or not before moving down to the next row.

If the ActiveCell was blank before entering the loop, why would you need to move the cursor down a row? Which is why it is tested first and not at the end of the loop block.

 Exit Do

Exit Do keywords are included in a block should you wish to terminate a loop block early without having wait to the end of iteration period.

It can speed up your procedure if there are various tests in a loop that may unexpectedly change state and act as a catch (error handler of some kind).

It is commonly found with If blocks nested in a loop of this kind.

 Do...Loop - no condition!!

Make sure you have a condition set in any loop block otherwise it will loop infinitely until it runs out of memory or an object fails.

Do not write this:

Where's the condition in the above block?

Next Topic: Looping (For...Counter/Each...Loop)