Contents.A Quick Guide to the VBA Dictionary FunctionParamsEarly binding reference“Microsoft Scripting Runtime”(Add using Tools-References from the VB menu)Declare (early binding)Dim dict As Scripting.DictionaryCreate(early binding)Set dict = New Scripting.DictionaryDeclare (late binding)Dim dict As ObjectCreate(late binding)Set dict = CreateObject( 'Scripting.Dictionary')Add item (key must not already exist)dict.Add Key, Valuee.g. Dict.Add 'Apples', 50Change value at key. Automatically adds if the key does not exist.dict( Key) = Valuee.g.

Dict( 'Oranges') = 60Get a value from the dictionary using the keyValue = dict( Key )e.g. AppleCount = dict( 'Apples')Check if key existsdict.Exists( Key)e.g. If dict.Exists( 'Apples') ThenRemove itemdict.Remove Keye.g. Example of Key, Value pairsIn both cases, we are storing the value 5 and giving it the name “Apple”. We can now get the value of Apple from both types like this ' Get value from DictionaryTotal = dict( 'Apple')' Get value from CollectionTotal = coll( 'Apple')So far so good. The Collection however, has two major faults. We cannot check if the key already exists.

We cannot change the value of an existing item.The first issue is pretty easy to get around:. The second is more difficult.The VBA Dictionary does not have these issues.

Click Add, and then locate and double-click the custom dictionary that you want to import. If the custom dictionary doesn't have a file name extension of.dic, on the Enable pop-up menu, click All Files. Add, delete, or edit words in a custom dictionary in Word. You can edit a custom dictionary in Word. You can change words that are in the. Commercial and consumer customers can now purchase Microsoft Office 2019 for Windows and Mac. Office 2019 is the next perpetual release of Office. It includes significant improvements over Microsoft Office 2016 and earlier versions of on-premises Office.

You can check if a Key exists and you can change the Item and the Key.For example, we can use the following code to check if we have an item called Apple. If dict.Exists( 'Apple') Thendict( 'Apple') = 78These may seem very simple differences. However, it means that the Dictionary is very useful for certain tasks.

Particularly when we need to retrieve the value of an item.Download the Source Code. Dictionary WebinarIf you are a member of the, then click on the image below to access the webinar and the associated source code.( Note: Website members have access to the.)A Dictionary in real world termsIf you are still not clear about a Dictionary then think of it this way.

A real world dictionary has a list of keys and items. The Keys are the words and the Items are the definition.When you want to find the definition of a word you go straight to that word. You don’t read through every item in the Dictionary.A second real world example is a phone book(remember those?).

The Key in a phone book is the nameaddress and the Item is the phone number. Again you use the nameaddress combination to quickly find a phone number.In Excel the VLookup function works in a similar way to a Dictionary. You look up an item based on a unique value.A Simple Example of using the VBA DictionaryThe code below give a simple but elegant example of using the Dictionary. It does the following.

Adds three fruit types and a value for each to a Dictionary. The user is asked to enter the name of a fruit. The code checks if this fruit is in the Dictionary. If yes then it displays the fruit name and the value. If no then it informs the user the fruit does not exist.'

Sub CheckFruit' Select Tools-References from the Visual Basic menu. ' Check box beside 'Microsoft Scripting Runtime' in the list. Dim dict As New Scripting.Dictionary' Add to fruit to Dictionarydict.Add key:= 'Apple', Item:=51dict.Add key:= 'Peach', Item:=34dict.Add key:= 'Plum', Item:=43Dim sFruit As String ' Ask user to enter fruitsFruit = InputBox( 'Please enter the name of a fruit')If dict.Exists(sFruit) ThenMsgBox sFruit & ' exists and has value ' & dict(sFruit)ElseMsgBox sFruit & ' does not exist.' End If Set dict = Nothing End SubThis is a simple example but it shows how useful a Dictionary is. We will see a real world example later in the post. Let’s look at the basics of using a Dictionary.Creating a DictionaryTo use the Dictionary you need to first add the reference. Select Tools-References from the Visual Basic menu.

