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
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!
Patrick O'Beirne @ sysmod 
Thank you! That’s one less thing on my own to-do list.
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.
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
Yes, those ideas are good.
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?
You also need to download and import KeyValuePair.cls from
Patrick, is this implementation in the public domain? It works well, thanks for posting.
Yes, it’s put out there for people to use and improve upon 🙂
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.
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!
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
That’s a neat little extra, thanks, Tachy.
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.
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
OK, thanks, if it works for you that’s great!
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|< User Response for Mod 1>MODIFIER2|< User Response for Mod 2>MODIFIER 3|< User Response for Mod 3> …
Thanks for commenting Scott
I recommend you use Option Explicit and then define variables like
dim lastRow as longI 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 DictionarymodifierList.Add oneMod, subList
You can use that in the Else clause as
modifierList.Item(oneMod).Add Str(theRow), oneRespand 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!
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.
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
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
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.
To use it, import the file into your project, then use
Dim td as Dictionary
Set td = new Dictionary
just as you would do with the MS Dictionary object.
See the update at
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.
I don’t have a Mac to hand, so I don’t know the cause of that message.
To use it, import the file into your project, then use
Dim td as Dictionary
Set td = new Dictionary
just as you would do with the MS Dictionary object.
See the update at
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.
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!
hi Patrick
worked like a charm on both Mac and Win versions
thank you
Seba
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
See the replies above to Thorak and Kallenconsulting
Thanks for replying! Looks quite complicated but I’ll try to extract from it my answer
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”
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.
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
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.
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?
.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
I see, thanks again, it helps me a lot!
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.
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.
Hi elgatnegre007
Please see the UPDATE to this class mentioned at the top of the post:
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
Thanks,
Patrick
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?
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.
Or, simply comment out the Debug statements, otherwise it should work fine.
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
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:
https://github.com/VBA-tools/VBA-Dictionary
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.
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
thank you so much. my company policy prohibited importing scrlib.
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
Else
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”)
.Range(“A:B”).ClearContents
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?
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..??
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
etc..
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
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”)
.Range(“A:B”).ClearContents
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!
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.
Code:
‘ 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
Another update at https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/
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
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.
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?
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.
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
Loop
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”)
#Endif
But any time you call a Windows-only method you’ll have to handle the #If Mac case there too.
Ah ok, perfect! I’ll give this a shot, thanks so much for your help!
Pingback: macos - VBA (Excel) Dictionnaire sur Mac?
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. 🙂
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.
Maybe the structure is circular or recursive somewhere?
I just tried a simple recursive nested dictionary in 32-bit Excel Office 365 and it ran out of stack space at 4953 levels.
Without the dictionary, I get out of stack space at 5693 levels.
It depends on a number of factors:
https://stackoverflow.com/questions/1866025/is-there-a-call-stack-level-limit
Thanks for linking the StackOverflow post. Exhausting the call stack was probably the cause – successfully refactored now.