In Excel VBA, use a Scripting.Dictionary when you need unique keys, quick key-based lookups, or an Exists check; use a Collection when you mainly need an ordered group of items and want a built-in VBA type. A Dictionary is convenient for matching keys to values, while a Collection is simpler and does not require a separate scripting component.
What the two VBA objects are
Both objects hold groups of values in memory while a macro runs. Neither is an Excel worksheet feature: you create and use them in VBA code, and their contents do not automatically save to the workbook. They are useful when a macro needs to gather records, remove duplicates, count occurrences, or look up a value repeatedly.
A Collection is part of VBA. It holds items in the order they are added and lets code retrieve an item by its numeric position or, if supplied, by a string key. A Scripting.Dictionary is a scripting object that associates each unique key with an item. It has methods such as Exists and properties such as Keys and Items, which make many lookup tasks more direct.
Both can hold different kinds of values, including numbers, text, objects, and arrays in suitable contexts. The important distinction is how you identify and retrieve entries: a Collection is primarily an ordered list; a Dictionary is primarily a key-to-value mapping.
How a Dictionary works
A Dictionary stores one item for each key. A key might be a customer ID, a product code, or a normalized name. The key must be unique within that Dictionary; trying to use Add with a key that is already present raises an error. Use Exists first when duplicate keys are expected, or deliberately update the existing item.
For example, a macro that processes transaction rows can use an account number as the key and a running total as the item. When another row has the same account number, the code retrieves and updates the existing total. This avoids searching through a separate list of account numbers for every transaction.
Dictionary keys are not limited to worksheet row numbers, and values do not have to be unique. Several keys can point to the same value. The Count property reports the number of key-item pairs. The Keys and Items properties return arrays, while For Each over a Dictionary iterates through its keys. Do not depend on a particular enumeration order when the sequence matters; sort the results explicitly or store an order value.
Text comparison is configurable through CompareMode. Set it before adding entries: vbBinaryCompare distinguishes upper- and lowercase text, while vbTextCompare treats text case-insensitively for comparisons. Changing the mode after entries have been added can raise an error. Decide how to treat spaces, capitalization, and other differences before using text as a key.
How a Collection works
A Collection is often the right choice for a straightforward sequence of items. Add entries with Add, read them by their one-based position, and loop through them with For Each. An optional key lets code retrieve a particular item by name, but a Collection does not offer a built-in Exists method or a direct way to retrieve all keys.
That missing existence check affects everyday code. Asking a Collection for a nonexistent key raises an error, so checking whether a key is present generally requires a small error-handling routine or a separate lookup structure. If missing keys are common, a Dictionary usually makes the intent clearer and reduces that extra code.
Collections also suit cases where insertion order is important and the macro naturally processes items from first to last. They are built into VBA, so creating one does not require a reference to the Microsoft Scripting Runtime. A Collection is not, however, a drop-in replacement for a Dictionary: its key handling and lookup methods differ.
Creating a Dictionary in Excel for Windows
For Microsoft 365 Excel on Windows, late binding is a convenient way to create a Dictionary without setting a project reference. Declare the variable as Object and create the scripting object at runtime. This can make a workbook easier to move among Windows computers that have the scripting component available, because the VBA project does not need a manually selected reference.
Early binding is another option. In the VBA editor, choose Tools > References, select Microsoft Scripting Runtime, and declare the variable as Scripting.Dictionary. Early binding provides the editor with the object’s methods and properties and can catch some spelling mistakes while coding. It also means the reference must resolve on the computer opening the workbook; a missing reference can cause compile errors.
With either approach, handle duplicates intentionally. The default item assignment can create an entry if the key is new and replace the item if the key already exists, whereas Add is for adding a new key and raises an error for a duplicate. Use the operation that reflects the macro’s logic rather than relying on an accidental overwrite.
Example: count repeated values in a column
This example counts nonblank names in column A on a worksheet named Data, starting at A2, then writes each distinct name and its count to columns D and E. It uses late binding and ignores leading and trailing spaces. It assumes the cells contain ordinary text rather than worksheet errors. The output order is not promised, because Dictionary enumeration order should not be treated as a sorting rule.
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.
Sub CountNamesWithDictionary()
Dim ws As Worksheet
Dim dict As Object
Dim lastRow As Long
Dim cell As Range
Dim key As String
Dim itemKey As Variant
Dim outRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare ' Compare names without case differences.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub ' No data rows to count.
For Each cell In ws.Range("A2:A" & lastRow)
key = Trim$(CStr(cell.Value2))
If Len(key) > 0 Then
If dict.Exists(key) Then
dict(key) = CLng(dict(key)) + 1
Else
dict.Add key, 1
End If
End If
Next cell
ws.Range("D1:E1").Value = Array("Name", "Count")
outRow = 2
For Each itemKey In dict.Keys
ws.Cells(outRow, "D").Value = itemKey
ws.Cells(outRow, "E").Value = dict(itemKey)
outRow = outRow + 1
Next itemKey
End Sub
Because the example uses vbTextCompare, names such as “Riley” and “RILEY” are counted together. Remove that setting or use vbBinaryCompare if capitalization should distinguish keys. The code trims spaces but does not standardize punctuation or internal spaces, so “Riley Chen” and “Riley Chen” remain different keys.
Choosing the right one
Choose based on how the macro needs to access its data, not on the idea that one object is universally better. A Dictionary is a natural fit when the key is the important identifier. A Collection is a natural fit when position and insertion order are more important than testing for a particular key.
- Choose a Dictionary for duplicate detection, frequency counts, key-to-value lookups, and grouping data by an identifier.
- Choose a Collection for a simple ordered sequence, especially when you only need to add items and loop through them.
- Choose a Collection when avoiding an external scripting dependency matters and key-based existence checks are not central.
- Choose a worksheet range, array, or another structure when the data must be saved, displayed, sorted, or exchanged outside the running macro.
For example, a list of selected worksheets that the macro will visit in order can be a Collection. A map from each worksheet name to a calculated total is usually clearer as a Dictionary. For a small fixed number of items, either object may work; clarity and platform compatibility matter more than pursuing a theoretical speed difference.
Common problems and practical limits
A common Dictionary error is attempting to add a key twice. If repeated keys are normal, use Exists and update the stored item, as in the counting example. Another frequent problem is setting CompareMode after data has been added; set it immediately after creating the object and before inserting keys.
Think carefully about how keys are formed. A numeric identifier stored in one row as a number and in another as text may not behave like the same key. Likewise, extra spaces, different punctuation, and different date formats can produce separate entries. Normalize the value consistently before using it as a key. Avoid converting identifiers to numbers if leading zeros are meaningful, as with codes such as 0042.
With a Collection, duplicate keys can also cause an error, and the object offers no direct Exists test. If code uses keys that may be absent, it needs a deliberate missing-key strategy rather than assuming every lookup succeeds. Avoid using a broad error handler to hide unrelated errors; keep any error handling narrow and purposeful.
Neither object automatically validates worksheet data. A cell containing an Excel error value can make a conversion such as CStr fail, and blank values may need special treatment. Decide whether to skip, report, or handle such rows before processing a large range. Also remember that these objects live in memory: editing a Dictionary does not write changes back to the source cells unless the macro explicitly writes results to the sheet.
Performance and platform compatibility
For larger data sets with repeated key lookups, a Dictionary is often more convenient and can be faster than repeatedly scanning a Collection or worksheet range. The actual result depends on the code, data size, and operations, so there is no single speed claim that applies to every workbook. For a small list, the simplest readable implementation is generally the better choice.
The Windows scripting object is a COM component. Late binding avoids a project reference, but it does not make that component available in every Excel environment. Excel for the web does not run VBA macros. On Mac, VBA is available, but Windows COM automation and the Scripting Runtime approach should not be assumed to work. If a workbook must run on Mac or across platforms, test it in the actual target environment and consider a built-in Collection, arrays, or a custom VBA class instead.
A Collection is therefore a useful portability fallback, but it is not always a straightforward substitute when the code depends on Dictionary features such as Exists, configurable comparison, or retrieving all keys. If you switch structures, redesign the lookup logic and test duplicate and missing-key cases rather than merely changing the declared type.
Frequently Asked Questions
Is Dictionary better than Collection in VBA?
Not in every situation. Dictionary is usually more convenient for unique-key lookup, existence checks, and counting. Collection is built into VBA and works well as an ordered sequence.
Do I need to add a reference to use Scripting.Dictionary?
No, not when using late binding with CreateObject("Scripting.Dictionary"). Early binding requires selecting Microsoft Scripting Runtime under Tools > References in the VBA editor on Windows.
How do I check whether a Dictionary key exists?
Call its Exists method with the key, such as dict.Exists(key). It returns True when the key is present and False when it is not.
Can a VBA Collection have duplicate keys?
No. Collection keys, when supplied, must be unique; attempting to add a duplicate key raises an error. Items without keys can still hold repeated values.
Can I use Scripting.Dictionary on Mac Excel?
Do not assume the Windows scripting component is available in Mac Excel. For macros that must run on Mac, test the target version and consider a Collection, array, or custom class instead.
Related
- Excel Split Cell Data Into Multiple Columns
- Excel Split Cell Into First and Last Name: A Clear Guide
- Excel Split Cell by Space Delimiter: Methods and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find Method in Excel: Syntax and ExamplesIn Excel VBA, the Range.Find method searches a specified range and returns the first matching cell as a…
- Excel Macro Arrays: How VBA Arrays WorkAn Excel macro array is a VBA variable that holds multiple values, so a macro can work with…
- Best Excel Macro Settings for Every Use CaseFor most people using Excel for Microsoft 365 on Windows, the best choice is Disable VBA macros with…
- VBA MsgBox Result: Return Values and Button ChoicesIn VBA, MsgBox returns a VbMsgBoxResult value that identifies which response button the user selected. Store that return…
- Excel VBA: Find Text and Delete the Matching Row SafelyTo find specific text in Excel and delete every row containing an exact match, run a VBA macro…
- Excel VBA AutoFilter: How to Apply Filters with CodeIn Excel VBA, apply AutoFilter to a range that includes its header row, then set the field number…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.