VbaObjectsIntermediate

Scripting.Dictionary

Stores key-value pairs and is usually the better choice than Collection for lookup-heavy tasks.

Review the syntaxStudy the examplesOpen the coding app
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
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 key-value pairs and is usually the better choice than Collection for lookup-heavy tasks.

Scripting.Dictionary is the go-to VBA container when you need fast key lookups, duplicate detection, grouping counts, or joins across worksheet data. It is often better than Collection for report automation, but it introduces an external object dependency and case-sensitivity choices that should be set deliberately.

Quick reference

Syntax

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

See it in practice

Examples

1

Count repeated customer codes

Dim dict As Object
Dim code As String
Set dict = CreateObject("Scripting.Dictionary")

code = Trim$(Range("A2").Value)
If dict.Exists(code) Then
    dict(code) = dict(code) + 1
Else
    dict.Add code, 1
End If

Dictionary is ideal for grouping and frequency tasks in imported workbook data.

Debug faster

Common Errors

1

LogicError

Cause: Assuming key comparisons are case-insensitive without setting CompareMode.

Fix: Set CompareMode before adding items when key case behavior matters.

Runtime support

Compatibility

Excel desktop VBA

Late binding via CreateObject avoids requiring a project reference during distribution.

Source: Microsoft Learn Office VBA reference

Common questions

Frequently Asked Questions

Stores key-value pairs and is usually the better choice than Collection for lookup-heavy tasks.

LogicError: Set CompareMode before adding items when key case behavior matters.