VBA Loop Types: For, Do, and While Explained

VBA has four commonly encountered loop constructs: For…Next, For Each…Next, Do…Loop, and While…Wend. Use For…Next when a counter or known number of passes suits the task, For Each to visit items in a collection or array, and Do…Loop when a condition determines when to stop; While…Wend is supported but is usually less flexible than Do While.

The Main VBA Loop Types

A loop repeats a block of VBA statements. Rather than writing the same instruction for every row, cell, or item, you write it once and let the loop advance through the work. The loop type determines how VBA chooses the next item and when it checks whether to continue.

These constructs are related, but they are not interchangeable in every situation:

For…Next: advances a numeric counter, usually across a known range.
For Each…Next: visits each member of a collection or array without requiring you to manage an index.
Do…Loop: repeats until a condition becomes true or false, with a choice of checking the condition before or after the loop body.
While…Wend: repeats while a condition is true, but offers fewer control options than Do…Loop.

In Excel, all four are VBA language constructs. They run in desktop Excel when macros are enabled; Excel for the web does not run VBA macros. The loop itself does not determine which worksheet is changed: the statements inside the loop do that.

For…Next: Repeat a Counted Number of Times

A For…Next loop uses a counter variable that starts at one value and moves toward an end value. By default, the counter increases by one each pass. A Step clause can change the increment, including making it negative to count down. The loop is useful when the number of iterations is known or when a numeric index is needed to refer to a particular position.

For example, if a macro must examine rows 2 through 20, a counter can represent each row number. The loop body can then refer to the worksheet’s row at that index. This approach is direct when the first and last row are known. If the worksheet’s data length changes, however, a fixed endpoint may skip new rows or include unwanted blank ones; calculate the endpoint or use another method when the data size varies.

VBA checks the counter against the end value as it runs the loop. A Step value determines its direction and rate of change, so a descending loop needs a negative step. Choose a step that moves the counter toward the endpoint; a step in the wrong direction can make the loop behave unexpectedly. After the loop, the counter should not be treated as a reliable way to identify the final processed row without checking the actual loop logic.

For Each…Next: Visit Items in a Collection

For Each…Next is often the clearest choice when the task is to process every member of an object collection or array. Instead of maintaining an index, the loop assigns each member in turn to a loop variable. For Excel, that member might be a worksheet, a chart, or a cell in a range. This can make the intention easier to read: process each cell in this range, for example, rather than process rows 2 through 20.

The loop variable must be compatible with the items being visited. A cell loop commonly uses a Range variable; an array loop commonly uses a Variant. If the code is working with objects, it is assigned an object reference. Use For Each when the task is about every item, not when you specifically need to skip by a numeric interval or calculate a position from an index.

There is a practical caution when iterating a collection: changing the collection itself while traversing it can cause items to be skipped or the traversal to behave differently than intended. For example, deleting worksheets during a For Each traversal is risky. If items must be removed, plan the iteration carefully—often by traversing in reverse using an index, or by collecting items to remove and handling them afterward.

Do…Loop: Repeat Until a Condition Changes

Do…Loop is the most flexible condition-based loop. It can test its condition at the top, before the body runs, or at the bottom, after the body has run. A top-tested loop can run zero times if its condition is already false. A bottom-tested loop always runs the body at least once before it decides whether another pass is needed.

Do While continues as long as its condition is true. Do Until continues until its condition becomes true. The condition can be placed before the body or after it, giving four common arrangements. That flexibility is useful for tasks such as reading input until it meets a rule, processing data until a blank cell is reached, or repeating an operation until a status changes. Use a bottom-tested arrangement only when one initial pass is genuinely required.

A condition-based loop must have a path that eventually changes the condition or exits the loop. If the condition never changes, the macro can run indefinitely. For example, a loop that waits for a cell value to change must include code that can change that value, or another safe way to stop. Do…Loop also supports Exit Do, which leaves the nearest Do loop early when a defined condition is met.

While…Wend: A Supported but Limited Form

While…Wend is another condition-tested loop in VBA. It checks whether its condition is true before each pass, so its body may run zero times. It is straightforward for simple repetition, but it does not provide an Exit While statement. That limitation makes it harder to leave the loop early based on a second condition while keeping the loop structure clear.

For new code, Do While is generally a more flexible alternative: it tests the condition at the same point but also supports Exit Do and has corresponding Until forms. You may encounter While…Wend in older macros, examples, or existing projects. Knowing how it works helps when maintaining that code, but there is usually little reason to choose it for a new loop when Do…Loop expresses the same intent with more control.

How to Choose the Right Loop

Choose based on what controls the repetition, rather than on which loop looks shortest. A useful decision process is:

