Excel VBA Loop Collection: For Each and Examples

VBA & macros · Updated November 21, 2026

To loop through a VBA Collection in Excel, use For Each to visit every item, or use a numbered For loop when you need an item’s position or want to remove items safely. For a simple collection, declare the loop variable as a Variant; for a collection of Excel objects, you can use a suitable object variable such as Worksheet.

What a VBA Collection is

A VBA Collection is a built-in container for holding related values or objects. You can add items, retrieve them by position or key, and remove them. Collections are useful when the number of items may change while a macro runs, or when you want to group objects such as worksheets or ranges.

A collection keeps its items in an order, and its numeric positions start at 1 rather than 0. You can optionally give an item a key when adding it, which provides another way to retrieve that item. The key is not the same as the item itself, and VBA does not provide a built-in way to enumerate a collection’s keys.

Collections are not the same as a worksheet range or an array. A range contains cells, and an array uses indexed positions; a Collection is an object with methods such as Add and Remove. You create one with Set items = New Collection.

Loop through items with For Each

For Each is the clearest choice when you simply need to process every item. The following complete macro creates a small collection and prints its contents in the Immediate window in the Visual Basic Editor:

Sub LoopThroughCollection()
    Dim items As Collection
    Dim item As Variant

    ' Create and fill the collection.
    Set items = New Collection
    items.Add "North"
    items.Add "South"
    items.Add "West"

    ' Visit each item in order.
    For Each item In items
        Debug.Print item
    Next item
End Sub

The loop variable is a Variant because the collection could contain different kinds of values. If all the items are known to be strings, a String variable may also be suitable. A Variant is a practical default when the collection’s contents are mixed or not known in advance.

To see the output, open the Visual Basic Editor and choose View > Immediate Window, then run the macro. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11. On a Mac, the Visual Basic Editor is available through the Developer tab; the keyboard shortcut can differ by keyboard and macOS settings. Excel for the web does not run VBA macros.

Loop through Excel objects

Collections are also used throughout Excel’s object model. For example, ThisWorkbook.Worksheets is a collection of the worksheets in the workbook containing the macro. Use an object variable whose type matches the items you expect:

Sub ListWorksheetNames()
    Dim ws As Worksheet

    ' Print each worksheet name in this workbook.
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

Here, ws is declared as a Worksheet, so each iteration assigns the next worksheet to that variable. Use ThisWorkbook when the intended workbook is the one containing the VBA code. ActiveWorkbook refers to the workbook that is currently active, which may be different if another workbook is open.

You can use a similar pattern with other Excel collections, such as Workbooks or a worksheet’s Shapes. Choose an element type that matches the particular collection. If its items may have different object types, a Variant can be more flexible, but strongly typed variables can make the intended object clearer.

Use an indexed loop when position matters

A numbered loop is useful if you need to refer to an item by its position, process only a portion of the collection, or work backwards while removing entries. Since Collection positions begin at 1, the usual bounds are 1 through items.Count:

Sub LoopByPosition()
    Dim items As Collection
    Dim item As Variant
    Dim i As Long

    ' Create example items.
    Set items = New Collection
    items.Add "North"
    items.Add "South"
    items.Add "West"

    ' Collection positions run from 1 to Count.
    For i = 1 To items.Count
        item = items.Item(i)
        Debug.Print i, item
    Next i
End Sub

The output pairs each position with its item: 1 with North, 2 with South, and 3 with West. The Item member can be written explicitly as above, or often omitted when using the default member, as in items(i). Writing Item can make the code easier for a reader to understand.

If the collection may be empty, a loop from 1 to its count will have no items to process. For more complicated bounds or logic, check the count before starting the loop. Avoid assuming that a Collection has a position 0: attempting to retrieve one raises an error.

Remove items without disrupting iteration

Changing a collection while a For Each loop is traversing it can disrupt the enumeration or produce an error. If you need to remove items based on a condition, use an indexed loop that works from the last position down to the first. Removing a later item does not change the positions of the items that the loop has yet to examine.