Find Microsoft Scripting Runtime in the list and place a check in the box beside it.We declare a dictionary as follows Dim dict As New Scripting.Dictionaryor Dim dict As Scripting.DictionarySet dict = New Scripting.DictionaryCreating a Dictionary in this way is called “Early Binding”. There is also “Late Binding”. Let’s have a look at what this means.Early versus Late BindingTo create a Dictionary using Late binding we use the following code. We don’t need to add a reference. Dim dict As Object Set dict = CreateObject( 'Scripting.Dictionary')In technical terms Early binding means we decide exactly what we are using up front.

With Late binding this decision is made when the application is running. In simple terms the difference is. Early binding requires a reference. Late binding doesn’t.

Early binding allows access to.Intellisense. Late binding doesn’t. Early binding may require you to manually add the Reference to the “Microsoft Scripting Runtime” for some users.(.Intellisense is the feature that shows you the available procedures and properties of an item as you are typing.)While that you use early binding in almost all cases I would differ. A good rule of thumb is to use early binding when developing the code so that you have access to the Intellisense.

Use late binding when distributing the code to other users to prevent various library conflict errors occurring.Adding Items to the Dictionary FunctionParamsExampleAddKey, Itemdict.Add 'Apples', 50We can add items to the dictionary using the Add function. Items can also be added by assigning a value which we will look at in the next section.Let’s look at the Add function first. The Add function has two parameters: Key and Item. Both must be supplieddict.Add Key:= 'Orange', Item:=45dict.Add 'Apple', 66dict.Add ', 'John'dict.Add 1, 45.56In the first add example above we use the parameter names. You don’t have to do this although it can be helpful when you are starting out.The Key can be any data type.

The Item can be any data type, an object, array, collection or even a dictionary. So you could have a Dictionary of Dictionaries, Array and Collections.

But most of the time it will be a value(date, number or text).If we add a Key that already exists in the Dictionary then we will get the errorThe following code will give this errordict.Add Key:= 'Orange', Item:=45' This line gives an error as key exists alreadydict.Add Key:= 'Orange', Item:=75Assigning a Value OperationFormatExampleAssignDictionary(Key) = Itemdict( 'Oranges') = 60We can change the value of a key using the following codedict( 'Orange') = 75Assigning a value to Key this way has an extra feature. If the Key does not exist it automatically adds the Key and Item to the dictionary. This would be useful where you had a list of sorted items and only wanted the last entry for each one.

' Adds Orange to the dictionarydict( 'Orange') = 45' Changes the value of Orange to 100dict( 'Orange') = 100Don’t forget that you can download all the VBA code used in this post from the top or bottom of the post.Checking if a Key Exists FunctionParametersExampleExistsKeyIf dict.Exists( 'Apples') ThenWe can use the Exists function to check if a key exists in the dictionary ' Checks for the key 'Orange' in the dictionary If dict.Exists( 'Orange') ThenMsgBox 'The number of oranges is ' & dict( 'Orange')ElseMsgBox 'There is no entry for Orange in the dictionary.' End IfStoring Multiple Values in One KeyTake a look at the sample data below. We want to store the Amount and Items for each Customer ID.The Dictionary only stores one value so what can we do?We could use an array or collection as the value but this is unnecessary. The best way to do it is to use a Class Module.The following code shows how we can do this ' clsCustomer Class Module Code Public CustomerID As String Public Amount As Long Public Items As Long' Create a new clsCustomer object Set oCust = New clsCustomer' Set the valuesoCust.CustomerID = rg.Cells(i, 1).ValueoCust.Amount = rg.Cells(i, 2).ValueoCust.Items = rg.Cells(i, 3).Value' Add the new clsCustomer object to the dictionarydict.Add oCust.CustomerID, oCustYou can see that by using the Class Module we can store as many fields as we want. © BigStockPhoto.comThe Dictionary uses a similar method. The CompareMode property of the Dictionary is used to determine if the case of the key matters.

