{"id":71,"date":"2023-04-11T23:19:14","date_gmt":"2023-04-11T23:19:14","guid":{"rendered":"https:\/\/www.excelfinancemastery.com\/?p=71"},"modified":"2024-01-26T23:22:30","modified_gmt":"2024-01-26T23:22:30","slug":"the-dictionary-object-in-excel-vba-definitive-guide","status":"publish","type":"post","link":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/","title":{"rendered":"The Dictionary Object in Excel VBA &#8211; Definitive Guide"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_62 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<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><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#1_Introduction\" title=\"1. Introduction\">1. Introduction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#2_Overview_When_and_Why_to_Use_a_Dictionary_Object\" title=\"2. Overview: When and Why to Use a Dictionary Object\">2. Overview: When and Why to Use a Dictionary Object<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#3_Including_the_Library\" title=\"3. Including the Library\">3. Including the Library<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#4_Creating_a_Dictionary_Object\" title=\"4. Creating a Dictionary Object\">4. Creating a Dictionary Object<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#5_Adding_Items_to_a_Dictionary\" title=\"5. Adding Items to a Dictionary\">5. Adding Items to a Dictionary<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#6_Accessing_and_Modifying_Dictionary_Items\" title=\"6. Accessing and Modifying Dictionary Items\">6. Accessing and Modifying Dictionary Items<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#7_Removing_Items_from_a_Dictionary\" title=\"7. Removing Items from a Dictionary\">7. Removing Items from a Dictionary<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#8_Searching_for_Items_in_a_Dictionary\" title=\"8. Searching for Items in a Dictionary\">8. Searching for Items in a Dictionary<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#9_Looping_Through_Dictionary_Keys_and_Items\" title=\"9. Looping Through Dictionary Keys and Items\">9. Looping Through Dictionary Keys and Items<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#10_Comparing_Dictionary_with_Arrays_and_Collections\" title=\"10. Comparing Dictionary with Arrays and Collections\">10. Comparing Dictionary with Arrays and Collections<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#11_Practical_Examples\" title=\"11. Practical Examples\">11. Practical Examples<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#Example_1_Counting_Word_Occurrences\" title=\"Example 1: Counting Word Occurrences\">Example 1: Counting Word Occurrences<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#Example_2_Storing_Unique_Items_from_a_Range\" title=\"Example 2: Storing Unique Items from a Range\">Example 2: Storing Unique Items from a Range<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#Example_3_Merging_Two_Dictionaries\" title=\"Example 3: Merging Two Dictionaries\">Example 3: Merging Two Dictionaries<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#12_Conclusion\" title=\"12. Conclusion\">12. Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"1_Introduction\"><\/span>1. Introduction<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>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.<\/p>\n<h1><span class=\"ez-toc-section\" id=\"2_Overview_When_and_Why_to_Use_a_Dictionary_Object\"><\/span>2. Overview: When and Why to Use a Dictionary Object<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>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:<\/p>\n<ul>\n<li><strong>Unique keys:<\/strong> Dictionary object ensures that all keys are unique, preventing duplicate data entry and making data management more efficient.<\/li>\n<li><strong>Fast data retrieval:<\/strong> Dictionary object enables quick data retrieval using keys, providing faster access to data compared to arrays and collections.<\/li>\n<li><strong>Dynamic resizing:<\/strong> Unlike arrays, Dictionary objects can be resized dynamically, making them more flexible for managing data sets with an unknown size.<\/li>\n<\/ul>\n<h1><span class=\"ez-toc-section\" id=\"3_Including_the_Library\"><\/span>3. Including the Library<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>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:<\/p>\n<ol>\n<li>Open the Visual Basic for Applications (VBA) editor by pressing <code>ALT + F11<\/code>.<\/li>\n<li>Go to the <code>Tools<\/code> menu and click <code>References<\/code>.<\/li>\n<li>Scroll down the list, find <code>Microsoft Scripting Runtime<\/code>, and check the box beside it.<\/li>\n<li>Click <code>OK<\/code> to close the dialog box and include the library in your project.<\/li>\n<\/ol>\n<h1><span class=\"ez-toc-section\" id=\"4_Creating_a_Dictionary_Object\"><\/span>4. Creating a Dictionary Object<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>To create a Dictionary object in VBA, declare a variable of type <code>Scripting.Dictionary<\/code> and then use the <code>New<\/code> keyword to instantiate it:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Dim myDictionary As Scripting.Dictionary\nSet myDictionary = New Scripting.Dictionary<\/pre>\n<h1><span class=\"ez-toc-section\" id=\"5_Adding_Items_to_a_Dictionary\"><\/span>5. Adding Items to a Dictionary<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>To add items to a Dictionary object, use the <code>Add<\/code> method, providing a unique key and a value:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">myDictionary.Add Key:=\"fruit\", Item:=\"apple\"\nmyDictionary.Add Key:=\"color\", Item:=\"red\"<\/pre>\n<h1><span class=\"ez-toc-section\" id=\"6_Accessing_and_Modifying_Dictionary_Items\"><\/span>6. Accessing and Modifying Dictionary Items<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>To access an item in a Dictionary object, use its key as an index:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Dim value As Variant\nvalue = myDictionary(\"fruit\") ' Returns \"apple\"<\/pre>\n<p>To modify an item in a Dictionary object, use its key to assign a new value:<\/p>\n<div class=\"bg-black rounded-md mb-4\">\n<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\">\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">myDictionary(\"fruit\") = \"banana\"<\/pre>\n<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>\n<p>To remove an item from a Dictionary object, use the <code>Remove<\/code> method with the item&#8217;s key:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">myDictionary.Remove \"fruit\"<\/pre>\n<p>To remove all items from a Dictionary object, use the <code>RemoveAll<\/code> method:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">myDictionary.RemoveAll<\/pre>\n<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>\n<p>To check if a key exists in a Dictionary object, use the <code>Exists<\/code> method, which returns a Boolean value:<\/p>\n<div class=\"bg-black rounded-md mb-4\">\n<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\">\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Dim keyExists As Boolean\nkeyExists = myDictionary.Exists(\"fruit\") ' Returns True if the key exists<\/pre>\n<p>To find a key or value in a Dictionary object, you can loop through its keys or items:<\/p>\n<div class=\"bg-black rounded-md mb-4\">\n<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\">\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Dim key As Variant\nDim searchKey As String\nDim foundKey As Boolean\n\nsearchKey = \"fruit\"\nfoundKey = False\n\nFor Each key In myDictionary.Keys\n    If key = searchKey Then\n        foundKey = True\n        Exit For\n    End If\nNext key\n\nIf foundKey Then\n    MsgBox \"Key found\"\nElse\n    MsgBox \"Key not found\"\nEnd If\n<\/pre>\n<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>\n<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>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Dim key As Variant\nDim item As Variant\n\nFor Each key In myDictionary.Keys\n    MsgBox \"Key: \" &amp; key &amp; \", Value: \" &amp; myDictionary(key)\nNext key\n\nFor Each item In myDictionary.Items\n    MsgBox \"Value: \" &amp; item\nNext item<\/pre>\n<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>\n<p>Here is a comparison of Dictionary objects, arrays, and collections in Excel VBA:<\/p>\n<ul>\n<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>\n<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>\n<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>\n<\/ul>\n<h1><span class=\"ez-toc-section\" id=\"11_Practical_Examples\"><\/span>11. Practical Examples<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<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>\n<p>The following example demonstrates how to use a Dictionary object to count the occurrences of words in a given text:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Sub CountWordOccurrences()\n    Dim text As String\n    Dim words() As String\n    Dim word As String\n    Dim wordCount As Scripting.Dictionary\n\n    text = \"This is an example text. This text is just an example.\"\n    words = Split(text, \" \")\n    Set wordCount = New Scripting.Dictionary\n\n    For Each word In words\n        word = LCase(word)\n        If wordCount.Exists(word) Then\n            wordCount(word) = wordCount(word) + 1\n        Else\n            wordCount.Add word, 1\n        End If\n    Next word\n\n    For Each word In wordCount.Keys\n        MsgBox \"Word: \" &amp; word &amp; \", Count: \" &amp; wordCount(word)\n    Next word\nEnd Sub<\/pre>\n<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>\n<p>The following example demonstrates how to use a Dictionary object to store unique items from a range in a worksheet:<\/p>\n<div class=\"bg-black rounded-md mb-4\">\n<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\">\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Sub StoreUniqueItems()\n    Dim ws As Worksheet\n    Dim rng As Range\n    Dim cell As Range\n    Dim uniqueItems As Scripting.Dictionary\n\n    Set ws = ThisWorkbook.Worksheets(\"Sheet1\")\n    Set rng = ws.Range(\"A1:A10\")\n    Set uniqueItems = New Scripting.Dictionary\n\n    For Each cell In rng\n        If Not uniqueItems.Exists(cell.Value) Then\n            uniqueItems.Add cell.Value, cell.Value\n        End If\n    Next cell\n\n    For Each cell In uniqueItems.Keys\n        MsgBox \"Unique Item: \" &amp; cell\n    Next cell\n\nEnd Sub<\/pre>\n<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>\n<p>The following example demonstrates how to merge two Dictionary objects:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Sub MergeDictionaries()\n    Dim dict1 As Scripting.Dictionary\n    Dim dict2 As Scripting.Dictionary\n    Dim mergedDict As Scripting.Dictionary\n    Dim key As Variant\n\n    ' Create and populate dict1\n    Set dict1 = New Scripting.Dictionary\n    dict1.Add \"fruit\", \"apple\"\n    dict1.Add \"color\", \"red\"\n\n    ' Create and populate dict2\n    Set dict2 = New Scripting.Dictionary\n    dict2.Add \"animal\", \"cat\"\n    dict2.Add \"city\", \"New York\"\n\n    ' Merge dictionaries\n    Set mergedDict = New Scripting.Dictionary\n    For Each key In dict1.Keys\n        mergedDict.Add key, dict1(key)\n    Next key\n\n    For Each key In dict2.Keys\n        If Not mergedDict.Exists(key) Then\n            mergedDict.Add key, dict2(key)\n        End If\n    Next key\n\n    ' Display merged dictionary\n    For Each key In mergedDict.Keys\n        MsgBox \"Key: \" &amp; key &amp; \", Value: \" &amp; mergedDict(key)\n    Next key\nEnd Sub<\/pre>\n<h1><span class=\"ez-toc-section\" id=\"12_Conclusion\"><\/span>12. Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<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>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8230; <a title=\"The Dictionary Object in Excel VBA &#8211; Definitive Guide\" class=\"read-more\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/\" aria-label=\"More on The Dictionary Object in Excel VBA &#8211; Definitive Guide\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Dictionary Object in Excel VBA - Definitive Guide - Tiposaurus<\/title>\n<meta name=\"description\" content=\"The definitive guide to using Dictionary objects in Excel VBA - how to create, add, search and remove items.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Dictionary Object in Excel VBA - Definitive Guide - Tiposaurus\" \/>\n<meta property=\"og:description\" content=\"The definitive guide to using Dictionary objects in Excel VBA - how to create, add, search and remove items.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/\" \/>\n<meta property=\"og:site_name\" content=\"Tiposaurus\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-11T23:19:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-26T23:22:30+00:00\" \/>\n<meta name=\"author\" content=\"Steven\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Steven\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/\"},\"author\":{\"name\":\"Steven\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#\/schema\/person\/94552b25c2fb53129269a0474088930c\"},\"headline\":\"The Dictionary Object in Excel VBA &#8211; Definitive Guide\",\"datePublished\":\"2023-04-11T23:19:14+00:00\",\"dateModified\":\"2024-01-26T23:22:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/\"},\"wordCount\":744,\"publisher\":{\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#organization\"},\"articleSection\":[\"VBA\"],\"inLanguage\":\"en-GB\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/\",\"url\":\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/\",\"name\":\"The Dictionary Object in Excel VBA - Definitive Guide - Tiposaurus\",\"isPartOf\":{\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#website\"},\"datePublished\":\"2023-04-11T23:19:14+00:00\",\"dateModified\":\"2024-01-26T23:22:30+00:00\",\"description\":\"The definitive guide to using Dictionary objects in Excel VBA - how to create, add, search and remove items.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.tiposaurus.co.uk\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Dictionary Object in Excel VBA &#8211; Definitive Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#website\",\"url\":\"https:\/\/www.tiposaurus.co.uk\/\",\"name\":\"Tiposaurus\",\"description\":\"Technology, Productivity and Development\",\"publisher\":{\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.tiposaurus.co.uk\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#organization\",\"name\":\"Tiposaurus\",\"url\":\"https:\/\/www.tiposaurus.co.uk\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.tiposaurus.co.uk\/wp-content\/uploads\/2023\/12\/cropped-cropped-tiposaurus-logo-small-e1702978467185.png\",\"contentUrl\":\"https:\/\/www.tiposaurus.co.uk\/wp-content\/uploads\/2023\/12\/cropped-cropped-tiposaurus-logo-small-e1702978467185.png\",\"width\":133,\"height\":97,\"caption\":\"Tiposaurus\"},\"image\":{\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#\/schema\/person\/94552b25c2fb53129269a0474088930c\",\"name\":\"Steven\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/www.tiposaurus.co.uk\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/70dad3cfd851615162ae18a230e533e3?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/70dad3cfd851615162ae18a230e533e3?s=96&d=mm&r=g\",\"caption\":\"Steven\"},\"sameAs\":[\"https:\/\/www.tiposaurus.co.uk\"],\"url\":\"https:\/\/www.tiposaurus.co.uk\/author\/steven\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Dictionary Object in Excel VBA - Definitive Guide - Tiposaurus","description":"The definitive guide to using Dictionary objects in Excel VBA - how to create, add, search and remove items.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/","og_locale":"en_GB","og_type":"article","og_title":"The Dictionary Object in Excel VBA - Definitive Guide - Tiposaurus","og_description":"The definitive guide to using Dictionary objects in Excel VBA - how to create, add, search and remove items.","og_url":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/","og_site_name":"Tiposaurus","article_published_time":"2023-04-11T23:19:14+00:00","article_modified_time":"2024-01-26T23:22:30+00:00","author":"Steven","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Steven","Estimated reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#article","isPartOf":{"@id":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/"},"author":{"name":"Steven","@id":"https:\/\/www.tiposaurus.co.uk\/#\/schema\/person\/94552b25c2fb53129269a0474088930c"},"headline":"The Dictionary Object in Excel VBA &#8211; Definitive Guide","datePublished":"2023-04-11T23:19:14+00:00","dateModified":"2024-01-26T23:22:30+00:00","mainEntityOfPage":{"@id":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/"},"wordCount":744,"publisher":{"@id":"https:\/\/www.tiposaurus.co.uk\/#organization"},"articleSection":["VBA"],"inLanguage":"en-GB"},{"@type":"WebPage","@id":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/","url":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/","name":"The Dictionary Object in Excel VBA - Definitive Guide - Tiposaurus","isPartOf":{"@id":"https:\/\/www.tiposaurus.co.uk\/#website"},"datePublished":"2023-04-11T23:19:14+00:00","dateModified":"2024-01-26T23:22:30+00:00","description":"The definitive guide to using Dictionary objects in Excel VBA - how to create, add, search and remove items.","breadcrumb":{"@id":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.tiposaurus.co.uk\/the-dictionary-object-in-excel-vba-definitive-guide\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.tiposaurus.co.uk\/"},{"@type":"ListItem","position":2,"name":"The Dictionary Object in Excel VBA &#8211; Definitive Guide"}]},{"@type":"WebSite","@id":"https:\/\/www.tiposaurus.co.uk\/#website","url":"https:\/\/www.tiposaurus.co.uk\/","name":"Tiposaurus","description":"Technology, Productivity and Development","publisher":{"@id":"https:\/\/www.tiposaurus.co.uk\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.tiposaurus.co.uk\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-GB"},{"@type":"Organization","@id":"https:\/\/www.tiposaurus.co.uk\/#organization","name":"Tiposaurus","url":"https:\/\/www.tiposaurus.co.uk\/","logo":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/www.tiposaurus.co.uk\/#\/schema\/logo\/image\/","url":"https:\/\/www.tiposaurus.co.uk\/wp-content\/uploads\/2023\/12\/cropped-cropped-tiposaurus-logo-small-e1702978467185.png","contentUrl":"https:\/\/www.tiposaurus.co.uk\/wp-content\/uploads\/2023\/12\/cropped-cropped-tiposaurus-logo-small-e1702978467185.png","width":133,"height":97,"caption":"Tiposaurus"},"image":{"@id":"https:\/\/www.tiposaurus.co.uk\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.tiposaurus.co.uk\/#\/schema\/person\/94552b25c2fb53129269a0474088930c","name":"Steven","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/www.tiposaurus.co.uk\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/70dad3cfd851615162ae18a230e533e3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/70dad3cfd851615162ae18a230e533e3?s=96&d=mm&r=g","caption":"Steven"},"sameAs":["https:\/\/www.tiposaurus.co.uk"],"url":"https:\/\/www.tiposaurus.co.uk\/author\/steven\/"}]}},"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":58,"url":"https:\/\/www.tiposaurus.co.uk\/display-the-weekday-as-text-in-excel\/","url_meta":{"origin":71,"position":0},"title":"Display the Weekday as Text in Excel","author":"Steven","date":"September 19, 2022","format":false,"excerpt":"I often want to know what day of the week a date is. The obvious choice in Excel is the WEEKDAY() function for this; however it only returns an integer. For example, the formula: =WEEKDAY(\"01\/02\/2009\") returns 1 (which represents Sunday). In this article, I'll describe the various ways which you\u2026","rel":"","context":"In &quot;Excel&quot;","block_context":{"text":"Excel","link":"https:\/\/www.tiposaurus.co.uk\/category\/archive\/excel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":131,"url":"https:\/\/www.tiposaurus.co.uk\/set-read-only-recommended-in-excel\/","url_meta":{"origin":71,"position":1},"title":"Set Read Only Recommended in Excel","author":"Steven","date":"September 14, 2022","format":false,"excerpt":"In this guide, we'll explain how to set read only recommended in Excel. That means that whenever someone opens it, Excel will suggest they open it as read only. While setting read only recommended can prevent accidental modifications, users can still choose not to open as read only, so we'll\u2026","rel":"","context":"In &quot;Excel&quot;","block_context":{"text":"Excel","link":"https:\/\/www.tiposaurus.co.uk\/category\/archive\/excel\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.excelfinancemastery.com\/wp-content\/uploads\/2022\/09\/readonly_1_info-1024x453.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.excelfinancemastery.com\/wp-content\/uploads\/2022\/09\/readonly_1_info-1024x453.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.excelfinancemastery.com\/wp-content\/uploads\/2022\/09\/readonly_1_info-1024x453.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.excelfinancemastery.com\/wp-content\/uploads\/2022\/09\/readonly_1_info-1024x453.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":113,"url":"https:\/\/www.tiposaurus.co.uk\/shortcut-key-to-paste-values-in-excel\/","url_meta":{"origin":71,"position":2},"title":"Shortcut Key to Paste Values in Excel","author":"Steven","date":"March 29, 2023","format":false,"excerpt":"If you came here looking for an easy shortcut key to paste values in Excel then it's bad news I'm afraid - there just isn't a simple keyboard shortcut in the same way there is to cut, copy or paste normally. However there is a way to paste values using\u2026","rel":"","context":"In &quot;Excel&quot;","block_context":{"text":"Excel","link":"https:\/\/www.tiposaurus.co.uk\/category\/archive\/excel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":133,"url":"https:\/\/www.tiposaurus.co.uk\/5-excel-formulas-every-finance-professional-should-know\/","url_meta":{"origin":71,"position":3},"title":"5 Excel Formulas Every Finance Professional Should Know","author":"Steven","date":"April 10, 2023","format":false,"excerpt":"The Importance of Excel in Finance As a finance professional, you already know that Excel is an essential tool in your day-to-day work. It's a powerful software that allows for efficient data manipulation, analysis, and reporting. The true magic of Excel lies in its formulas, which can help you automate\u2026","rel":"","context":"In &quot;Functions&quot;","block_context":{"text":"Functions","link":"https:\/\/www.tiposaurus.co.uk\/category\/functions\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":135,"url":"https:\/\/www.tiposaurus.co.uk\/how-to-calculate-net-present-values-in-excel\/","url_meta":{"origin":71,"position":4},"title":"How to Calculate Net Present Values in Excel","author":"Steven","date":"April 11, 2023","format":false,"excerpt":"Introduction Net Present Value (NPV) is a widely used financial metric that helps investors and business managers to evaluate the profitability of an investment or project. NPV calculates the difference between the present value of cash inflows and the present value of cash outflows over a certain period. A positive\u2026","rel":"","context":"In &quot;Excel&quot;","block_context":{"text":"Excel","link":"https:\/\/www.tiposaurus.co.uk\/category\/archive\/excel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":134,"url":"https:\/\/www.tiposaurus.co.uk\/excel-tips-and-tricks-for-financial-modeling\/","url_meta":{"origin":71,"position":5},"title":"Excel Tips and Tricks for Financial Modeling","author":"Steven","date":"April 10, 2023","format":false,"excerpt":"Financial modeling is an essential skill for finance professionals, allowing them to project financial performance, evaluate investment opportunities, and make informed decisions. Microsoft Excel is the go-to software for building financial models, thanks to its flexibility, functionality, and ease of use. In this blog post, we'll share tips and tricks\u2026","rel":"","context":"In &quot;Excel&quot;","block_context":{"text":"Excel","link":"https:\/\/www.tiposaurus.co.uk\/category\/archive\/excel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/posts\/71"}],"collection":[{"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/comments?post=71"}],"version-history":[{"count":1,"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/posts\/71\/revisions"}],"predecessor-version":[{"id":150,"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/posts\/71\/revisions\/150"}],"wp:attachment":[{"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/media?parent=71"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/categories?post=71"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tiposaurus.co.uk\/wp-json\/wp\/v2\/tags?post=71"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}