HI
I've Created a class to mimic the dictionary object for those of us who have windows in work and a mac at home!
(You'll need to download the class file if you want the default property as item. Simply rename to remove the .txt and import into your vba project)
Please read the notes:
Thanks
Code
- '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
- ''=============================== Mac Dictionary Object =================================''
- '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
- ''Created thanks to the fact that Microsoft decided not to include the dictionary ''
- ''object in Office 2011 for Mac. ''
- '' ''
- ''This class is the best approximation of the dictionary object I could manage given ''
- ''the limitations of VBA and of course the limitations of my knowledge of VBA! ''
- '' ''
- ''The class supports all the standard methods of the dictionary object (Add, Remove, ''
- ''RemoveAll, Item, Items, Keys, and Count) and also include an isEmpty method to ''
- ''quickly find out if the collection is empty or not - To make your project compatible ''
- ''with Windows versions of Excel do not use the isEmpty method. ''
- '' ''
- ''When creating projects on your mac simply replace instances of the dictionary object ''
- ''with this class and everything should work fine, just make sure you change your ''
- ''variable declerations and initialisations back to windows compatible versions before ''
- ''you send to people working in Excel for Windows. ''
- '' ''
- ''There are some notable problems with this class and you can really screw it up if you ''
- ''wanted to, but as this is intended to help you bridge the gap between home and work I ''
- ''don't see why you would seek these out. Anyway the problems are as follows: ''
- '' ''
- ''1) The keys function returns the collection object dictionaryKeys which means that if ''
- ''you wanted to you could remove an item from this collection and it would not remove the''
- ''corresponding item from the dictionaryValues collection nor decrement the count leaving''
- ''a mismatch of keys and values ''
- '' ''
- ''2) The exact same problem is true for the items function only in reverse ''
- '' ''
- ''3) To iterate you must use .keys or .items but the standard dictionary object supports ''
- ''that too so it's not too much to add .keys every now and again. ''
- '' ''
- ''Like I said, you would really have to purposely mess this up as the dictionary object ''
- ''wouldn't let you do that anyway, and the purpose is to still work on projects that use ''
- ''dictionary objects on your Mac. If you know how to fix this though, please go ahead. ''
- '' ''
- '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
- '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
- Option Explicit
- Private itemCount As Long
- Private dictionaryKeys As New Collection
- Private dictionaryValues As New Collection
- Public Function add(ByVal Key As String, Value As Variant) As Variant
- itemCount = itemCount + 1
- If exists(Key) Then
- add = dictionaryValues.Item(Key)
- dictionaryValues.remove Key
- dictionaryValues.add Value, Key
- Else
- dictionaryKeys.add Key, Key
- dictionaryValues.add Value, Key
- add = Null
- End If
- End Function
- Public Function exists(ByVal Key As String) As Boolean
- Dim aKey
- Dim found As Boolean
- found = False
- For Each aKey In dictionaryKeys
- If aKey = Key Then
- found = True
- Exit For
- End If
- Next aKey
- exists = found
- End Function
- Public Function remove(ByVal Key As String) As Boolean
- If exists(Key) Then
- dictionaryKeys.remove Key
- dictionaryValues.remove Key
- itemCount = itemCount - 1
- remove = True
- Else
- remove = False
- End If
- End Function
- Public Sub removeAll()
- itemCount = 0
- Set dictionaryKeys = New Collection
- Set dictionaryValues = New Collection
- End Sub
- Public Property Get Count() As Long
- Count = itemCount
- End Property
- Public Property Get isEmpty() As Boolean
- If itemCount = 0 Then
- isEmpty = True
- Else
- isEmpty = False
- End If
- End Property
- Public Function keys() As Collection
- Set keys = dictionaryKeys
- End Function
- Public Function items() As Collection
- Set items = dictionaryValues
- End Function
- 'This is the default property
- Public Property Get Item(ByVal Key As String) As Variant
- If exists(Key) Then
- If TypeOf dictionaryValues.Item(Key) Is Object Then
- Set Item = dictionaryValues.Item(Key)
- Else
- Item = dictionaryValues.Item(Key)
- End If
- Else
- Item = False
- End If
- End Property
- Public Property Let Item(ByVal Key As String, Value As Variant)
- Me.add Key, Value
- End Property