Excel VBA Arrays vs Collections vs Dictionaries

In Excel VBA, use an array for a compact, indexed set of values, a Collection for an ordered group of items you add and remove, and a Dictionary when you need to retrieve values by unique keys. The best choice depends on how you access and change the data—not on a rule that one structure is always fastest or best.

These structures all hold multiple values in memory, but their interfaces and trade-offs differ. Choosing deliberately can make code easier to read, reduce lookup work, and prevent errors such as trying to retrieve a missing key or changing the size of an array in the wrong way.

What Each VBA Structure Is

An array is a sequence of elements addressed by numeric positions. It can have a declared type, such as Long or String, or it can be a Variant array capable of holding different kinds of values. Arrays may have one dimension or several. They are a natural fit for batches of worksheet values, calculations, and data whose positions have meaning.

A Collection is a built-in VBA object that stores items in an ordered group. You add items with Add, retrieve them by position or by an optional string key, and remove them with Remove. Collections are convenient when the group changes size and you want a straightforward object-based interface.

A Dictionary stores key-and-value pairs. Each key identifies one associated value, so code can ask for the value belonging to a particular customer ID, product code, or other identifier. In Excel VBA, the commonly used Scripting.Dictionary is supplied by the Windows scripting runtime rather than being a core VBA language type.

How Arrays Work in Excel VBA

Arrays suit data that is naturally represented as rows, columns, or indexed sequences. You can declare a fixed-size array, or declare a dynamic array and allocate its bounds later with ReDim. You can inspect its actual bounds with LBound and UBound rather than assuming that its first element is always zero or one.

For example, this procedure creates a dynamic array, fills three positions, and prints their contents. The comments show the array’s bounds and the purpose of each operation.

Sub ExampleArray()
    Dim scores() As Long
    Dim i As Long

    ' Allocate positions 1 through 3.
    ReDim scores(1 To 3)
    scores(1) = 82
    scores(2) = 91
    scores(3) = 76

    ' Visit each valid position.
    For i = LBound(scores) To UBound(scores)
        Debug.Print scores(i)
    Next i
End Sub

Arrays are especially useful when moving worksheet data into memory for processing. A multi-cell Range.Value or Range.Value2 result is generally returned as a two-dimensional Variant array, even when the selected range has only one column. The row and column bounds should be checked with LBound and UBound. A single-cell range, by contrast, returns a scalar value rather than a two-dimensional array, so code that handles both cases may need to account for that difference.

Dynamic arrays can be resized with ReDim. ReDim Preserve keeps existing values while changing the size of an array, but VBA limits which bounds can be changed while preserving data: for a multidimensional array, only the last dimension can be resized with Preserve. If an algorithm needs to grow rows repeatedly while retaining all values, consider a different design, such as collecting items first and then copying them into a final array.

How Collections Work

A Collection is often the simplest choice for a changing list. Add appends an item by default, and the optional Before or After argument can place it at a position relative to an existing item. Items are read by their one-based position, or by a key if one was supplied when the item was added. Remove accepts a position or key.

Sub ExampleCollection()
    Dim names As Collection
    Dim item As Variant

    Set names = New Collection
    names.Add "Ava"
    names.Add "Noah"
    names.Add "Mina", "staff-17"

    ' Keys let code identify an item without its position.
    Debug.Print names("staff-17")

    For Each item In names
        Debug.Print item
    Next item
End Sub

Collection keys are optional and are unique within that Collection. Adding an item with a key that is already present raises an error; the Collection does not provide a built-in Exists method to check first. You can handle that with error handling or choose a Dictionary when frequent membership checks are central to the task. A Collection can store objects as well as values; when adding an object, use the Set keyword for the item argument.

Removing an item changes the positions of later items. Therefore, a loop that removes elements while counting upward through the original indexes can skip items or run into an invalid position. A common approach is to loop backward when removing by index, or to build a new Collection containing only the items to keep.

How Dictionaries Work

A Dictionary associates a key with a value and exposes methods and properties useful for lookup. Add inserts a key-value pair, Exists checks whether a key is present, and Item retrieves or assigns its value. Keys must be unique: adding an existing key with Add raises an error, while assigning through Item can create or replace an entry. Keys and values can be inspected with Keys and Items.

Sub ExampleDictionary()
    Dim counts As Object

    ' Late binding avoids setting a VBA reference on Windows.
    Set counts = CreateObject("Scripting.Dictionary")
    counts.CompareMode = vbTextCompare

    counts.Add "P-104", 2
    counts("P-208") = 1

    If counts.Exists("p-104") Then
        counts("p-104") = counts("p-104") + 1
    End If

    Debug.Print counts("P-104")
End Sub

In this example, the result printed is 3. The text comparison mode makes key comparison case-insensitive, so “p-104” matches the stored key “P-104”. Set CompareMode before adding entries; changing it after the Dictionary contains data can raise an error. If case-sensitive identifiers matter, use the appropriate comparison mode instead.

Early binding is another option on Windows: set a reference to Microsoft Scripting Runtime in the Visual Basic Editor’s Tools > References dialog and declare a Scripting.Dictionary variable. This gives the editor access to the object’s type information. Late binding, as in the example, avoids a project reference but declares the variable as Object, so the editor cannot provide the same compile-time type checking.

