Dictionary Class in VBA instead of Scripting.Dictionary

In adapting an addin for Mac Excel 2011, I found that the Mac did not have the reference to the “Microsoft Scripting Runtime” dictionary (scrrun.dll). So, I wrote my own using a Collection. You can download it here: Dictionary.cls (update: see https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/)


To use it, import the file into your project, then use

Dim td as Dictionary
Set td = new Dictionary


Interestingly, it’s almost three times faster than scrrun.dll for the .Add method and six times faster for the .Item method.

A couple of other lessons are:

1) The usual way to get an element is .Item(key).  The method .Items returns an array. To iterate the entire set, use

for each vItem in td.Items

repeating .Items in a loop is the slowest possible way to address them.

Instead of
For i = 1 To 1000
  var = td.Items(i - 1)
use one call to return the array and then index into that.
vItems = td.Items
For i = 1 To 1000
  var = vItems(i - 1)

2) Calling the Scripting.Dictionary .Item method will silently add the key if it is not in the dictionary. Be careful to use .Exists if you want to check that first. I chose to have my Dictionary object throw an error if you address a key that does not exist.

3) Note that the order of .Add parameters is different in a Collection from a Dictionary.

Collection parameter order is Add(Item,Key); Dictionary is Add(Key,Item) so I recommend to always use named arguments to avoid confusion.

If you find this useful, let me know!


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. Bookmark the permalink.

