The VBA Excel Dictionary

In Visual Basic for Applications, in the Dictionary function, we use arrays and collections in storing values needed in creating a macro code. For example, we can use it when storing the lists of colors name from a certain cell range in a worksheet.

Dictionary is very much like collections and we can use both in naming an item.

Dictionary Property

We have the below as the VBA dictionary properties and those properties can get information about the arguments of the object.

• .Count - is used to show the number of elements in the dictionary.
• .CompareMode - is used to refer and set to the compare mode of the VBA dictionary and it can be set into text, binary or even to database.
• Key - it is used to replace a key with another key.
• Item - it is used to refer or set the item to a specific key.

Dictionary Features

• Properties of Keys - VBA dictionary is always case-sensitive.
• Strings and Keys are the most commonly used data type for the dictionary.
• *** Properties of items*** - this can be any type, it can be an object, collections, and arrays, can be strings or numbers, etc.
• Auto re-sizing of Dictionaries - You can create and add as many key-value pairs to your dictionary as you wish.

Dictionary Methods

The below will show you a variety of methods of dictionary objects.

• .Add - this is used to adds a key-value pair to the dictionary.

-.Exists - this is used in checking whether the specified key already exists in the dictionary.

-.Items - this is the array of all items or the values inputted in the dictionary.

• .Keys - this is also known as the array of all keys in the dictionary.
• .Remove - this is used in removing a specific key-value pair from the dictionary.
• .Removeall - this is used in removing all items (key-value pairs) from the dictionary.

Below are different terms used in dictionary and its parameters:

• Binding early reference - also known as the “Microsoft Scripting Runtime”. You can do that by going to Tools- then>References from the Visual Basic menu
• Declare (binding early) - or also known as Dim DCT As Scripting.Dictionary
• Create (binding early) - also known as Set DCT = New Scripting.Dictionary
• Declare (binding late) - also known as Dim DCT As Object
• Create(binding late) - aslo known as Set DCT = CreateObject("Scripting.Dictionary")
• Go to all the items (for looping - early binding) -

Dim k As Long  
For k = 0 To DCT.Count - 1  
Debug.Print DCT.Keys(f), DCT.Items(f)  
Next k  

• Go to all the items (for a looping early/late binding) -

Dim k As Long  
For k = 0 To DCT.Count - 1  
Debug.Print DCT.Keys()(f), dict.Items()(f)  
Next k  

• Get the number of items - also known as the DCT.Count
• Make key case sensitive (the dictionary must be empty). - also known as DCT.CompareMode = vbBinaryCompare
• Make key non case sensitive (the dictionary must be empty). - also known as DCT.CompareMode = vbTextCompare

Conclusion

In this article we discussed Dictionaries in VBA and we hope you use them in your worksheets when they fit the data you are evaluating.