Office 2011 for Mac Dictionary replacement

trunten · Oct 25th 2011

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

Display More

**Reafidy** · Oct 25th 2011