VbaObjectsIntermediate

Collection object

Stores an ordered group of items that can be iterated or keyed.

Review the syntaxStudy the examplesOpen the coding app
Dim items As New Collection
Open Coding Reference

This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.

What it does

Overview

Stores an ordered group of items that can be iterated or keyed.

Collection gives VBA a built-in container for grouping objects or values without managing worksheet storage directly. It works well for moderate in-memory grouping and ordered processing, but lookups by key are weaker than Scripting.Dictionary when you need fast repeated existence checks.

Quick reference

Syntax

Dim items As New Collection

See it in practice

Examples

1

Queue worksheets for export

Dim sheetsToExport As New Collection
sheetsToExport.Add Worksheets("Report")
sheetsToExport.Add Worksheets("Summary")

A Collection is useful when a macro needs an ordered list of workbook objects to process.

Debug faster

Common Errors

1

Run-time error

Cause: Trying to access a missing key or index directly.

Fix: Guard key lookups or handle errors when the requested item may not exist.

Runtime support

Compatibility

Excel desktop VBA

Source: Microsoft Learn Office VBA reference

Common questions

Frequently Asked Questions

Stores an ordered group of items that can be iterated or keyed.

Run-time error: Guard key lookups or handle errors when the requested item may not exist.