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)
Next
use one call to return the array and then index into that.
vItems = td.Items
For i = 1 To 1000
  var = vItems(i - 1)
Next

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!

Advertisements

About Patrick O'Beirne, spreadsheet auditor

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

55 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.

  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
    ————————–

    FYI:
    http://www.thijsschreijer.nl/blog/?p=48

  9. Thorak says:

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

    Given:
    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
    To:
    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:
    ‘MODIFIER1|;MODIFIER2|;MODIFIER 3| …

    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
    ‘THE LINE BELOW THROWS THE ERROR
    modifierList(oneMod) = New Dictionary
    End If
    modifierList(oneMod)(theRow) = oneResp
    Next modResp
    Next theRow

    ‘Debug
    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) & “‘”
    Next
    Debug.Print “Modifier: ” & K, “Values: ” & debugValues
    Next
    ‘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
      'THE LINE BELOW THROWS THE ERROR
      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)) & "'"
      Next
      Debug.Print "Modifier: " & K, "Values: " & debugValues
      Next

      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
        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
    P

  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
      Patrick

  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?

    Thanks
    V.

  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

    Seba

  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
    td(elt)=1
    Next
    V=td.keys

    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)
      Else
      Set Dic(Str) = Union(Dic(Str), Cells(i, 1).Resize(, lc))
      End If
      Next
      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
      Else
      Sht.Cells.Clear
      End If
      Rng.Copy Sht.Range(“A1”)
      t(i).Copy Sht.Range(“A2”)
      Sht.Cells.EntireColumn.AutoFit
      Set Sht = Nothing
      Next
      End With
      Sheets(1).Activate
      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?

    VERSION 1.0 CLASS
    BEGIN
    MultiUse = -1 ‘True
    END
    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)

  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.
        Mark

      • 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!
        Mark

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s