The settings arevbTextCompare: Upper and lower case are considered the same.vbBinaryCompare: Upper and lower case are considered different. This is the default.With the Dictionary we can use these settings to determine if the case of the key matters.

Example 1 – Summing Single ValuesLet’s have a look at a real-world example of using a dictionary. Our data for this example is the World Cup Final matches from 2014.Our task here is to get the number of goals scored by each team.The first thing we need to do is to read all the data. The following code reads through all the matches and prints the names of the two teams involved. Teams ordered by number of goals scoredExample 2 – Dealing with Multiple ValuesWe are going to use the data from the section aboveImagine this data starts at cell A1. Hi Paul,Thank you for the very useful post.I have macro that uses the objects as dictionary items that I believes follows your instructions. The macro is supposed to take the key and if it exists in the dictionary: retrieve the object and output it. However, the output is always the last object that was put into the dictionary.

Hi Paul,Thanks for the help. Hello Paul,Thank you for your blog! I have started using vba a couple of months ago and I am learning as much as I can, I didn’t knew about dictionaries (but I read it in your blog about vlook up, which I found also extremely useful) so I decided to learn about it also in your blog, I have found it very useful and want to apply it to a project I am working on right now. Nevertheless I have tried to run the Exercise 2, in order to fully understand the dictionary for multi items, and when I run it, I receive an error on the“Dim oCust As clsCustomer, i As Long” line saying that the type defined by the user has not been defined.

How can I fix this error? I have already followed the previous steps of the Class module, the sheets and adding the microsoft scripting runtime. Thank you Paul, I have tried that and it works! I will now try to use it in my project. I do believe that the steps for exercise 2 should include that part because it is not very clear that we should modify the name of this class module (at least from someone who has not so clear the class module characteristics), it only says “6. Create a new class module and add the first piece of code from below.” On the other hand, I would like to ask you, If I send that excel file (on which the macro is) on other computers, via email for example, does the file remain with the microsoft script runtime available? Or should the user activate it when it is downloaded?And again thank you for your fast reply and for this excellent tutorial!

Hi, you could make a dictionary of collections. The collections would store the different amounts and items for each customer ID (using class modules). Hello,I want to display only values greater than 6the attached program only displays values greater than 1Sub CompteItemsDoublonsSet d = CreateObject(“Scripting.Dictionary”)Set d2 = CreateObject(“Scripting.Dictionary”)For Each c In Range(a2, a65000.End(xlUp))If d.exists(c.Value) ThenIf d2.Item(c.Value) = 0 Then d2.Item(c.Value) = 2 Else d2.Item(c.Value) = d2.Item(c.Value) + 1End Ifd.Item(c.Value) = “”Next cIf d2.Count 0 ThenRange(“c2”).Resize(d2.Count, 1) = Application.Transpose(d2.keys)Range(“d2”).Resize(d2.Count, 1) = Application.Transpose(d2.items)End IfEnd Sub.

Thank you for such a great resource, Paul. Very much appreciated.Is there a way to increase the speed of the Get Keys property in the ‘Dictionary’ class module? Looping through each key to create the property array is proving very slow (5 minutes) with a decent sized data set (135K records).Alternatively, might there be a way commit the dictionary’s contents to a range without using.keys?For what it’s worth, I am on Excel for Mac.Apologize if this is a duplicate, but did not get confirmation it posted. Your Excel VBA Dictionary – A Complete Guide is fabulous. I was able to duplicate the functionality with my own classical music database. Thank you for your work in producing the above guide.I was a Pick O/S programmer for nearly 30 years and am quite familiar with data dictionaries. The Pick dictionaries are much more flexible than the one in excel but excel is a most viable product and the Pick O/S is mostly relegated to no longer being practical as it is a character based system without a graphic user interface.

Hi Paul,I’ve created a two dimensional dictionary with the old and new IDs of our customers. I can’t use an array as there are over 180.000 unique customers. I successfully filled the dictionary using a class module.Every day I have to run through a list of new and/or old IDs and replace the old ID with the new ID. I am struggling to find a way to search the dictionary for an old ID and return the new ID.