Is the number of passes known or tied to a numeric index? Use For…Next.
Must every member of an array or collection be processed? Use For Each…Next.
Does the loop continue until a condition changes? Use Do While or Do Until.
Must the body run at least once before checking? Put the condition at the bottom of a Do…Loop.
Are you maintaining existing While…Wend code? Keep its behavior in mind, and consider changing it to Do While if you need an early exit or clearer control.

For instance, a fixed sequence of twelve monthly calculations suits a counted loop. Formatting every worksheet in a workbook suits For Each because the worksheets form a collection. Waiting for a user-entered value to become valid suits a Do loop, provided the code includes a way for the condition to change and does not trap the user in an endless wait.

Loop Control, Nesting, and Excel Examples

VBA provides ways to control loop flow. Exit For leaves the nearest For loop, and Exit Do leaves the nearest Do loop. These statements are useful for a clear early-stop condition, such as finding a match. They should not replace a well-designed loop condition when the stopping rule can be stated up front. A Continue For or Continue Do statement is not part of standard VBA syntax; to skip the remaining work in an iteration, use a conditional If structure.

Loops can be nested, such as one loop over rows and another over columns. Each nested loop needs its own counter or item variable, and Exit For or Exit Do affects only the innermost applicable loop. Nested loops multiply the amount of work: if one loop visits 100 rows and another visits 20 columns for each row, the body may run 2,000 times. For large Excel workloads, repeatedly reading or writing individual worksheet cells can be slow; reading a range into an array, processing values in memory, and writing results back in a block may be more efficient.

The example below adds positive numeric values in A2:A20 on the first worksheet in the workbook containing the macro. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. If A2:A20 contains 4, -2, 7, and text such as n/a, with the other cells blank, the displayed total is 11.

Sub SumPositiveValues()
    Dim ws As Worksheet
    Dim cell As Range
    Dim cellValue As Variant
    Dim total As Double

    ' Use the first worksheet in this workbook.
    Set ws = ThisWorkbook.Worksheets(1)
    total = 0

    ' Visit each cell in the specified range.
    For Each cell In ws.Range("A2:A20")
        cellValue = cell.Value2
        If Not IsError(cellValue) Then
            If IsNumeric(cellValue) Then
                If CDbl(cellValue) > 0 Then
                    total = total + CDbl(cellValue)
                End If
            End If
        End If
    Next cell

    MsgBox total
End Sub

The error check matters because a worksheet cell can contain an Excel error value, which should not be passed to CDbl as if it were an ordinary number. IsNumeric filters out text that is not numeric, and the positive-value test excludes zero and negative values. For a real workbook, replace the fixed range and worksheet selection with references appropriate to the data, and test the macro on a copy first.

Common Loop Problems to Avoid

An infinite loop is one of the most disruptive mistakes. In a Do loop, inspect whether every route through the body can eventually make the condition false or true as required. In a For loop, check that the step moves toward the endpoint. If Excel stops responding during a macro, pressing Esc may interrupt some running code, but it is not a substitute for designing a reliable stopping condition.

Off-by-one errors are another frequent issue. Decide whether the first and last items should be included, and check how the chosen loop handles its boundaries. For a range-based For Each loop, the range itself defines what gets visited. For a counter loop, verify the starting value, endpoint, and step together. Also distinguish an empty cell from a cell containing a formula that returns an empty string; they may look alike on the sheet but represent different values to VBA.

Finally, avoid using a fixed row limit when the data can grow unless that limit is intentional. A loop can be logically correct and still process the wrong records because its range is outdated. Explicitly qualify worksheet references, too: an unqualified Range can refer to the active sheet, which might not be the sheet the macro is meant to change.

Frequently Asked Questions

What are the four types of loops in VBA?
Commonly taught VBA loop constructs are For…Next, For Each…Next, Do…Loop, and While…Wend. Do…Loop has several forms, depending on whether it uses While or Until and whether it checks the condition at the beginning or end.

Which VBA loop is best for Excel rows?
For…Next is convenient when you need row numbers or a known range of row indexes. For Each…Next is convenient when you want to process every cell in a defined range without managing a row counter. The right choice depends on whether the code needs an index.

What is the difference between Do While and Do Until?
Do While repeats while its condition is true. Do Until repeats while its condition is false and stops when it becomes true. Both can check the condition before or after the loop body.

Can a VBA loop run zero times?
Yes. A For loop may have no valid pass for its starting value, endpoint, and direction, and a top-tested Do or While loop may start with a condition that is already false. A bottom-tested Do loop runs its body at least once.

How do you stop a VBA loop early?
Use Exit For inside a For or For Each loop, or Exit Do inside a Do loop, when a specific condition requires an early stop. While…Wend does not have an Exit While statement, which is one reason Do While is usually more flexible for new code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.