↓Skip to main content

The Dictionary Object in Excel VBA – Definitive Guide

·1458 words·7 mins
Author
Tiposaurus

Table of Contents 

<span class="ez-toc-title-toggle"><a href="#" class="ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle" aria-label="Toggle Table of Content"><span class="ez-toc-js-icon-con"><span class=""><span class="eztoc-hide" style="display:none;">Toggle</span><span class="ez-toc-icon-toggle-span"><svg style="fill: #999;color:#999" xmlns="http://www.w3.org/2000/svg" class="list-377408" width="20px" height="20px" viewBox="0 0 24 24" fill="none"><path d="M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z" fill="currentColor"></path></svg><svg style="fill: #999;color:#999" class="arrow-unsorted-368013" xmlns="http://www.w3.org/2000/svg" width="10px" height="10px" viewBox="0 0 24 24" version="1.2" baseProfile="tiny"><path d="M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z"/></svg></span></span></span></a></span>

1. Introduction
#

This guide aims to provide a comprehensive understanding of the Dictionary object in Excel VBA, including an overview of its features and the advantages it offers over other data structures like arrays and collections. We will also cover how to include the necessary library, create a dictionary, add items to it, access and modify items, remove items, and search for items. Additionally, we will explore some practical examples of using the Dictionary object in Excel VBA.

2. Overview: When and Why to Use a Dictionary Object
#

A Dictionary object is a powerful data structure in Excel VBA that allows you to store and manage data in key-value pairs. It provides unique advantages over other data structures like arrays and collections, making it an ideal choice in specific scenarios:

  • Unique keys: Dictionary object ensures that all keys are unique, preventing duplicate data entry and making data management more efficient.
  • Fast data retrieval: Dictionary object enables quick data retrieval using keys, providing faster access to data compared to arrays and collections.
  • Dynamic resizing: Unlike arrays, Dictionary objects can be resized dynamically, making them more flexible for managing data sets with an unknown size.

3. Including the Library
#

Before you can use a Dictionary object in your VBA project, you need to include the Microsoft Scripting Runtime library. To do this, follow these steps:

  1. Open the Visual Basic for Applications (VBA) editor by pressing ALT + F11.
  2. Go to the Tools menu and click References.
  3. Scroll down the list, find Microsoft Scripting Runtime, and check the box beside it.
  4. Click OK to close the dialog box and include the library in your project.

4. Creating a Dictionary Object
#

To create a Dictionary object in VBA, declare a variable of type Scripting.Dictionary and then use the New keyword to instantiate it:

Dim myDictionary As Scripting.Dictionary
Set myDictionary = New Scripting.Dictionary

5. Adding Items to a Dictionary
#

To add items to a Dictionary object, use the Add method, providing a unique key and a value:

myDictionary.Add Key:="fruit", Item:="apple"
myDictionary.Add Key:="color", Item:="red"

6. Accessing and Modifying Dictionary Items
#

To access an item in a Dictionary object, use its key as an index:

Dim value As Variant
value = myDictionary("fruit") ' Returns "apple"

To modify an item in a Dictionary object, use its key to assign a new value:

myDictionary("fruit") = "banana"
<h1>
  <span class="ez-toc-section" id="7_Removing_Items_from_a_Dictionary"></span>7. Removing Items from a Dictionary<span class="ez-toc-section-end"></span>
</h1>

<p>
  To remove an item from a Dictionary object, use the <code>Remove</code> method with the item&#8217;s key:
</p>

<pre class="EnlighterJSRAW" data-enlighter-language="visualbasic">myDictionary.Remove "fruit"</pre>

<p>
  To remove all items from a Dictionary object, use the <code>RemoveAll</code> method:
</p>

<pre class="EnlighterJSRAW" data-enlighter-language="visualbasic">myDictionary.RemoveAll</pre>

<h1>
  <span class="ez-toc-section" id="8_Searching_for_Items_in_a_Dictionary"></span>8. Searching for Items in a Dictionary<span class="ez-toc-section-end"></span>
</h1>

<p>
  To check if a key exists in a Dictionary object, use the <code>Exists</code> method, which returns a Boolean value:
</p>

<div class="bg-black rounded-md mb-4">
  <div class="flex items-center relative text-gray-200 bg-gray-800 px-4 py-2 text-xs font-sans justify-between rounded-t-md">
    <pre class="EnlighterJSRAW" data-enlighter-language="visualbasic">Dim keyExists As Boolean

keyExists = myDictionary.Exists(“fruit”) ’ Returns True if the key exists

    <p>
      To find a key or value in a Dictionary object, you can loop through its keys or items:
    </p>
    
    <div class="bg-black rounded-md mb-4">
      <div class="flex items-center relative text-gray-200 bg-gray-800 px-4 py-2 text-xs font-sans justify-between rounded-t-md">
        <pre class="EnlighterJSRAW" data-enlighter-language="visualbasic">Dim key As Variant

Dim searchKey As String Dim foundKey As Boolean

searchKey = “fruit” foundKey = False

For Each key In myDictionary.Keys If key = searchKey Then foundKey = True Exit For End If Next key

If foundKey Then MsgBox “Key found” Else MsgBox “Key not found” End If 

        <h1>
          <span class="ez-toc-section" id="9_Looping_Through_Dictionary_Keys_and_Items"></span>9. Looping Through Dictionary Keys and Items<span class="ez-toc-section-end"></span>
        </h1>
        
        <p>
          To loop through the keys or items in a Dictionary object, use the <code>For Each</code> loop with the <code>Keys</code> or <code>Items</code> properties:
        </p>
        
        <pre class="EnlighterJSRAW" data-enlighter-language="visualbasic">Dim key As Variant

