Yes. A VBA Scripting.Dictionary can hold multiple items for one key if you store a container—most often a Collection—as that key’s value, then add each item to the container. The dictionary still has one entry per key; the collection inside it holds the many values.
How multiple items per key work
A dictionary maps each unique key to one item. It does not create a separate entry each time you add the same key: calling Add again with an existing key raises an error. Nor does assigning a new value to an existing key append to the old value; it replaces that value.
The useful distinction is between the dictionary’s item and the data inside that item. If the item is a Collection, the dictionary can point a key to that collection, and the collection can contain many values. For example, the key Fruit might point to a collection containing Apple, Pear, and another Apple. Repeated values are allowed in a collection, and it retains the order in which they were added.
This pattern is often called a one-to-many mapping or grouping. It is handy when one category, customer, project, or identifier can be associated with several rows of data. The dictionary provides quick access to the group by key; the container provides access to the values in that group.
Choose the right container
A Collection is a good default when you need an ordered list and may have repeated values. It is built into VBA, so you do not need to create another reference for the container. Add values with .Add, check the number with .Count, and retrieve an item by its one-based position.
- Collection: A straightforward choice for an ordered list that can contain duplicates.
- Array: Useful when you need array operations or a compact structure, but growing an array as items arrive requires extra code, commonly involving
ReDim Preserve. - Nested dictionary: Use one when each group needs unique subkeys, such as a customer key containing one value per year. A second dictionary also lets you check whether a subkey already exists.
- Custom class: Consider one when each grouped entry has several named fields, such as a product ID, description, and quantity, rather than one simple value.
Pick the simplest structure that matches what you must do later. If you only need to collect values and loop through them, a collection is usually easier to understand than a dynamically resized array or a second dictionary.
Build a dictionary of collections
This Windows Excel example reads a key from column A and a value from column B on a worksheet named Data. It creates a collection the first time it sees a key and adds each row’s value to the appropriate collection. The sample range contains six data rows, from row 2 through row 7.
Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z. The code uses late binding, so it does not require adding a reference in the Visual Basic Editor.
Sub GroupValuesByKey()
Dim ws As Worksheet
Dim data As Variant
Dim groups As Object
Dim bucket As Collection
Dim r As Long
Dim key As String
Dim k As Variant
Dim item As Variant
' Read keys from column A and values from column B.
Set ws = ThisWorkbook.Worksheets("Data")
data = ws.Range("A2:B7").Value2
' Create a late-bound Scripting.Dictionary.
Set groups = CreateObject("Scripting.Dictionary")
groups.CompareMode = vbTextCompare
For r = 1 To UBound(data, 1)
key = Trim$(CStr(data(r, 1)))
' Ignore blank or whitespace-only keys.
If Len(key) > 0 Then
If Not groups.Exists(key) Then
Set bucket = New Collection
groups.Add key, bucket
End If
Set bucket = groups(key)
bucket.Add data(r, 2)
End If
Next r
' Print each key and its collected values.
For Each k In groups.Keys
Set bucket = groups(k)
Debug.Print CStr(k) & " (" & bucket.Count & ")"
For Each item In bucket
Debug.Print " " & CStr(item)
Next item
Next k
End Sub
For example, if the rows are Fruit–Apple, Tool–Hammer, Fruit–Pear, Fruit–Apple, Tool–Saw, and Fruit–Plum, the Fruit collection has four items and the Tool collection has two. The repeated Apple is kept because the code adds every matching row. To see the output, open the Visual Basic Editor’s Immediate window using View > Immediate Window.
Retrieve and loop through the values
To retrieve a group, use the key to get its collection. Because the dictionary item is an object, assign it with Set: conceptually, Set bucket = groups("Fruit"). You can then use bucket.Count to find the number of values, and bucket(1) to retrieve the first value. Collection positions start at 1, not 0.
For code that should handle a key that may not exist, check groups.Exists(key) before retrieving it. Otherwise, looking up a missing key can fail. When processing every group, loop over groups.Keys and retrieve each collection with Set, as in the example. Then loop over the collection with For Each when you want to process every stored value without relying on an index.
Do not assume the dictionary will return keys in a particular order. If presentation order matters, sort the keys separately or store the desired order in another collection. The values inside each collection, by contrast, remain in the order in which the code added them.
Set key comparison and clean up input
Dictionary key comparison matters when keys are text. In the example, CompareMode = vbTextCompare makes keys such as Fruit and FRUIT compare as equal. Set this property before adding the first key; changing it after the dictionary contains entries can raise an error. With the default binary comparison, differences in letter case are significant.
The example also applies Trim$ to keys, so leading or trailing spaces do not accidentally create separate groups. That is a data-cleaning choice, not a universal rule: if spaces are meaningful in your identifiers, do not trim them. Likewise, converting every key to a string means a numeric value such as 1 and a text value such as 1 can become the same key. Normalize keys deliberately, and avoid converting them if their original types must remain distinct.
Blank keys need a policy too. The sample ignores empty and whitespace-only keys, but you could instead collect them under a deliberate label or report them as invalid input. Decide this before grouping so missing data does not silently become a misleading group.
Common mistakes and troubleshooting
- Adding the same key repeatedly: Create the collection only when
Existsreturns False. Then retrieve it and add the new value. Callinggroups.Add key, valuefor every row does not append to an existing entry. - Replacing the collection: Assigning a new item to a key replaces its current item. Retrieve the existing collection and add to it instead.
- Omitting
Set: When assigning a collection or another object from a dictionary item to an object variable, useSet. Without it, VBA may report an object-related assignment error. - Setting comparison mode too late: Choose
CompareModeimmediately after creating the dictionary and before inserting keys. - Assuming every row is valid: Check for blank keys and consider whether source cells might contain errors or unexpected values. The example assumes ordinary text keys and values.
On Windows, CreateObject("Scripting.Dictionary") is a common late-binding approach. Early binding is also possible by selecting Tools > References in the Visual Basic Editor and checking Microsoft Scripting Runtime; then the code can declare a Scripting.Dictionary variable. Early binding gives access to the library’s type information, while late binding avoids a project reference that may need to be set on another computer.
The Scripting Runtime is a Windows COM component, so this exact creation method is not a portable choice for Excel for Mac. If a workbook must run on Mac, use a built-in VBA structure such as collections, or choose and validate a cross-platform implementation for the workbook’s target Excel versions. Test on the actual operating systems where the macro will run.
When a nested dictionary is better
A collection is ideal when repeated values should remain repeated. If each group needs to hold unique items identified by a second key, use a dictionary inside a dictionary instead. For instance, a department could map to another dictionary keyed by employee ID. Before adding an employee, check whether that ID exists in the inner dictionary; this prevents duplicates by identifier while still allowing multiple employees in the department.
The trade-off is added complexity. You must create the inner dictionary for each new outer key, retrieve it with Set, and decide what to do if an inner key is already present. A nested dictionary stores one item per inner key, so it is not a substitute for a collection when duplicate entries are meaningful. Use it when uniqueness and lookup by a second identifier are requirements, not merely because nesting seems more sophisticated.
Frequently Asked Questions
Can a VBA Dictionary key have more than one value?
Yes, by making the dictionary item a container such as a collection or array. The dictionary itself still stores one item per key; that item contains the multiple values.
Does adding a value to an existing key append it?
No. Add with an existing key raises a duplicate-key error, and assigning a replacement item overwrites the old item. Retrieve the collection already stored at that key and call its Add method.
Can a collection contain duplicate values?
Yes. A VBA collection allows repeated values and preserves their insertion order. If you need to prevent repeats, check the values before adding them or use a nested dictionary keyed by a unique identifier.
How do I get the number of values for a key?
First verify that the key exists, then retrieve its collection with Set and read the collection’s Count property. The dictionary’s own Count reports the number of keys, not the total number of values across all groups.
Will this Scripting.Dictionary code work on Mac?
The Windows CreateObject("Scripting.Dictionary") approach depends on a Windows COM component and should not be treated as Mac-compatible. For Mac-targeted VBA, use a built-in structure such as Collection or a tested cross-platform alternative.
Related
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
- Data Validation Interview Questions and Strong Answers
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Delete Rows Based on CriteriaTo delete Excel rows that meet a condition, loop through the relevant rows from bottom to top and…
- VBA Copy and Paste Data Between Excel SheetsTo copy a range from one Excel worksheet to another with VBA, use a macro that names both…
- VBA Code to Send an Email With an AttachmentTo send an email with an attachment from Excel VBA on Windows, create an Outlook mail item, add…
- Excel Macro File: How to Save, Open, and Use It SafelyAn Excel macro file is a workbook that can store VBA code to automate tasks; the usual format…
- VBA Dictionary Remove Key: Safe Removal and Common ErrorsTo remove one entry from a VBA Dictionary, call its Remove method with the key: dict.Remove key. If…
- Excel VBA: Insert a Row and Shift Cells DownTo insert a worksheet row in Excel VBA and push the existing rows down, use Rows(rowNumber).Insert Shift:=xlShiftDown on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.