I’ve uploaded a new version of the Dictionary Class and a helper KeyValuePair Class. This exposes the KeyValuePairs collection so iteration is now very fast, about 2 seconds to iterate 1 million items using a for each loop:
dim oKeyValuePair as KeyItemPair For Each oKeyValuePair In td.KeyValuePairs ' direct access to collection var = oKeyValuePair.Value 'either of .key,.value Next
Updated 25-Nov-11: The need for the helper class is to allow updates to the .Item without changing the order. 23-Oct-2018: KeyValuePair.cls updated. 09-Apr-2021: above two links refreshed.
Screenshots of import process
Now repeat for KeyValuePair.cls
Patrick O'Beirne @ sysmod 
Pingback: Dictionary Class in VBA instead of Scripting.Dictionary | Patrick O'Beirne @ sysmod
Thanks for the quick reply. I will get try that tomorrow; sound like that should do it.
Partially successful. Added the code to my wbk and it works. However, I want to save the wbk as a Excel 2007 macro-enabled template. I can save it, but when I start a new wbk with the template, I am not allowed to save the wbk. Error message says VB project will be lost but it will not save either with or without the project. Is there anything about your code that would restrict version (e.g. Excel 2003)?
I’ve just tried creating a .xltm template in Excel 2010 and when I create a workbook from it, and save, the only thing I need to do is to specify xlsm (macro enabled) format otherwise it would default to xlsx which of course is without macros.
If I do click Yes to save without the VBA project, it does it and the xlsx can be closed and opened again normally.
I also tried it in Excel 2003, in case that’s what you’re using, and it works fine saving as .xls and .xlt
When you say “will not save either with or without the project”, what does it do, what error message do you get, what file name are you using and what file format are you specifying?
Yes, I have verified that your code does work when saved as a template. My problem seems to be coming from something else I am doing in my workbook that is affecting the workbook after it is saved as a template (I’m using Excel 2007). When I open a new workbook with the template file and then try to save it, it refuses to retain the VP project and will not save even if I ask it to continue without the VB project. No error messages of any kind. So it isn’t anything you have done, it’s me. Thanks anyway for your attempts to help me.
If you like, try the save again and grab the screen with the refusal message (PrtSc, paste into Word or Paint) and email it to me : mail3 at my sysmod.com site.
Just tested this out and it worked beautifully. I had to adjust a couple of my calls to conform better with the syntax but was pretty easy work. Thanks for this.
Hi Patrick,
Could you kindly show me an simple example implementation of this for a custom collection class? For example, say I want to create mcTasks that is a collection of mcTask. The mcTask has three attributes: TaskID (as String), RoleID (as Integer), and TaskDesc (as String), with a conjugate key as TaskID+RoleID.
I am not very good at it and am trying to learn the subject. A simple example will be very helpful for me to see the connections and usage and will provide significant clarity.
Also, I am on a Mac using Excel 2011, if that is of any value.
Thank you so very much.
You’re asking about how to create your own class. I don’t have a tutorial on that, here are some links:
http://www.cpearson.com/excel/classes.aspx
http://stackoverflow.com/questions/2161666/what-are-the-benefits-of-using-classes-in-vba
http://www.wiseowl.co.uk/blog/s237/classes.htm
http://dailydoseofexcel.com/archives/2010/07/04/custom-collection-class/
http://ramblings.mcpher.com/Home/excelquirks/snippets/classes
sysmod,
I’m attempting to use your Dictionary class. When I put it in a module the VERSION and Attribute lines at the top of the file are causing a compile error. What don’t I know about that syntax?
Thanks for adding ” You can ignore my previous post. I found information on how to import your code and it worked fine. Thanks for providing this valuable code.”
Just in case anyone else has this problem, I should add here: Use the VBE to Import the code, don’t copy & paste the file in.
Ribbon > Developer menu > Visual Basic
or press Alt+F11
Select your project.
File > Import File
Select the .bas file
Thanks,
Patrick
Pingback: Excel Instrumentation: Activity Tracking with Visual Basic for Applications | mmatthews06
This does not work on a Mac Excel with the above mentioned Dictionary and KeyValuePair classes.
Sub test()
Dim dict As Dictionary
Set dict = New Dictionary
Dim values(1 To 2) As String
values(1) = “one”
values(2) = “two”
dict.Add “key”, values
MsgBox dict(“key”)(1)
MsgBox dict(“key”)(2)
End SubÏ
Under a windows excel one gets two successive messages:
“one”
“two”
Jiri
Hi Jiri
When you run it in Mac what do you get? I don’t have a Mac to test with.
Run-time error ‘438’
Object does not support this property or method.
… with this row highlighted:
MsgBox dict(“key”)(1)
Ok, let’s break in into steps:
try
dim v as variant
dict.Add “key”, values
v = dict(“key”)
debug.print typename(v),lbound(v),ubound(v)
debug.print v(1)
The Test2() says:
Run-time error 438
Object does not support this property or method.
v = dict(“key”) ‘ This row HIGHLIGHTED
Sub test2()
Dim dict As Dictionary
Set dict = New Dictionary
Dim values(1 To 2) As String
values(1) = “one”
values(2) = “two”
Dim v As Variant
dict.Add “key”, values
v = dict(“key”) ‘ This row HIGHLIGHTED
Debug.Print TypeName(v), LBound(v), UBound(v)
Debug.Print v(1)
End Sub
Thanks, Jiri,
I see you are using the default property dict(“key”) rather than dict.item(“key”)
v = dict.item(“key”) works in Windows.
In which case I recommend you make the change in the code offered by Tachy on March 26 at the original post :
https://sysmod.wordpress.com/2011/11/02/dictionary-class-in-vba-instead-of-scripting-dictionary/
Good luck
P
Finally it works! It was a hell to make it right.
=====
(according to Tachy, March 26)
1. Insert:
Attribute Item.VB_UserMemId = 0
after
Public Property Get Item(Key As String) As Variant
(The result looks like this ..)
Public Property Get Item(Key As String) As Variant
Attribute Item.VB_UserMemId = 0
AssignVariable Item, KeyValuePairs.Item(Key).Value
End Property
(according to http://productivebytes.blogspot.cz)
2. You’ll get a “Syntax Error” – Ignore it.
3. Export the Class
4. Remove the Class
5. Reimport it again.
======
What a piece of sh** is this Microsoft producing … !?!
Thanks Patrick!
Jiri
Helpful post Jiri, thanks. Seems to work perfectly so far.
Hello,
How do you insert the code into the Visual Basics Editor for Excel for Mac 2016?
I don’t have a Mac, but the first result for Google is
http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac/importing-macro-modules-into-excel-visual-basic/905b01cf-78ce-4bfc-9f93-9f7dda137101
BobGreenblatt replied on October 1, 2015″Can’t be done! The Mac Excel 2016 just does not have this capability. You can use a text editor to open the exported code and then copy what you want and paste it into an existing module.”
Also see recommendations to go back to Office 2011:
http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac/visual-basic-support-in-mac-2016/4afbba04-4435-4970-8b02-40da006a590e
I used your Dictionary implementation for some years. Thanks a lot!
Now I’ve upgraded to Excel 2016 and get errors 😦
When I query a value, “KeyValuePairs.Item(Key).value” returns Null, even though the entry exists. I’ve fixed this with a temporary KeyValuePair, where the result of .Item(Key) is written to first.
When trying to update a stored value I get the error “Variable uses an Automation type not supported” (https://support.office.com/en-us/article/-variable-uses-an-automation-type-not-supported-error-in-visual-basic-editor-in-excel-for-mac-c3125bf3-c30b-4d62-be42-d2f1bec0e5ad). As workaround I am now again removing the value from the collection first and then adding it again.
Do you know a better solution?
Regards
Jon, I’m just catching up on a backlog at the moment, but I have seen your post and will look at it on a Mac when I get a chance.
Jon, I’ve reproduced the problem and filed a bug report with Microsoft.
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_officeinsider-mso_mac-msoinsider_excel/excel-1613-for-mac-user-defined-class-passed-as/b94e9fd4-65d3-4bfa-8a61-f10610945b6b
I’ll fix the Dictionary classes and re-post later.
Updated and uploaded as http://www.sysmod.com/Dictionary.zip
Patrick,
I found a typo when trying to make the item of a Dictionary an Object type.
In the setter property of the KeyValuePair class, you have “Set Value = obj”, which causes an infinite loop. If you change it to “Set Value_ = obj”, it works as expected
Thanks, Steve, well caught! I fixed the file now.
Looks great some things of concern thou one being the public members KeyValuePairs to allow iteration.
To allow iteration for custom collections you could add the code below. Note: must add the Attribute in a text editor. That should allow you to encapsulation the class members by setting to private and allow iteration. I’ll have to test myself as currently exploring custom lists where can implement an IList template or IKeyedList one I’m especially interested in. That way it doesn’t matter what the implementation is whether it’s a scripting dictionary or a collection based replacement dictionary.
Public Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
Set NewEnum = KeyValuePairs.[_NewEnum]
End Property
The other inconsistency with scripting.dictionary is the Public Property Get Exists(Key As String) As Boolean it shouldn’t it be a function? i.e. Public Function Exists(Key as String) As Boolean
Same for Public Property Get Count() As Long it “probably” should be a function as well. 🙂 If consistent across the scripting dictionary and other implementations of using collection based dictionary replacements can implement an interface.
Thanks for the ideas Mark. I have not implemented Interfaces ; you could post one yourself, and explain what the benefits are over the simpler form here.
Thanks will try and explain in another reply. First of I suggested Count should be a function. Apologies I got mixed up with Collection and Scripting. Dictionary when checking the Object Browser. Strangely enough, Collection has the Count as a function.
The scripting dictionary has Count as a Property.
Same goes for items and keys should a function to keep consistent with the scripting.dictionary .
Thanks a lot. In fact I feel lost. Can you post the latest version of the two classes : Dictionary.cls and KeyValuePair.cls?
Updated and uploaded as http://www.sysmod.com/Dictionary.zip
I have not made any of the changes markjohnstone suggested.
I’ve been using your dictionary class extensively in a project I’m working on and it’s been very helpful, thanks for making it available. I’m wondering if you ( or another reader) have ever needed to sort the dictionary and have some advice on doing that? I’m typically using the dictionary to hold objects so I expect I’ll need to somehow specify the object field to sort on.
I see Chip Pearson’s site has some code examples and I’ll look into using those but if the work has already been done for this library that that would be a bonus. He also suggests where the dictionary holds an object, outputting the dictionary to an array, sorting and then recreating the dictionary.
http://www.cpearson.com/excel/CollectionsAndDictionaries.htm
I’ve never needed to sort it. I’d dump the keys to a sheet, add an index, sort, and reload a new collection in that order. Or use Chip’s code to heapsort an array.
Thanks for your reply. In the end I used Chip’s code, modified with inheritance to handle the different objects I want to sort. It was helpful that the dictionary class already has the .items method that returns an array.
Hello, congrats for the idea & the fantastic job done.
Unhappily, I can’t get the files from the provided link!
Would you please update the link as guessing the needed code is a bit confusing and lambirintic due the quantity of comments.
Thank’s a lot
Hi joanbaptist, I’ve re-uploaded the two .cls files linked from the top post. Please right-click on each and Save As… on your local PC. Then in VBA use File>Import to import each one.
HTH
Patrick
Hello. Thanks a lot for your page and your examples. I want to ask you about something. I built a Excel DNA addInn complement. and i want to make an excel file with macros in VBA. How can i call my functions from Excel DNA? i try with Application.WorksheetFunction.MyFunction(Range) but it’s doesn’t work. Can you help me?
Thank you very much.
Thanks Alvin.
“Application.WorksheetFunction.” is for Excel functions, you don’t need that to call an addin function, just its name.
Support for the excellent Excel-DNA is on their Google Group:
https://groups.google.com/g/exceldna
Hello,
Thanks for this post but I have been unable to figure how to use this dictionary. Basically I want to extract a range of unique values from a range. Could you please advise how to transform this as per the new dictionary. Thanks very much!
Dim dict As Object: Set dict = CreateObject(“Scripting.Dictionary”)
dict.CompareMode = vbTextCompare
Dim arg As Range
Dim cel As Range
Dim Key As Variant
For Each arg In crg.Areas
For Each cel In arg.Cells
Key = cel.Value
If Not IsError(Key) Then
If Len(Key) > 0 Then
dict(Key) = Empty
End If
End If
Next cel
Next arg
Dim rCount As Long: rCount = dict.Count
If rCount > 0 Then
‘ Write unique values from Unique Dictionary to Data Array.
Dim Data As Variant
Dim i As Long
ReDim Data(1 To rCount, 1 To 1)
For Each Key In dict.Keys
i = i + 1
Data(i, 1) = Key
Next Key
End If
I don’t know Alvin. The question is not related to the blog post.
@Crrepyboy :
1) Import into your VBA project the two files Dictionary.cls and KeyValuePair.cls
2) Remove the line dict.CompareMode = vbTextCompare, that property is not available in this class.
3) The line dict(Key) = Empty relies on the auto-adding feature of the Windows Scripting.Dictionary. Replace it with
If Not dict.Exists(CStr(Key)) Then dict.Add CStr(Key), Key
That then runs the same as the original code.
Hope this helps
Patrick
Hello, can you tell me more about how to import this in excel ? Do I have to ask the users to open the vba editor and copy the code or there is a much simplier way to import the code ? Thanks.
Thanks for asking Ratrack. Simply go into the VBE Editor and File > Import into your VBA project the two files Dictionary.cls and KeyValuePair.cls
Hi Patrick. When I import the files into Excel, they always import as a normal module and not as a “Class Module”.
The lines before “options explict” don’t compile and Excel does not recognize the module this way.
When I insert a Class Module and copy/paste, then Excel recognizes the classes but it does not compile the lines before “options explicit”
I tried in Mac and Windows. I’m using Office 365
Hi Eduardo,
In the VBE, select the project of the workbook you want to import it to.
File > Import File > pick the .cls file, OK
A new class module appears correctly.
I’ll edit the post above and put in pictures.
Hi Patrick,
I am a bit new to such an advanced level of vba scripting.
Want to understand how can I access the value of the dictionary key when using your classes?
I am trying following ways, and none of these are giving me the result.
?MyDict.key(“key”)
?MyDict.key(“key”).value
Thanks.
Hello Vaseem, when you know the key, use .Item to index the collection, eg
? MyDict.item(“key”) give you the value for the key “key”.
https://ogrharranedu-my.sharepoint.com/:x:/g/personal/195107020_ogrenci_harran_edu_tr/EerT7vDuFMJLrSMl7fxqm8UBPVFU8Nn_JtpOnh_XI7cPew?e=VFQwAN
I knew I could run this file on windows computer, but it doesn’t work on MacBook, it gives an error. I applied what you said, but it still didn’t work, I would appreciate it if you could help with this.
What is the error?