Dim item As Variant

For Each key In myDictionary.Keys MsgBox “Key: " & key & “, Value: " & myDictionary(key) Next key

For Each item In myDictionary.Items MsgBox “Value: " & item Next item

        <h1>
          <span class="ez-toc-section" id="10_Comparing_Dictionary_with_Arrays_and_Collections"></span>10. Comparing Dictionary with Arrays and Collections<span class="ez-toc-section-end"></span>
        </h1>
        
        <p>
          Here is a comparison of Dictionary objects, arrays, and collections in Excel VBA:
        </p>
        
        <ul>
          <li>
            <strong>Arrays:</strong> Arrays are simple, fixed-size data structures that store elements of the same data type. They are fast and memory-efficient but lack built-in methods for managing data (e.g., adding, removing, or searching elements). Arrays are ideal for handling small, static data sets with known dimensions.
          </li>
          <li>
            <strong>Collections:</strong> Collections are dynamic data structures that can store elements of different data types. They provide basic built-in methods for adding, removing, and accessing elements but do not support unique keys or fast data retrieval. Collections are suitable for handling dynamic data sets with simple data management requirements.
          </li>
          <li>
            <strong>Dictionary:</strong> Dictionary objects are dynamic data structures that store key-value pairs, ensuring unique keys and allowing fast data retrieval. They provide a variety of built-in methods for managing data and are ideal for handling complex data sets with unique identifiers or keys.
          </li>
        </ul>
        
        <h1>
          <span class="ez-toc-section" id="11_Practical_Examples"></span>11. Practical Examples<span class="ez-toc-section-end"></span>
        </h1>
        
        <h2>
          <span class="ez-toc-section" id="Example_1_Counting_Word_Occurrences"></span>Example 1: Counting Word Occurrences<span class="ez-toc-section-end"></span>
        </h2>
        
        <p>
          The following example demonstrates how to use a Dictionary object to count the occurrences of words in a given text:
        </p>
        
        <pre class="EnlighterJSRAW" data-enlighter-language="visualbasic">Sub CountWordOccurrences()
Dim text As String
Dim words() As String
Dim word As String
Dim wordCount As Scripting.Dictionary

text = "This is an example text. This text is just an example."
words = Split(text, " ")
Set wordCount = New Scripting.Dictionary

For Each word In words
    word = LCase(word)
    If wordCount.Exists(word) Then
        wordCount(word) = wordCount(word) + 1
    Else
        wordCount.Add word, 1
    End If
Next word

For Each word In wordCount.Keys
    MsgBox "Word: " & word & ", Count: " & wordCount(word)
Next word

End Sub

        <h2>
          <span class="ez-toc-section" id="Example_2_Storing_Unique_Items_from_a_Range"></span>Example 2: Storing Unique Items from a Range<span class="ez-toc-section-end"></span>
        </h2>
        
        <p>
          The following example demonstrates how to use a Dictionary object to store unique items from a range in a worksheet:
        </p>
        
        <div class="bg-black rounded-md mb-4">
          <div class="flex items-center relative text-gray-200 bg-gray-800 px-4 py-2 text-xs font-sans justify-between rounded-t-md">
            <pre class="EnlighterJSRAW" data-enlighter-language="visualbasic">Sub StoreUniqueItems()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim uniqueItems As Scripting.Dictionary

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("A1:A10")
Set uniqueItems = New Scripting.Dictionary

For Each cell In rng
    If Not uniqueItems.Exists(cell.Value) Then
        uniqueItems.Add cell.Value, cell.Value
    End If
Next cell

For Each cell In uniqueItems.Keys
    MsgBox "Unique Item: " & cell
Next cell

End Sub

            <h2>
              <span class="ez-toc-section" id="Example_3_Merging_Two_Dictionaries"></span>Example 3: Merging Two Dictionaries<span class="ez-toc-section-end"></span>
            </h2>
            
            <p>
              The following example demonstrates how to merge two Dictionary objects:
            </p>
            
            <pre class="EnlighterJSRAW" data-enlighter-language="visualbasic">Sub MergeDictionaries()
Dim dict1 As Scripting.Dictionary
Dim dict2 As Scripting.Dictionary
Dim mergedDict As Scripting.Dictionary
Dim key As Variant

' Create and populate dict1
Set dict1 = New Scripting.Dictionary
dict1.Add "fruit", "apple"
dict1.Add "color", "red"

' Create and populate dict2
Set dict2 = New Scripting.Dictionary
dict2.Add "animal", "cat"
dict2.Add "city", "New York"

' Merge dictionaries
Set mergedDict = New Scripting.Dictionary
For Each key In dict1.Keys
    mergedDict.Add key, dict1(key)
Next key

For Each key In dict2.Keys
    If Not mergedDict.Exists(key) Then
        mergedDict.Add key, dict2(key)
    End If
Next key

' Display merged dictionary
For Each key In mergedDict.Keys
    MsgBox "Key: " & key & ", Value: " & mergedDict(key)
Next key

End Sub

            <h1>
              <span class="ez-toc-section" id="12_Conclusion"></span>12. Conclusion<span class="ez-toc-section-end"></span>
            </h1>
            
            <p>
              The Dictionary object in Excel VBA is a powerful and versatile data structure that offers unique advantages over arrays and collections, such as unique keys, fast data retrieval, and dynamic resizing. By understanding how to create, manipulate, and search Dictionary objects, you can effectively manage complex data sets in your VBA projects. This definitive guide has provided you with the knowledge and tools necessary to leverage the Dictionary object in your Excel VBA applications.
            </p>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>