VBA (Excel) Dictionary on Mac? VBA (Excel) Dictionary on Mac? vba vba

VBA (Excel) Dictionary on Mac?


Pulling the Answer from the comments to prevent link rot.

Patrick O'Beirne @ sysmod wrote a class set that addresses this issue.

Be sure to stop by Patirk's Blog to say thanks! Also there is a chance he has a newer version.

save this as a plain text file named KeyValuePair.cls and import into Excel

VERSION 1.0 CLASSBEGIN  MultiUse = -1  'TrueENDAttribute VB_Name = "KeyValuePair"Attribute VB_GlobalNameSpace = FalseAttribute VB_Creatable = FalseAttribute VB_PredeclaredId = FalseAttribute VB_Exposed = FalseOption Explicit'Unrestricted class just to hold pairs of values together and permit Dictionary object updatingPublic Key As StringPublic value As Variant

save this as a plain text file named Dictionary.cls and import into excel

VERSION 1.0 CLASSBEGIN  MultiUse = -1  'TrueENDAttribute VB_Name = "Dictionary"Attribute VB_GlobalNameSpace = FalseAttribute VB_Creatable = FalseAttribute VB_PredeclaredId = FalseAttribute VB_Exposed = FalseOption Explicit'Collection methods: Add, Count, Item, Remove'Dictionary : .Add(Key as string, Item as variant), .CompareMode, .Count, .Exists(Key); _   .Item(Key) - writeable, .Items, .Keys, .Remove(Key), .RemoveAll'plus KeyValuePairs collection, KeyValuePair(Index as long), Tag as variant' 25-11-2011 KeyValuePair helper objectPublic KeyValuePairs As Collection ' open access but allows iterationPublic Tag As Variant            ' read/write unrestrictedPrivate Sub Class_Initialize()   Set KeyValuePairs = New CollectionEnd SubPrivate Sub Class_Terminate()   Set KeyValuePairs = NothingEnd Sub' in Scripting.Dictionary this is writeable, here we have only vbtextCompare because we are using a CollectionPublic Property Get CompareMode() As VbCompareMethod   CompareMode = vbTextCompare   '=1; vbBinaryCompare=0End PropertyPublic Property Let Item(Key As String, Item As Variant)    ' dic.Item(Key) = value ' update a scalar value for an existing key   Let KeyValuePairs.Item(Key).value = ItemEnd PropertyPublic Property Set Item(Key As String, Item As Variant)    ' Set dic.Item(Key) = value ' update an object value for an existing key   Set KeyValuePairs.Item(Key).value = ItemEnd PropertyPublic Property Get Item(Key As String) As Variant   AssignVariable Item, KeyValuePairs.Item(Key).valueEnd Property' Collection parameter order is Add(Item,Key); Dictionary is Add(Key,Item) so always used named argumentsPublic Sub Add(Key As String, Item As Variant)   Dim oKVP As KeyValuePair   Set oKVP = New KeyValuePair   oKVP.Key = Key   If IsObject(Item) Then      Set oKVP.value = Item   Else      Let oKVP.value = Item   End If   KeyValuePairs.Add Item:=oKVP, Key:=KeyEnd SubPublic Property Get Exists(Key As String) As Boolean   On Error Resume Next   Exists = TypeName(KeyValuePairs.Item(Key)) > ""  ' we can have blank key, empty itemEnd PropertyPublic Sub Remove(Key As String)   'show error if not there rather than On Error Resume Next   KeyValuePairs.Remove KeyEnd SubPublic Sub RemoveAll()   Set KeyValuePairs = Nothing   Set KeyValuePairs = New CollectionEnd SubPublic Property Get Count() As Long   Count = KeyValuePairs.CountEnd PropertyPublic Property Get Items() As Variant     ' for compatibility with Scripting.DictionaryDim vlist As Variant, i As LongIf Me.Count > 0 Then   ReDim vlist(0 To Me.Count - 1) ' to get a 0-based array same as scripting.dictionary   For i = LBound(vlist) To UBound(vlist)      AssignVariable vlist(i), KeyValuePairs.Item(1 + i).value ' could be scalar or array or object   Next i   Items = vlistEnd IfEnd PropertyPublic Property Get Keys() As String()Dim vlist() As String, i As LongIf Me.Count > 0 Then   ReDim vlist(0 To Me.Count - 1)   For i = LBound(vlist) To UBound(vlist)      vlist(i) = KeyValuePairs.Item(1 + i).Key   '   Next i   Keys = vlistEnd IfEnd PropertyPublic Property Get KeyValuePair(Index As Long) As Variant  ' returns KeyValuePair object    Set KeyValuePair = KeyValuePairs.Item(1 + Index)            ' collections are 1-basedEnd PropertyPrivate Sub AssignVariable(variable As Variant, value As Variant)   If IsObject(value) Then      Set variable = value   Else      Let variable = value   End IfEnd SubPublic Sub DebugPrint()   Dim lItem As Long, lIndex As Long, vItem As Variant, oKVP As KeyValuePair   lItem = 0   For Each oKVP In KeyValuePairs      lItem = lItem + 1      Debug.Print lItem; oKVP.Key; " "; TypeName(oKVP.value);      If InStr(1, TypeName(oKVP.value), "()") > 0 Then         vItem = oKVP.value         Debug.Print "("; CStr(LBound(vItem)); " to "; CStr(UBound(vItem)); ")";         For lIndex = LBound(vItem) To UBound(vItem)            Debug.Print " (" & CStr(lIndex) & ")"; TypeName(vItem(lIndex)); "="; vItem(lIndex);         Next         Debug.Print      Else         Debug.Print "="; oKVP.value      End If   NextEnd Sub'NB VBA Collection object index is 1-based, scripting.dictionary items array is 0-based'cf Scripting.Dictionary Methods s.Add(Key, Item), s.CompareMode, s.Count, s.Exists(Key); _   s.Item(Key) - updateable, s.Items, s.Key(Key), s.Keys, s.Remove(Key), s.RemoveAll'Scripting.Dictionary has no index number; you can index the 0-based variant array of Items returned'  unlike Collections which can be indexed starting at 1'Efficient iteration is For Each varPair in thisdic.KeyValuePairs'Another difference I introduce is that in a scripting.dictionary, the doc says'  If key is not found when changing an item, a new key is created with the specified newitem.'  If key is not found when attempting to return an existing item, a new key is created and its corresponding item is left empty.'but I want to raise an error when addressing a key that does not exist'similarly, the scripting.dictionary will create separate integer and string keys for eg 2


Patirk's implementation doesn't work for MS Office 2016 on Mac. I made use of the implementation by Tim Hall.Here is the link: https://github.com/VBA-tools/VBA-Dictionary

Also import of cls files into Excel doesn't work in MS Office 2016 on Mac as of September 2017. So I had to create a class module and to copy and paste the contents of Dictionary.cls manually in that module while removing meta info from Dictionary.cls such as VERSION 1.0 CLASS, BEGIN, END, Attribute.


I have at last updated the files for Excel 2016 for Mac.http://www.sysmod.com/Dictionary.zip(capital D in Dictionary)

Unzip this and import the class files (tested in Excel 2016 for Mac 16.13 Build 424, 27-Apr-2018)

My bug report to MS is at answers.microsoft.comExcel 16.13 for Mac User Defined Class passed as parameter all properties are Null

Let me know if I've missed anything else!Good luck,

Patrick O'Beirne