80 Responses to Dictionary Class in VBA instead of Scripting.Dictionary

  1. John Nurick says:

    Thank you! That’s one less thing on my own to-do list.

  2. MSimms says:

    re: “Interestingly, it’s almost three times faster than scrrun.dll for the .Add method and six times faster for the .Item method.”
    I find this hard to believe given the scrrun.dll is likely built from highly optimized native C code.
    However, with MSFT’s implementations, anything goes !
    Thanks for this.

  3. sysmod says:

    Here are the test timings which Mark encouraged me to do properly (and also gave me an optimisation I might use). The abbreviations are SD=Scripting.Dictionary (scrrun.dll), TD=This Dictionary VBA class. Times are in Mins:secs

    To add 1,000,000 items: SD 00:46, TD 00:18
    To retrieve 1 million using .Item(Key) SD 00:45, TD 00:07

    However, where SD wins is in iterating over the whole set.

    It’s not wise to use TD.Items in a loop because for say 100 iterations you’re calling 100 times a method that iterates the entire collection to build the Items array
    SD.Items is almost instantaneous to return an array of 1,000,000 items

    An alternative is to use the TD.KeyValuePair method which addresses the collection behind the class implementation. TD.KeyValuePair(i) takes 01:44 for 100,000 items.

    Better performance, at the risk of exposing private elements, is to simply make the Key-Item collection available as a collection for iteration. I’ll post an update next.

    • Hi just reading some of your old comments as I’ve finally got around to adapting your concept of using a key-value pair with using a collection with also dealing with case sensitive keys. You could solve the issue of rebuilding the items and keys array by using a static array and a flag to check if any change has been made and only repopulate when a change is made. At the moment testing and haven’t checked performance thou that idea should work. The alternative sounds a better idea with iteration over the collection and if using the following you don’t need to expose the key-item collection.

      Public Property Get NewEnum() As IUnknown
      ‘Attribute NewEnum.VB_UserMemId = -4
      Set NewEnum = this.KeyValuePairs.[_NewEnum]
      End Property

  4. SJT says:

    After importing Dictionary.cls to a new workbook and attempting to Compile the code, I get an error ‘User-defined type not defined’ presumably for KeyValuePair. What am I doing wrong?

  5. scott says:

    Patrick, is this implementation in the public domain? It works well, thanks for posting.

  6. thomas says:

    Thank you, this is very helpful to me. My company is transitioning from all Windows to all Mac, and I have a lot of Excel VBA code that use Dictionary. I appreciate your work to create this class.

  7. Tito says:

    Huge help. I’m new to VBA and have been working on a solution that required this kind of class. In reading up about scripting.dictionary I found it’s not available for OSX, but I immediately found this and was able to adapt to your class very easily. I appreciated notes about differences too. Very clearly annotated so even a beginner can understand without much trouble. Thanks for sharing this!

  8. Tachy says:

    Thank you, this tips is very helpful to me.

    But, I was happy to correct the one point .
    I hoped to use the ‘Default Property’.
    “Scripting.Dictionary” by Microsoft has Default Property.

    For example …
    Dim td as Dictionary
    Set td = new Dictionary

    td.Add (“Id”, “12345”)
    Debug.Print td(“Id”)

    Therefore, I added the following statement in line 43.

    42 : Public Property Get Item(Key As String) As Variant
    43 #: Attribute Item.VB_UserMemId = 0
    44 : AssignVariable Item, KeyValuePairs.Item(Key).Value
    45 : End Property


  9. Thorak says:

    The .Items and .Item methods do not work with a dictionary within a dictionary.

    Dim materialDictionary As Dictionary
    mat materialDictionary = New Dictionary

    Dim colorDictionary As Dictionary
    Set colorDictionary = New Dictionary

    colorDictionary .Add “Blue”, “5”

    material = “Cloth”
    materialDictionary.Add material, colorDictionary

    For Each colval In colorDictionary.Items
    msg = msg & colval & vbNewLine
    Next colval
    MsgBox (msg)

    I get “Object does not support this property or method” error

    However if I change :
    materialDictionary.Add material, colorDictionary
    materialDictionary.Add material, “foo”

    it works fine.

    if I do this:

    For Each colval In colorDictionary.Keys
    msg = msg & colval & vbNewLine
    Next colval
    MsgBox (msg)

    This works fine. I can Iterate through the key list. Just not the Item list.

    How can it get .Items and .Item to work with a Dictionary object so I can loop through it like so:

    Count = 2
    For Each matKey In matCollection.Keys
    ActiveSheet.Cells(Count, 17) = matKey

    For Each matVal In matCollection.Item(CStr(matKey))
    ActiveSheet.Cells(Count, 18) = matVal
    Count = Count + 1
    Next matVal
    Next matKey

    • Thanks, Thorak, I’ll look at that when I have time.

      • Thorak says:

        I actually figured it out after a lot of head scratching and tinkering. It works like a charm now. The key was to decalare and then set the value of the first Dictionarys item as another Dictionary item. I have included my code in case it helps anyone else in the future.

        ‘ loop through the list of materials
        For Each matKey In matCollection.Keys
        ‘ output the material name (KEY)
        ActiveSheet.Cells(rowCounter, 17) = matKey

        ‘ loop through the list of colors in this material (VALUE)
        Dim matVal As Dictionary
        Set matVal = matCollection.Item(CStr(matKey))

        For Each colorKey In matVal.Keys
        ‘ ouput the color name (KEY)
        ActiveSheet.Cells(rowCounter, 18) = colorKey
        ‘ ouput the color qty (VALUE)
        ActiveSheet.Cells(rowCounter, 19) = matVal.Item(CStr(colorKey))

        rowCounter = rowCounter + 1
        Next colorKey
        Next matKey

  10. OK, thanks, if it works for you that’s great!

  11. Ran into some trouble and I hope you might be able to shed some light. The editor would take dimensioning and setting fine, but when I tried to use the .add method, I kept getting “Invalid character” from the editor.

    I ended up using ‘dict(xxx) = “value”‘ syntax in place of Add. .Exists doesn’t throw an editor error but it as yet untested due to this issue. My goal is to use a dictionary of dictionaries. The error is:

    Run-time error ‘438’:
    Object doesn’t support this property or method

    Here’s my code:

    Sub SplitModifiersIntoColumns()
    ‘Assumption: Current Sheet is broken out into unique SKUs with modifiers formatted identically

    ‘Locate “Modifiers” column in the current sheet
    ‘Count total number of modifiers for this sheet.
    ‘Make a new column for each Modifier, titling it with text of Modifier
    ‘Populate with value of Modifier

    Dim modifierColumn As Integer
    Dim rawModifiers As String
    Dim oneMod As String
    Dim oneResp As String

    Dim modifierList As Dictionary
    Set modifierList = New Dictionary
    Dim subList As Dictionary
    Set subList = New Dictionary

    ‘Each cell in the “Modifiers” column use a semicolon ( ; ) to separate assigned modifiers and the pipe( | ) to separate modifer and user response. Example:

    lastRow = Range(“A1″).End(xlDown).Row
    ‘Locate Modifiers Column
    modifierColumn = Sheets(1).Rows(1).Find(what:=”Modifiers”, lookat:=xlWhole).Column

    ‘Iterate through each entry in Modifiers, parsing out individual modifiers and responses.
    modTitleIdx = 0
    modResponse = 0
    lastModTitle = 0

    For theRow = 2 To lastRow
    ‘Get the string of modifiers in the current row
    rawModifiers = Cells(theRow, modifierColumn).value
    ‘Break each modifier string in the row into a separate array element
    rowModifiersArray = Split(rawModifiers, “;”)
    ‘Iterate through each of the modifiers and value in the new array
    For Each modResp In rowModifiersArray
    ‘Seperate the modifier from the response in another temp array, ‘singleModifier’.
    ‘The first element of the array will be the name of the modifier, the second will be the response to the modifier.
    singleModifier = Split(modResp, “|”)
    oneMod = singleModifier(0)
    oneResp = singleModifier(1)
    ‘If the modifier exists as a key in the ModifierList, add the row and the value into the dictionary associated to that key
    myResp = MsgBox(oneMod & ” = ” & oneResp & vbCrLf & “Continue?”, vbYesNo, “Debug”)
    If (myResp = vbNo) Then
    modResp = -1
    Exit For
    End If
    ‘If the modifier has already been entered in modifierList, add the row and value to the sub-dictionary
    If (Not modifierList.Exists(oneMod)) Then
    modifierList(oneMod) = New Dictionary
    End If
    modifierList(oneMod)(theRow) = oneResp
    Next modResp
    Next theRow

    Dim K As Variant
    For Each K In modifierList.Keys
    debugValues = “”
    For Each Y In modifierList(K).Keys
    If (debugValues “”) Then debugValues = debugValues & “, ”
    debugValues = debugValues & “Row(” & Y & “) : ‘” & modifierList(K)(Y) & “‘”
    Debug.Print “Modifier: ” & K, “Values: ” & debugValues
    ‘End Debug
    End Sub

    Any and all help would be appreciated!

    • Well, the system seemed to munge some of my code — in the comments about the format, it should read:

      ‘Each cell in the “Modifiers” column use a semicolon ( ; ) to separate assigned modifiers and the pipe( | ) to separate modifer and user response. Example:
      ‘MODIFIER1|&lt User Response for Mod 1>MODIFIER2|&lt User Response for Mod 2>MODIFIER 3|&lt User Response for Mod 3&gt …

    • Thanks for commenting Scott

      I recommend you use Option Explicit and then define variables like
      dim lastRow as long

      I see a line
      modifierList(oneMod) = New Dictionary

      When you use New, you are creating an object and so you need to use SET var = new class
      and the way to do that here is to create a dictionary object and add that to the main list:
      Set subList = New Dictionary
      modifierList.Add oneMod, subList

      You can use that in the Else clause as

      modifierList.Item(oneMod).Add Str(theRow), oneResp

      and the debug code is then

      For Each K In modifierList.Keys
      Set subList = modifierList.Item(CStr(K))
      debugValues = """"
      For Each Y In subList.Keys
      If (debugValues = "") Then debugValues = debugValues & ", "
      debugValues = debugValues & "Row(" & Y & "): '" & subList.Item(CStr(Y)) & "'"
      Debug.Print "Modifier: " & K, "Values: " & debugValues

      Good luck!

      • kallenconsulting says:

        Thank you for the response, Patrick!

        I threw some stuff out to stack overflow and got some good input. My final solution ended up uglier but more elegant. No “sublist”:

        For theRow = 2 To lastRow
        'Get the string of modifiers in the current row
        rawModifiers = Cells(theRow, modifierColumn).value
        'Break each modifier string in the row into a separate array element
        rowModifiersArray = Split(rawModifiers, ";")
        'Iterate through each of the modifiers and value in the new array
        For Each modResp In rowModifiersArray
        'Seperate the modifier from the response in another temp array, 'singleModifier’.
        'The first element of the array will be the name of the modifier, the second will be the response to the modifier.
        singleModifier = Split(modResp, "|")
        oneMod = singleModifier(0)
        oneResp = singleModifier(1)
        'If the modifier exists as a key in the ModifierList, add the row and the value into the dictionary associated to that key
        If (Not modifierList.Exists(oneMod)) Then
        modifierList.Add oneMod, New Dictionary
        End If
        modifierList.KeyValuePairs(oneMod).value.Add CStr(theRow), oneResp
        Next theRow

        Strangely, the Dictionary doesn’t seem to allow creating Keys as numeric (hence me adding CStr above). But when retrieving the data via the Key, numeric works fine. Addressing KeyValuePairs instead of the Parent object was the… **key** to the solution.

  12. Good to see you got the answer!
    Just be careful of numeric ; an implicit cast of 1 to a string is ” 1″ (space for a sign) but CStr(1) is “1” – with no space
    Good luck

  13. Patrick Schuele says:

    Hi Patrick,
    first thank you for your great work. I searched long to find something useful and i think I found it 🙂
    But I have a problem. A really have no skills in macros and programming but I have to get a xlsm-sheet working on my Mac.
    My english is not the best, I hope you understand what I mean.

    Everytime the macro runs I get this error:
    “Run-time error ‘429’:
    ActiveX component can’t create object.”

    In debugger the “Scripting.Dictionary”-Line is highlighted:
    Dim Kategorie_Sum As Object
    Set Kategorie_Sum = CreateObject(“Scripting.Dictionary”)
    Kategorie_Sum.CompareMode = vbBinaryCompare

    I imported your file and changed to code to:

    Dim Kategorie_Sum As Dictionary
    Set Kategorie_Sum = New Dictionary
    Kategorie_Sum.CompareMode = vbBinaryCompare

    Now the I get a Compilee error:
    “Can’t assign to read-only property”

    Can you help me with that

    Greetings from Germany
    Patrick 😉

    • Thanks, Patrick.
      This is explained in the comments in Dictionary.cls

      ‘ in Scripting.Dictionary this is writeable, here we have only vbtextCompare because we are using a Collection

      So, we can not have case sensitive keys. Sorry about that.
      It might be a good idea for me to define a Set method that raises a more informative error message like the above.

      Good luck

  14. Robert Childress says:

    This is an interesting solution, but I am having trouble understanding the implementation of the file(s). I am developing a macro-heavy Excel spreadsheet for Windows and Mac users. Some of the Mac users are experiencing problems with missing libraries (scrrun.dll, outlctl.dll, msoutl.olb). These files look like a promising solution to scrrun, but I don’t know I can integrate it fully into the code as substitutes for the scrrun-reliant code. Thank you for any help.

  15. actusurlefil says:

    Hi Patrick,

    First of all thanks very much for this useful code, you can’t imagine how much time I spent trying to find an alternative to the dictionaries for Mac.
    However I have to say I can’t really figure out to import this file into excel.

    As explained I tried to import the .cls file into VBA but it says that it’s not a valid VB file… Am I doing something wrong here?


  16. Paul Coffey says:

    Hi Patrick, thanks for your work here, from the many positive comments here it sure seems that you have made a terrific contribution. In my particular example, when I try to import the Dictionary.cls file into my project I see this message: “The form class contained in ~Dictionary.cls is not supported in VBE. The file can’t be imported”. Since I am not a programmer myself, I am going to hunt on oDesk for a freelancer to help me bug fix this. And I’d love to hear from anybody here in the comments stream who might have found (and solved!) a similar problem. (I’m not sure if it will display here but I’ve left the link to my company’s account on oDesk here in the website section).

    • “~Dictionary.cls”
      There is no tilde in front of the file name. Please check your download is just “Dictionary.cls” and try again. I could email it to you directly if you like, as WordPress shows me your address.

      • Samantha says:

        I would just like to add, when I tried to save these files by copying the text into a plain text document, saving them, then changing the extension from .txt to .cls (I first came across this solution on another forum and that’s what was recommended), I got the same “form class not supposed in VBE” error message. However, when I saved the files directly from your website, they imported just fine. Just in case this helps anyone else. Thank you so much Patrick for developing this!

  17. seba says:

    hi Patrick
    worked like a charm on both Mac and Win versions

    thank you


  18. Ettelaiv says:

    Great article! However when I try to use the following code I get a run time error 438 ” object doesn’t support this property or method”
    Set td= new dictionary
    for each elt in MyArray

    Can you help? The error is at ‘td(elt)=1

  19. bella says:

    Sorry the last comment missed some file name, see this one as below:
    Thanks for your contribution!
    I’m using excel for mac 2011, I download the two .cls files, and it automatically save as (Dictionary.cls.txt) file, when I try to import them I cannot choose the file.
    Then I change the file to (Dictionary.cls), still cannot import! What’s the problem?
    After that I try to create a class module, and copy the script in Dictionary.cls to the new class module, and when I compile it, the error shows “expected: end of statement”

  20. Bella
    Did you get the update mentioned:
    update: see https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/

    Import both the KeyValuePair and the Dictionary.cls files.
    If you wish to use copy/paste then insert new class modules named KeyValuePair and Dictionary and then paste in the code from the .cls files starting from the line ‘Option Explicit’, ie do not paste in the lines above; they are only relevant when importing.

    • bella says:

      Thanks very much!
      Now I successfully created the two class modules, and use them in my codes.
      However I come up with a bug,
      “runtime error 438: object doesn’t support this property or method”
      at this line:
      Set Dic(Str) = Cells(i, 1).Resize(, lc)

      could u kindly take a look at my codes? It runs successfully on windows OS, so I think maybe the Dictionary class you created has a different property or method.

      I can send you the .xls file if you kindly provide an email?

      The codes are used to split the data in a worksheet to several worksheets by the different values in one specific column. Firstly take the only values in that column as the keys in dictionary, then match those rows which has the specific value with the specific key, lastly paste them sportily into different sheets.

      Sub SplitColumn()
      Dim Arr, Rng As Range, Sht As Worksheet ‘Dic As Object
      Dim k, t, Str As String, i As Long, lc As Long
      Dim MyColumn As Integer, InPutStr As String
      Dim Dic As Dictionary

      InPutStr = InputBox(“Please input the column to split. Example: A”)
      MyColumn = Range(InPutStr & 1).Column
      Application.ScreenUpdating = False

      Arr = Range(“A1”).CurrentRegion.value
      lc = UBound(Arr, 2)

      Set Rng = Rows(1)
      Set Dic = New Dictionary
      ‘Set Dic = CreateObject(“Scripting.Dictionary”)

      For i = 2 To UBound(Arr)
      Str = Arr(i, MyColumn)
      If Not Dic.Exists(Str) Then
      Set Dic(Str) = Cells(i, 1).Resize(, lc)
      Set Dic(Str) = Union(Dic(Str), Cells(i, 1).Resize(, lc))
      End If
      k = Dic.Keys
      t = Dic.Items
      On Error Resume Next
      With Sheets
      For i = 0 To Dic.Count – 1
      Set Sht = .Item(k(i))
      If Sht Is Nothing Then
      .Add(After:=.Item(.Count)).Name = k(i)
      Set Sht = ActiveSheet
      End If
      Rng.Copy Sht.Range(“A1”)
      t(i).Copy Sht.Range(“A2”)
      Set Sht = Nothing
      End With
      Application.ScreenUpdating = True
      End Sub

  21. Bella,
    I don’t have time look at code or XLS files.
    The usual way to get an element is .Item(key)
    So try Set Dic.Item(Str) = Cells(i, 1).Resize(, lc)
    See the replies above to Thorak and Kallenconsulting, they may help.

    • bella says:

      thanks, I change the line to
      Dic.Add Str, Cells(i, 1).Resize(, lc)
      and it works!!
      another question, is the Key restricted to “String” type? what if I would like a numeric value to be the Key?

  22. .Add will fail if the key already exists. You need to use .Item if updating an existing item.

    Key is always string. A numeric value is implicitly converted to a string.
    See my reply above, September 8, 2014 at 5:34 pm

  23. Fx says:

    You sir, deserve a medal.Thank you very much for posting the custom classes. It saved me a lot of time while dealing with Mac OSX VBA compatibility.

  24. elgatnegre007 says:

    I’m a Excel for Mac 2011 user (on a mackbook) and wish I could use your dictionary class. I never imported such a file into excel. I don’t know what to do with the code in order to try it. How to ‘install’ it?

    I already tried to paste the code lines in two different class modules, but the first lines give me error (are red text, not black, blue or green (so I understand this may not be placed in a module?). I’m lost.

    most of this lines get red coloured when pasted into a class module. must I really paste this code into a class module? must I rename It? what is wrong?

    MultiUse = -1 ‘True
    Attribute VB_Name = “Dictionary”
    Attribute VB_GlobalNameSpace = False
    Attribute VB_Creatable = False
    Attribute VB_PredeclaredId = False
    Attribute VB_Exposed = False

    It would be of a great from you help If you could provide me some steps on what to do with your code to make it operative.

    Excuse my English, I’m from Barcelona.

  25. Jean-Baptiste Quenot says:

    Hi Patrick, thanks for the nice contribution. I wonder if it’s supported to use a Dictionary as value in a Dictionary, eg mainDict.Add “key”, subDict. The instruction causes no error but I get errors on next calls like DebugPrint, Item or any other method of mainDict: “Object doesn’t support this property or method”

    With primitive types there is no error.

    Have you tested this?

  26. No, I had been assuming simple types. You’ll need to modify the class to add a test for IsObject() before the Debug.print, and just print the typename or something generic like that.

  27. Or, simply comment out the Debug statements, otherwise it should work fine.

  28. Raymond Dillon says:

    Hi Patrick,

    I was wondering is there a way to cast your dictionary class on to a worksheet range

    e.g I currently use the below which works OK on PC but not on MAC

    With CreateObject(“Scripting.Dictionary”)
    For Each rCell In ws.Range(“meals”, ws.Cells(Rows.Count, “B”).End(xlUp))
    If Not .Exists(rCell.value) Then
    .Add rCell.value, Nothing
    End If
    Next rCell

    cbMeal.List = .Keys
    End With

    • I’m not sure what you mean by “cast your dictionary class on to a worksheet range”
      My class should work on Mac but I have no Mac to test it on.
      instead of the CreateObject above which is Windows specific you would
      Dim Dic as Dictionary
      Set Dic = new Dictionary
      with Dic
      …etc.. as above

  29. Denis Samokhvalov says:

    Hi Patrick,
    unfortunately it seems that the class does not work with the new Office for Mac 2016 (with the VBA 7 onboard)

    • OK, thanks, Denis. Without a Mac to test it on I cannot say any more.

    • Tim Hall has a version that seems to have worked as a drop-in replacement for the script Patrick shared here:


      I’m on a Mac and recently upgraded from Excel 2012 to Excel 2017. The upgrade from some old version of VBA to VBA 7.1 broke my macro which relied upon Patrick’s Dictionary. I replaced Patrick’s code with Tim’s by cutting-and-pasting the Dictionary.cls and removing the red metadata and comments. Macro works again…

      • I had an extensive look at Tim Hall’s implementation and performance wise it’s “slow”. It grinds to a halt once you add around 1000-2000 items due the internally the use of arrays to maintain various functionality. His idea for handling case sensitive strings is interesting.

  30. Hi Patrick,
    I truly hope you are around today, because I am totally lost, trying to get a VBA to work the way it did before.
    Last year, for freelance work, I inherited an excel workbook from a departed programmer. It was design to take payroll list sheets dropped into the workbook. It would then compare the two list, and populate a third sheet showing employees who were on the current payroll, but not the previous one.
    All I needed to do then was change the columns being referred to because the client had added extra columns in the payroll sheet for department codes.

    This month, the client called back, to say she was now payroll manager for a different company, and she had brought the workbook with her. Because she brought the file to an office running Office 2016, most of the .dlls that let it run before are no longer supported; the ones you wrote your Dictionary and KeyValuePair classes for.
    VBA now works fine on a couple of tutorial examples, with a few minor modifications, but on my main Macro, the Debugger hangs where I’m trying to assign a dictionary’s keys to a variable. The section where this hangs, with relevant declarations, is:

    ‘Check for items in the previous Pay Period and not in the current Pay Period
    Dim dicFileNumCur As Dictionary
    Set dicFileNumCur = New Dictionary

    Dim dicFileNumCurDiff As Dictionary
    Set dicFileNumCurDiff = New Dictionary

    Dim dicFileNumPrevDiff As Dictionary
    Set dicFileNumPrevDiff = New Dictionary
    Dim aa As Variant

    aa = dicFileNumCur.Keys
    For i = 0 To UBound(aa)
    If dicFileNumPrev.Exists(aa(i)) = False Then
    dicFileNumPrevDiff.Add aa(i), dicFileNumCur(aa(i))
    End If
    Next i

    This returns the Compile error: “ByRef argument type mismatch”error, highlighting the “aa” in ” If dicFileNumPrev.Exists(aa(i)) = False Then”

    I tried defining the key array variable differently, as in:
    Dim aa()
    aa = dicFileNumCur.Keys

    But that yields the “Compile error: Can’t assign to array.” focused on “aa =” on the second line.

    i have tried different types of variables to assign to “aa”, but none changes the issue.

    Previous sections of the macro referencing keys of the dicctionaries seem to compile, but it’s just a matter of getting past this section.

    Will keep slogging away at this and try commenting out all of that secion to where other issues might arise. I can forward the entire spreadsheet if needed.

    Thanks in advance

    • is this for Windows?
      You could simply define the dictionaries as Object and use Scripting.Dictionary. Isn’t that what the previous programmer did?
      Anyhow, if you’re getting Type Mismatch at dicFileNumPrev.Exists(aa(i)) I guess that’s because the argument is dicFileNumPrev.Exists(Key as String), but aa is a Variant array so you need to say dicFileNumPrev.Exists(Cstr(aa(i)))

      • Hello! Thanks for your quick reply!
        I am alternating between Windows and the Mac, through the intermediary of OneDrive. The scripts seem to function equally here, but the Windows book is my wife’s, and has that nasty white stripe down the middle of the screen.
        The previous programmer used the declaration:

        Dim dicFileNumCur As New Dictionary
        Changing to “New Scripting.Dictionary” sends the message “Can’t find object or library.”

        So I have bitten the bullet and begun to rebuild the macro from the start, placing as many parts into separate subroutines as I can, so I can debug without running the whole thing.
        My new variable declarations are:

        Public prevPPdict As New Scripting.Dictionary[b] Message: “User type not defined”

        Dim prevPPdict As Object
        Set prevPPdict = New Scripting.Dictionary: “Invalid outside procedure” (Same for just “New Dictionary”)

        Only thing that seems to work is: Public prevPPdict As New Dictionary
        Does defining a Scripting.Dictionary make much difference in functionality? I get slightly different results if I remove your classes, but having them in works better.

        Your other suggestion of using a “CStr” declaration in my other variable has worked, and I was better able to create my variable array with:
        Dim aa()
        aa = dicFileNumCur.Keys

        As I said, I’m rebuilding this whole macro after having read up much more of Dictionaries than I ever had before. My first goal is to pull out items that are in one Dictionary and not the other. I can probably pop the results onto the main worksheet without having to fill out a third dictionary. Then fill in another half of the sheet with those who got paid different amounts between pay periods. Hey, at first I just needed to change the columns being referenced, but now I’ve stepped into a total rewrite because of changes in VBA. I can probably do it, just not my tomorrow!

        Thanks so much for your suggestions.

      • In Windows, you would need to set a Reference to Microsoft Scripting Runtime so that you can declare as Scripting.Dictionary
        If you’re working in Mac, there is no scripting object and hence no dictionary. Unless it’s available by other means I haven’t seen yet. I do not have a Mac so cannot test that now.
        So, that’s why I wrote the Dictionary class. That will work in both Win and Mac.
        I don’t understand what you mean by “I get slightly different results if I remove your classes, but having them in works better.”
        If you remove the classes, you can’t use dictionary objects at all, so you can’t even get results.

        Here’s an idea: instead of using dictionaries, why not just use Collection objects and store the Key and Value the same?
        As there is no .Exists method, you simply try to access the object and catch the error that results.
        Something like

        For i = 0 To collFileNumCur.Count
        on error resume next
        collFileNumPrev.Item Cstr(collFileNumCur(i)) ‘ try to access the item by key
        if err0 then ‘ it was not found
        collFileNumPrevDiff.Add collFileNumCur(i),collFileNumCur(i) ‘ add it to the differences collection
        end if
        next i

        I may not have understood what your code is doing, so be sure the above is what you intend.

      • Thanks again. I was trying to preserve the original programmer’s macro, but now that I’m working more with it, I see some other options. Because, if I’m not mistaken, I can’t seem to do an .exists function on the Keys of a dictionary. I see many recommendations to just reverse the Items and Keys, but since this is a list of names, there’s a chance some on the list will have the same name. I’m almost thinking it would be simpler to run an IF… THEN loop through the worksheets themselves and copy over non-duplicating lines.
        I know this project is a little hard to explains: basically my client, an HR director, drops a worksheet with payroll data from the current pay period, and renames the previous pay period sheet. She then wants to build two lists on the main sheet: Employees on the current payroll sheet who were not on the previous payroll. Then employees whose weekly earning are different from one pay period to the next. I picked this up as a freelance job through a temp agency. I’ve done a couple VBA jobs pretty well, considering I’ve had no training; just a superficial understanding of logic in data.

        I was waylaid today by a kidney stone. I’ll pick this up tomorrow and see if I can use my more primitive method.

        Thanks again for your help and support!

  31. athos says:

    thank you so much. my company policy prohibited importing scrlib.

  32. Daan de Wilt says:

    Hi, great to see a solution, but I can’t get it to work somehow..
    I imported the updated Dictionary.cls and KeyValuePair.cls and imported it in my Excel workbook where I have the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Dn As Range
    Dim Rng As Range
    Dim nRng As Range
    Dim Dic As Object
    Dim Ac As Long
    Dim k As Variant
    Dim p As Variant, Sp As Variant
    Dim c As Long, Wk As Long, n As Long
    With Sheets(“Sheet1”)
    Set Rng = .Range(“C3”, .Range(“C” & Rows.Count).End(xlUp))
    End With
    Set nRng = Rng.Offset(, 1).Resize(, 52)
    If Not Intersect(nRng, Target) Is Nothing Then
    Set Dic = CreateObject(“Scripting.Dictionary”)
    Dic.CompareMode = 1
    For Each Dn In Rng
    For Ac = 1 To 52
    If Dn.Offset(, Ac).Value “” Then
    If Not Dic.exists(Dn.Offset(, Ac).Value) Then
    Set Dic(Dn.Offset(, Ac).Value) = CreateObject(“Scripting.Dictionary”)
    End If

    If Not Dic(Dn.Offset(, Ac).Value).exists(Dn.Value) Then
    Dic(Dn.Offset(, Ac).Value).Add (Dn.Value), Rng(1).Offset(-1, Ac).Value
    Dic(Dn.Offset(, Ac).Value).Item(Dn.Value) = Dic(Dn.Offset(, Ac).Value).Item(Dn.Value) _
    & “, ” & Rng(1).Offset(-1, Ac).Value
    End If
    End If
    Next Ac
    Next Dn

    With Sheets(“Sheet2”)
    For Wk = 1 To 52
    For Each k In Dic.Keys
    If Val(k) = Wk Then
    c = c + 1
    .Cells(c, 1) = “Week ” & k
    c = c + 1
    For Each p In Dic(k)
    .Cells(c, 1) = p
    Sp = Split(Dic(k).Item(p), “, “)
    For n = 0 To UBound(Sp)
    .Cells(c, 2) = Sp(n)
    c = c + 1
    Next n
    Next p
    End If
    Next k
    Next Wk
    End With
    End If
    End Sub

    What do I need to change here to get it to work?!

    • “I can’t get it to work somehow..” doesn’t give me much to go on. What is the difference between what it is doing and what you want to do?

      • Daan de Wilt says:

        Sorry for the indistinctness. I get an ‘ActiveX component can’t create object’ error on my Mac. On Windows it works, but not on MacOS, so I found your page and hoped this would be a solution for me too. I’m not that good in VBA, but I understand some things of coding with VBA.. I tried to implement what you’ve typed above:

        “To use it, import the file into your project, then use
        Dim td as Dictionary
        Set td = new Dictionary”

        But I think I overlooked something..??

      • Daan de Wilt says:

        This is how it works:

        I have 2 sheets: On Sheet 1 on row 3 in Column E there’s a name and from Column F to AD are weeknumbers (1 to 51). Per action you can fill in a weeknumber which that action has to take place. On Sheet 2 I need to insert a name with rows depending how many actions that week need to be done.

        SHEET 1
        Toprow (row2), beginning on column E and the actions from F to AD:
        Columns: E F G … …. …
        ROW 2 :Name | Action 1 | Action 2 | Action 3 | Action 4 | Action 5 | Action 6 | Action 7 | Action 8 | Action 9 | and so on…

        Datarow (row3 to row X) where on column E is the name and F to AD presenting the weeknumbers:
        ROW 3 :John | 1 | 5 | 15 | 1 | …

        Now, on Sheet 2, I have the weeknumbers below eachother in Column A:

        SHEET 2
        Week 1

        Week 2


        What I need is to put the name in the right week with the right actions (insert rows based on how many 1’s, 2’s etc.)… So, if John has actions in week 1, his name has to be under Week 1, if there are two 1’s in the range F to AD, there has to be 2 rows inserted with the name of the actions:

        SHEET 2
        Week 1
        Name | Actionname
        John | Action 1
        Action 4

  33. You didn’t say what line gave you that error but reading your code I guess it’s
    Set Dic = CreateObject(“Scripting.Dictionary”)
    “Set Dic(Dn.Offset(, Ac).Value) = CreateObject(“Scripting.Dictionary”)”
    That’s WIndows only.
    If you correctly imported those two files as classes, all you need is
    Set Dic = new Dictionary
    Set Dic(Dn.Offset(, Ac).Value) = new Dictionary

    • Daan de Wilt says:

      Thanks for your reply! I replaced it, but he gives an error on: “Dic.CompareMode = 1”.
      The error says: “Can’t assign Read-only property”. Well, I found someone who rewrote the code so it won’t use the custom Dictionary. This is the corrected code:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Dn As Range
      Dim Rng As Range
      Dim nRng As Range
      Dim c As Long
      Dim Ac1 As Long
      Dim Ac2 As Long
      Dim n As Long
      Dim R As Range
      With Sheets(“Sheet1”)
      Set Rng = .Range(“C3”, .Range(“C” & Rows.Count).End(xlUp))
      End With
      Set nRng = Rng.Offset(, 1).Resize(, 52)
      ReDim ray(1 To 52) As Range
      If Not Intersect(nRng, Target) Is Nothing Then
      For Each Dn In Rng
      For Ac1 = 1 To 52
      For Ac2 = 1 To 52
      If Dn.Offset(, Ac2).Value = Ac1 Then
      If ray(Ac1) Is Nothing Then Set ray(Ac1) = Dn.Offset(, Ac2) Else Set ray(Ac1) = Union(ray(Ac1), Dn.Offset(, Ac2))
      End If
      Next Ac2
      Next Ac1
      Next Dn
      With Sheets(“Sheet2”)
      For n = 1 To 52
      If Not ray(n) Is Nothing Then
      c = c + 1
      .Cells(c, 1) = “Week ” & ray(n)(1)
      For Each R In ray(n)
      c = c + 1
      .Cells(c, 1) = Cells(R.Row, “C”)
      .Cells(c, 2) = Cells(2, R.Column)
      Next R
      End If
      If Not ray(n) Is Nothing Then c = c + 1
      Next n
      End With
      End If
      End Sub

      Many thanks for the help!

  34. Great you got the problem solved more simply! You’re using the builtin Range collection.
    Yes, my Dictionary is limited to text compare mode only, as the comment below indicates,
    So in fact “Dic.CompareMode = 1” is not needed as it is always 1.

    ‘ in Scripting.Dictionary this is writeable, here we have only vbtextCompare because we are using a Collection
    Public Property Get CompareMode() As VbCompareMethod
    CompareMode = vbTextCompare ‘=1; vbBinaryCompare=0
    End Property

  35. Mr. O’Beirne, thanks very much for making this. I spent hours making a tool for a small group of my co-workers, leveraging dictionaries in many places (as is my habit), only to discover that dictionaries do not work on Macs. Looking forward to testing out this class! Thanks again! –pd

  36. Celso Pereira Neto says:

    If you find this useful, let me know!
    Just as I recently discovered Scripting.Dictionaries are not available on Macs, I had to refactor a whole project that leaned on this data structure.
    If it wasn’t for your helpful code, I would just give up and run to the hills just to be nowhere seen.

  37. Evan says:

    Thank you very much for this! I’m running into an error, however, on this line of my code:
    Set RngList = CreateObject(“Scripting.Dictionary”)
    I imported the .cls files and I added the declared td as Dictionary, and Set td = New Dictionary. Am I forgetting/missing something?

  38. Evan, it confuses me to read Set RngList = CreateObject(“Scripting.Dictionary”)
    RngList reads to me as an Excel range, like Activesheet.Range(“A1:Z10”)
    CreateObject is a Windows VBA function to return a COM object that does not exist on the Mac.
    Perhaps if you show more of your code I’ll know what you intend to do. See the reply by Daan de Wilt above.

    • Evan says:

      Of course! Basically, I have a list of items, some of which are similar. So The code below searches for similar items, groups them together and inserts 3 blanks rows after each group. I don’t have it shown (would have made a long reply!), but then it totals all of the data for each group. It seems likely to me I’m going to have to run an If statement and check to see if the system is a Mac or Windows and run separate code?

      Sub InsertRowsandSum()
      Application.ScreenUpdating = False
      Dim LastRow As Long, product As Range, rng As Range, val As String, RngList As Object, foundVal As Range, Item As Variant, x As Long: x = 2
      Dim k As Integer, td As Dictionary
      Set td = New Dictionary

      Set RngList = CreateObject(“Scripting.Dictionary”)
      LastRow = Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      LastRow = LastRow – 4
      Set rng = Range(“B4:B” & LastRow)
      For Each product In rng
      val = Mid(product, 1, Excel.WorksheetFunction.Find(” “, product, WorksheetFunction.Find(” “, product) + 1))
      If Not RngList.Exists(val) Then
      RngList.Add val, Nothing
      End If
      Next product
      For Each Item In RngList
      Set foundVal = rng.Find(Item, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
      k = 1
      Do While k < 4
      Rows(foundVal.Row + k).Insert
      k = k + 1

      • Simply use
        Set RngList = new Dictionary
        and as long as the Dictionary class is in the project, it will be used. You can’t use any Scripting.Dictionary methods other than the ones I provided in the Dictionary class.

        If you need any of the Windows methods then you can use Conditional Compilation

        #If Mac then
        Set RngList = new Dictionary
        #Else ‘Win
        Set RngList = CreateObject(“Scripting.Dictionary”)

        But any time you call a Windows-only method you’ll have to handle the #If Mac case there too.

      • Evan says:

        Ah ok, perfect! I’ll give this a shot, thanks so much for your help!

  39. Pingback: macos - VBA (Excel) Dictionnaire sur Mac?

  40. Hello, I’ve completed IDictionary using interfaces, just doing performance testing and completed most of the unit testing. Hopefully will post it up on Github at https://github.com/MarkJohnstoneGitHub/VBA-IDictionary in a few weeks once finalized the presentation of the performance comparisons.

    Initially, I based it off a Collection and adding the KeyValuePair object and added the functionality of case sensitive string keys and enumeration which features you were missing.

    When it came down to the performance testing it ran into a major bottleneck when cleaning up the KeyValuePair objects for large datasets. i.e. when setting the Dictionary object to Nothing. It starts becoming noticeable adding datasets of 100,000+

    It appears it’s the VBA garbage collection that’s is slow when cleaning up objects for all data structures. I tested the Scripting.Dictionary , Arrays, Collection etc and all had similar performance for cleaning up the same large-sized data set containing objects.

    That’s why I’ve switched from using a KeyValuePair object and instead wrapped each key, value pair using the Array() function. i.e. Where keyValuePairs is a collection object:
    keyValuePairs.Add Item:=Array(Key, Item), Key:=encodedStringKey

    That significantly improved the performance when cleaning up the collection object when keys and/or items are not objects, as not creating always creating and storing objects for each key, value pair. There was also a noticeable performance improvement when adding using the Array function as had fewer overheads than creating the KeyValuePair object.

    The issue from the users perspective it appears that the application has hung for large datasets containing objects, however, VBA is doing its garbage collection of objects. This can take up at least a minute for datasets containing 1 million objects. The overall lesson attempt to avoid creating large datasets containing objects. 🙂

  41. Zollie says:

    Are there known limits to Dictionary nesting? I’ve been trying to debug a stack issue (‘Run-time error ’28’: Out of stack space) involving a nested dictionary. It looks something like:

    {“GroupName”: {
    “SubGroups”: {
    “SubGroupName”: “”

    There are currently 3 unique groups and at most 24 unique subgroups.

    In any case, thanks for this tool Patrick.

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