Choosing by Access Pattern

Think first about the operation the code performs most often. A structure that makes the important operation clear is usually a better choice than one selected from a general claim about speed.

Choose an array when positions or row-and-column coordinates matter, when values have a consistent type, or when processing a worksheet range in a batch.
Choose a Collection when you need an ordered, changeable group and mostly add, enumerate, or retrieve items by position.
Choose a Dictionary when code repeatedly asks whether an identifier exists or needs the value for a particular key.
Use more than one structure when the problem has distinct needs—for example, keep a list in an array and a separate Dictionary for fast key-based association.

For a small list that is scanned once, a simple array or Collection may be easier than introducing a Dictionary. For a large set of records that must be matched repeatedly by ID, a Dictionary can avoid writing a search loop for every lookup. Actual performance depends on data size, how the code is written, and the work being done, so avoid treating any one structure as universally fastest.

Comparison at a Glance

The following differences are useful when making a first decision. They describe the usual VBA interfaces; they do not mean that a structure cannot be adapted to other tasks.

Addressing: arrays use numeric indexes; Collections use indexes and optional keys; Dictionaries use keys.
Membership check: arrays generally require a search or separate tracking; Collections have no built-in Exists method; Dictionaries provide Exists.
Resizing: dynamic arrays use ReDim; Collections and Dictionaries grow as items are added.
Ordering: arrays have explicit positions, and Collections preserve their item order. Do not rely on a Dictionary’s enumeration order as a sorting or presentation guarantee.
Value types: arrays can be declared with a specific element type; Collections and Dictionaries hold items or values through object-style interfaces and can accommodate mixed content.
Portability: arrays and Collections are standard VBA features. Scripting.Dictionary has platform and reference considerations.

Working with Worksheet Data

For many Excel macros, the most useful pattern is to read a range into an array, process the values in memory, and write a result back to the sheet in a batch. Repeatedly reading or writing individual cells can add overhead and make code harder to manage. An array is a good fit for the rectangular data transfer; a Dictionary can complement it when rows need to be found by a unique column such as an employee number.

For instance, code can read a two-column table into a Variant array, use the first column as keys in a Dictionary, and store the second column as the associated value. Before using a worksheet field as a key, decide how blanks, duplicate identifiers, text-versus-number values, and letter case should be treated. A Dictionary cannot resolve ambiguous duplicate IDs on its own: the macro must define whether to keep the first value, replace it, collect multiple values, or report the duplicate.

When writing an array back to a range, its dimensions need to match the target range. A two-dimensional array sized for 20 rows and 3 columns corresponds to a 20-by-3 range. Check bounds and dimensions explicitly, especially if data might be empty or a range might contain only one cell.

Common Errors and Platform Considerations

Several recurring mistakes are easy to prevent:

Assuming an array starts at zero or one without checking its declared bounds or using LBound.
Using ReDim Preserve to resize a dimension that VBA does not permit it to preserve.
Adding a duplicate Collection key or Dictionary key without deciding how duplicates should be handled.
Reading a missing Dictionary key without first checking Exists, which can create an entry when accessed through Item or otherwise produce unexpected results depending on the operation.
Changing a Dictionary’s CompareMode after entries have already been added.
Relying on a Dictionary to return keys in a desired sorted order. Sort the keys separately if presentation order matters.

On Windows, CreateObject(“Scripting.Dictionary”) is a common late-bound approach. Mac Excel does not support the same Windows COM/ActiveX environment, so this creation method and the Microsoft Scripting Runtime reference should not be assumed to work there. If a macro must run on both Windows and Mac, test its dependencies on both platforms and consider arrays, Collections, or a cross-platform custom lookup implementation. Excel for the web does not run VBA macros; use desktop Excel to run VBA.

Before running any macro that changes workbook contents, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the simplest way to preserve the original data if the code produces an unexpected result.

Frequently Asked Questions

Is a Dictionary faster than an array in VBA?
Not for every task. A Dictionary is designed for key-based lookup, while an array is a compact fit for indexed data and sequential processing. The size of the data and the operations performed affect performance; choose based on access pattern and measure a real workload if speed is critical.

Can a VBA Collection have duplicate keys?
No. A Collection can contain multiple items without keys, but supplied keys must be unique. Adding another item with an existing key raises an error. Collections do not include a built-in Exists method.

Does a Dictionary keep items in insertion order?
Do not depend on Dictionary enumeration order as a sorting or display guarantee. If a specific order matters, keep an ordered list separately or sort the keys before presenting them.

Can I use Scripting.Dictionary on a Mac?
Do not assume that the Windows Scripting.Dictionary COM object or its reference is available in Mac Excel. For macros intended to run on both platforms, use a supported alternative or a deliberately cross-platform implementation, and verify the macro in the target Excel versions.

Should I use an array or a Collection for worksheet rows?
Use an array when rows form a rectangular block you process by row and column, especially when transferring values in batches. A Collection is more suitable when you need a changing ordered group of individual items. A Dictionary can be added when rows must be retrieved repeatedly by a unique field.

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.