Dictionary VBA Class update

I’ve uploaded a new version of the Dictionary Class and a helper KeyValuePair Class. This exposes the KeyValuePairs collection so iteration is now very fast, about 2 seconds to iterate 1 million items using a for each loop:

dim oKeyValuePair as KeyItemPair 
For Each oKeyValuePair In td.KeyValuePairs ' direct access to collection
   var = oKeyValuePair.Value 'either of .key,.value

Updated 25-Nov-11: The need for the helper class is to allow updates to the .Item without changing the order. 23-Oct-2018: KeyValuePair.cls updated. 09-Apr-2021: above two links refreshed.

Screenshots of import process

Example new workbook
File > Import File…
Select the file you want to import
Imported as class

Now repeat for KeyValuePair.cls


About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Excel/VBA and tagged , , , , . Bookmark the permalink.

55 Responses to Dictionary VBA Class update

  1. Pingback: Dictionary Class in VBA instead of Scripting.Dictionary | Patrick O'Beirne @ sysmod

  2. SJT says:

    Thanks for the quick reply. I will get try that tomorrow; sound like that should do it.

  3. SJT says:

    Partially successful. Added the code to my wbk and it works. However, I want to save the wbk as a Excel 2007 macro-enabled template. I can save it, but when I start a new wbk with the template, I am not allowed to save the wbk. Error message says VB project will be lost but it will not save either with or without the project. Is there anything about your code that would restrict version (e.g. Excel 2003)?

    • sysmod says:

      I’ve just tried creating a .xltm template in Excel 2010 and when I create a workbook from it, and save, the only thing I need to do is to specify xlsm (macro enabled) format otherwise it would default to xlsx which of course is without macros.
      If I do click Yes to save without the VBA project, it does it and the xlsx can be closed and opened again normally.
      I also tried it in Excel 2003, in case that’s what you’re using, and it works fine saving as .xls and .xlt
      When you say “will not save either with or without the project”, what does it do, what error message do you get, what file name are you using and what file format are you specifying?

      • SJT says:

        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.

  4. sysmod says:

    If you like, try the save again and grab the screen with the refusal message (PrtSc, paste into Word or Paint) and email it to me : mail3 at my sysmod.com site.

  5. Zach says:

    Just tested this out and it worked beautifully. I had to adjust a couple of my calls to conform better with the syntax but was pretty easy work. Thanks for this.

  6. tapas says:

    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.

  7. Frank Root says:

    I’m attempting to use your Dictionary class. When I put it in a module the VERSION and Attribute lines at the top of the file are causing a compile error. What don’t I know about that syntax?

    • sysmod says:

      Thanks for adding ” You can ignore my previous post. I found information on how to import your code and it worked fine. Thanks for providing this valuable code.”
      Just in case anyone else has this problem, I should add here: Use the VBE to Import the code, don’t copy & paste the file in.

      Ribbon > Developer menu > Visual Basic
      or press Alt+F11

      Select your project.
      File > Import File
      Select the .bas file


  8. Pingback: Excel Instrumentation: Activity Tracking with Visual Basic for Applications | mmatthews06

  9. Jiri says:

    This does not work on a Mac Excel with the above mentioned Dictionary and KeyValuePair classes.

    Sub test()
    Dim dict As Dictionary
    Set dict = New Dictionary
    Dim values(1 To 2) As String
    values(1) = “one”
    values(2) = “two”

    dict.Add “key”, values
    MsgBox dict(“key”)(1)
    MsgBox dict(“key”)(2)
    End SubÏ

    Under a windows excel one gets two successive messages:


  10. Ok, let’s break in into steps:
    dim v as variant
    dict.Add “key”, values
    v = dict(“key”)
    debug.print typename(v),lbound(v),ubound(v)
    debug.print v(1)

    • Jiri says:

      The Test2() says:

      Run-time error 438
      Object does not support this property or method.

      v = dict(“key”) ‘ This row HIGHLIGHTED

      Sub test2()

      Dim dict As Dictionary
      Set dict = New Dictionary
      Dim values(1 To 2) As String
      values(1) = “one”
      values(2) = “two”

      Dim v As Variant
      dict.Add “key”, values
      v = dict(“key”) ‘ This row HIGHLIGHTED
      Debug.Print TypeName(v), LBound(v), UBound(v)
      Debug.Print v(1)

      End Sub

  11. Thanks, Jiri,
    I see you are using the default property dict(“key”) rather than dict.item(“key”)
    v = dict.item(“key”) works in Windows.

    In which case I recommend you make the change in the code offered by Tachy on March 26 at the original post :


    Good luck


    • Jiri says:

      Finally it works! It was a hell to make it right.
      (according to Tachy, March 26)
      1. Insert:
      Attribute Item.VB_UserMemId = 0
      Public Property Get Item(Key As String) As Variant

      (The result looks like this ..)
      Public Property Get Item(Key As String) As Variant
      Attribute Item.VB_UserMemId = 0
      AssignVariable Item, KeyValuePairs.Item(Key).Value
      End Property

      (according to http://productivebytes.blogspot.cz)
      2. You’ll get a “Syntax Error” – Ignore it.
      3. Export the Class
      4. Remove the Class
      5. Reimport it again.

      What a piece of sh** is this Microsoft producing … !?!

      Thanks Patrick!

  12. Jason P. says:

    Helpful post Jiri, thanks. Seems to work perfectly so far.

  13. Carter P. says:

    How do you insert the code into the Visual Basics Editor for Excel for Mac 2016?

  14. Jon says:

    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” (https://support.office.com/en-us/article/-variable-uses-an-automation-type-not-supported-error-in-visual-basic-editor-in-excel-for-mac-c3125bf3-c30b-4d62-be42-d2f1bec0e5ad). As workaround I am now again removing the value from the collection first and then adding it again.
    Do you know a better solution?


  15. Steve says:

    I found a typo when trying to make the item of a Dictionary an Object type.

    In the setter property of the KeyValuePair class, you have “Set Value = obj”, which causes an infinite loop. If you change it to “Set Value_ = obj”, it works as expected

  16. 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 IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = KeyValuePairs.[_NewEnum]
    End Property

    The other inconsistency with scripting.dictionary is the Public Property Get Exists(Key As String) As Boolean it shouldn’t it be a function? i.e. Public Function Exists(Key as String) As Boolean

  17. Same for Public Property Get Count() As Long it “probably” should be a function as well. 🙂 If consistent across the scripting dictionary and other implementations of using collection based dictionary replacements can implement an interface.

  18. Same goes for items and keys should a function to keep consistent with the scripting.dictionary .

  19. yasserkhalil says:

    Thanks a lot. In fact I feel lost. Can you post the latest version of the two classes : Dictionary.cls and KeyValuePair.cls?

  20. paulb says:

    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.

    • I’ve never needed to sort it. I’d dump the keys to a sheet, add an index, sort, and reload a new collection in that order. Or use Chip’s code to heapsort an array.

      • paulb says:

        Thanks for your reply. In the end I used Chip’s code, modified with inheritance to handle the different objects I want to sort. It was helpful that the dictionary class already has the .items method that returns an array.

  21. joanbaptist says:

    Hello, congrats for the idea & the fantastic job done.
    Unhappily, I can’t get the files from the provided link!
    Would you please update the link as guessing the needed code is a bit confusing and lambirintic due the quantity of comments.
    Thank’s a lot

  22. Alvin Correa says:

    Hello. Thanks a lot for your page and your examples. I want to ask you about something. I built a Excel DNA addInn complement. and i want to make an excel file with macros in VBA. How can i call my functions from Excel DNA? i try with Application.WorksheetFunction.MyFunction(Range) but it’s doesn’t work. Can you help me?
    Thank you very much.

  23. Crrepyboy says:


    Thanks for this post but I have been unable to figure how to use this dictionary. Basically I want to extract a range of unique values from a range. Could you please advise how to transform this as per the new dictionary. Thanks very much!

    Dim dict As Object: Set dict = CreateObject(“Scripting.Dictionary”)
    dict.CompareMode = vbTextCompare
    Dim arg As Range
    Dim cel As Range
    Dim Key As Variant
    For Each arg In crg.Areas
    For Each cel In arg.Cells
    Key = cel.Value
    If Not IsError(Key) Then
    If Len(Key) > 0 Then
    dict(Key) = Empty
    End If
    End If
    Next cel
    Next arg
    Dim rCount As Long: rCount = dict.Count
    If rCount > 0 Then
    ‘ Write unique values from Unique Dictionary to Data Array.
    Dim Data As Variant
    Dim i As Long
    ReDim Data(1 To rCount, 1 To 1)
    For Each Key In dict.Keys
    i = i + 1
    Data(i, 1) = Key
    Next Key
    End If

  24. I don’t know Alvin. The question is not related to the blog post.

  25. @Crrepyboy :
    1) Import into your VBA project the two files Dictionary.cls and KeyValuePair.cls
    2) Remove the line dict.CompareMode = vbTextCompare, that property is not available in this class.
    3) The line dict(Key) = Empty relies on the auto-adding feature of the Windows Scripting.Dictionary. Replace it with
    If Not dict.Exists(CStr(Key)) Then dict.Add CStr(Key), Key

    That then runs the same as the original code.
    Hope this helps

  26. Ratrack says:

    Hello, can you tell me more about how to import this in excel ? Do I have to ask the users to open the vba editor and copy the code or there is a much simplier way to import the code ? Thanks.

  27. Thanks for asking Ratrack. Simply go into the VBE Editor and File > Import into your VBA project the two files Dictionary.cls and KeyValuePair.cls

  28. Hi Patrick. When I import the files into Excel, they always import as a normal module and not as a “Class Module”.

    The lines before “options explict” don’t compile and Excel does not recognize the module this way.

    When I insert a Class Module and copy/paste, then Excel recognizes the classes but it does not compile the lines before “options explicit”

    I tried in Mac and Windows. I’m using Office 365

  29. Hi Eduardo,
    In the VBE, select the project of the workbook you want to import it to.
    File > Import File > pick the .cls file, OK
    A new class module appears correctly.
    I’ll edit the post above and put in pictures.

  30. Vaseem Mohammed says:

    Hi Patrick,
    I am a bit new to such an advanced level of vba scripting.
    Want to understand how can I access the value of the dictionary key when using your classes?
    I am trying following ways, and none of these are giving me the result.


  31. Hello Vaseem, when you know the key, use .Item to index the collection, eg
    ? MyDict.item(“key”) give you the value for the key “key”.

  32. ayhan says:

    I knew I could run this file on windows computer, but it doesn’t work on MacBook, it gives an error. I applied what you said, but it still didn’t work, I would appreciate it if you could help with this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s