Sub RemoveSouthItems()
    Dim items As Collection
    Dim i As Long

    ' Build an example collection.
    Set items = New Collection
    items.Add "North"
    items.Add "South"
    items.Add "West"
    items.Add "South"

    ' Work backwards so removal does not skip later items.
    For i = items.Count To 1 Step -1
        If items.Item(i) = "South" Then
            items.Remove i
        End If
    Next i
End Sub

After this code runs, the collection contains North and West. The backward direction matters: if you removed position 2 while counting upward, the old position 3 would move into position 2, and the loop could then advance past it. For removal by a known key, you can pass the key to Remove, but a key must exist or VBA raises an error.

Before running any macro that changes workbook data, save a copy of the workbook. Macros cannot be undone with Ctrl+Z, and that is true even if the macro only appears to make a small change.

Keys, order, and common errors

When adding an item, the optional key can be used to retrieve it without remembering its numeric position. For example, a collection can be created with items.Add Item:="Approved", Key:="Status", then retrieved using items.Item("Status"). Keys must be unique within the collection; trying to add a duplicate key raises an error. A key is for lookup, not a label that is automatically returned alongside each item during a For Each loop.

Common problems usually come from a mismatch between the code and the collection:

  • Using position 0: Collection indexing starts at 1. Start at 1 or use For Each.
  • Using the wrong loop variable type: Use a Variant for general values or a compatible object variable for objects.
  • Changing the collection during For Each: Use a backward indexed loop when removing items.
  • Looking up a missing key: A key lookup fails if no item has that key. Check your logic or handle the possible error.
  • Confusing a workbook with its worksheets: A workbook is not itself a worksheet. Loop through ThisWorkbook.Worksheets to process sheets.
  • Expecting macros to run in a browser: Excel for the web can preserve a macro-enabled file, but it does not run VBA. Use desktop Excel to run VBA code.

For troubleshooting, step through the macro in the Visual Basic Editor with F8 on Windows. This lets you inspect the loop variable and collection count one statement at a time. On a Mac, use the Visual Basic Editor’s debugging controls; keyboard shortcuts may differ.

For Each versus arrays and dictionaries

Use For Each for a Collection when the task is simply to act on each item. Choose an indexed loop when the position matters or when removing items safely. Neither approach is universally faster or better; clarity and the operations required are usually the important factors.

An array is a better fit when you need compact, position-based data or want to work with a block of worksheet values. Arrays can have different lower bounds, so check the array’s bounds rather than assuming it starts at 1. A Collection is convenient for adding and removing items one at a time, but it does not offer every operation available in other data structures.

A Scripting.Dictionary is often more convenient when you need to map unique keys to values or test whether a key exists. It has a Keys method, unlike the VBA Collection. A Dictionary is not the same built-in type as Collection, and code that uses Scripting.Dictionary should account for reference availability if it needs to work on different computers. For a simple ordered group of items, a Collection may be the more straightforward choice.

Frequently Asked Questions

Can I use For Each with a VBA Collection?
Yes. Declare a loop variable compatible with the items, then write For Each item In items and close the loop with Next item. Use Variant for general values, or a matching object type for a collection of objects.

Does a VBA Collection start at 0 or 1?
Its numeric positions start at 1. The last position is the collection’s Count, so an indexed loop commonly runs from 1 to items.Count.

How do I get an item from a Collection by key?
Pass the key to the Collection’s Item member, such as items.Item("Status"), provided the item was added with that key. A missing key causes an error, and keys cannot be enumerated through a Collection property.

Can I remove a Collection item inside For Each?
It is safer not to change the collection while enumerating it with For Each. Use an indexed loop from Count down to 1, and remove items by position when they meet your condition.

Can Excel for the web run VBA loops?
No. VBA macros run in desktop Excel, not Excel for the web. You can use desktop Excel for VBA; if the solution must run in a browser, consider Office Scripts, which use a different programming model.

Related

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.