I hope you can point me in the right direction.Sample data:OldID NewIDAAA000001 CCC000001AAA000002 CCC000002AAA000003 CCC000003BBB000001 CCC000004BBB000002 CCC000005BBB000003 CCC000006. I’m sorry Paul,I just watched your YouTube series again, it helped me quite a lot!I thought I needed a two dimensional dictionary using a class module because my dataset contained two columns. Therefor I could never get ‘if dict.Exist’ to work.I now created the a regular dictionary with the old ID as the key and the new ID as the item/value. It works beautifully now.

I used to check my daily list using an index/match formula. Baofeng uv 5r driver mac download. This took between 6 and 8 minutes. Using the dictionary it takes 4-7 seconds!You can delete my post if you want.

Yes, I have verified that your code does work when saved as a template. My problem seems to be coming from something else I am doing in my workbook that is affecting the workbook after it is saved as a template (I’m using Excel 2007).

When I open a new workbook with the template file and then try to save it, it refuses to retain the VP project and will not save even if I ask it to continue without the VB project. No error messages of any kind. So it isn’t anything you have done, it’s me. Thanks anyway for your attempts to help me.

Hi Patrick,Could you kindly show me an simple example implementation of this for a custom collection class? For example, say I want to create mcTasks that is a collection of mcTask.

The mcTask has three attributes: TaskID (as String), RoleID (as Integer), and TaskDesc (as String), with a conjugate key as TaskID+RoleID.I am not very good at it and am trying to learn the subject. A simple example will be very helpful for me to see the connections and usage and will provide significant clarity.Also, I am on a Mac using Excel 2011, if that is of any value.Thank you so very much.

Finally it works! It was a hell to make it right.(according to Tachy, March 26)1. Insert:Attribute Item.VBUserMemId = 0afterPublic Property Get Item(Key As String) As Variant(The result looks like this.)Public Property Get Item(Key As String) As VariantAttribute Item.VBUserMemId = 0AssignVariable Item, KeyValuePairs.Item(Key).ValueEnd Property(according to )2.

You’ll get a “Syntax Error” – Ignore it.3. Export the Class4. Remove the Class5. Reimport it again.What a piece of sh. is this Microsoft producing!?!Thanks Patrick!Jiri. I used your Dictionary implementation for some years.

Thanks a lot!Now I’ve upgraded to Excel 2016 and get errors 😦When I query a value, “KeyValuePairs.Item(Key).value” returns Null, even though the entry exists. I’ve fixed this with a temporary KeyValuePair, where the result of.Item(Key) is written to first.When trying to update a stored value I get the error “Variable uses an Automation type not supported”. As workaround I am now again removing the value from the collection first and then adding it again.Do you know a better solution?Regards. Looks great some things of concern thou one being the public members KeyValuePairs to allow iteration.To allow iteration for custom collections you could add the code below. Note: must add the Attribute in a text editor. That should allow you to encapsulation the class members by setting to private and allow iteration.

I’ll have to test myself as currently exploring custom lists where can implement an IList template or IKeyedList one I’m especially interested in. That way it doesn’t matter what the implementation is whether it’s a scripting dictionary or a collection based replacement dictionary.Public Property Get NewEnum As IUnknownAttribute NewEnum.VBUserMemId = -4Set NewEnum = KeyValuePairs.NewEnumEnd PropertyThe other inconsistency with scripting.dictionary is the Public Property Get Exists(Key As String) As Boolean it shouldn’t it be a function? Public Function Exists(Key as String) As Boolean. I’ve been using your dictionary class extensively in a project I’m working on and it’s been very helpful, thanks for making it available.

I’m wondering if you ( or another reader) have ever needed to sort the dictionary and have some advice on doing that? I’m typically using the dictionary to hold objects so I expect I’ll need to somehow specify the object field to sort on.I see Chip Pearson’s site has some code examples and I’ll look into using those but if the work has already been done for this library that that would be a bonus. He also suggests where the dictionary holds an object, outputting the dictionary to an array, sorting and then recreating the dictionary.