Add a dictionary class to substitute the “Microsoft Scripting Runtime” dictionary

[bernatbosch]

Hi guys, and excuse my English (I write from Barcelona)


I do work with Excel for Mac 2011. Excel for Mac users do not have the reference to the “Microsoft Scripting Runtime” dictionary (scrrun.dll) and I though it would need a dictionary for one of my vb projects. So, I did a little research and found a guy that wrote his own using a Collection and that offered his code to everyone in his blog page.


The problem is that I'm not very familiar with using classes in vba and the author only says the code must be imported to excel ("To use it, import the file into your project").


My question is simple:
what shoud I do exactly with this code? Should I copy it to a text file? Sould I copy it to my workbook vbproject in a separate class module? should I give an specific name to that module?


In brief:
what are ALL the steps to be done in order to make that code available for its use in my vb project?


Besides,
when I try to copy the code to a standard or a class module, the beginning of the code apears as red coloured (as it would be unrecognizable to the module). Must I check if some references are available before copying the code?


I'm lost. Any help will be hardly appreciated.


thank you guys


PS:

The code, as he says, needs two different code files to be imported:

First file:

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "KeyValuePair"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
'Unrestricted class just to hold pairs of values together and permit Dictionary object updating
Public Key As String
Public value As Variant
Second file:

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
Option 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 object

Public KeyValuePairs As Collection ' open access but allows iteration
Public Tag As Variant ' read/write unrestricted

Private Sub Class_Initialize()
Set KeyValuePairs = New Collection
End Sub

Private Sub Class_Terminate()
Set KeyValuePairs = Nothing
End Sub

' 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

Public 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 = Item
End Property

Public 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 = Item
End Property

Public Property Get Item(Key As String) As Variant
AssignVariable Item, KeyValuePairs.Item(Key).value
End Property

' Collection parameter order is Add(Item,Key); Dictionary is Add(Key,Item) so always used named arguments
Public 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:=Key
End Sub

Public Property Get Exists(Key As String) As Boolean
On Error Resume Next
Exists = TypeName(KeyValuePairs.Item(Key)) > "" ' we can have blank key, empty item
End Property

Public Sub Remove(Key As String)
'show error if not there rather than On Error Resume Next
KeyValuePairs.Remove Key
End Sub

Public Sub RemoveAll()
Set KeyValuePairs = Nothing
Set KeyValuePairs = New Collection
End Sub

Public Property Get Count() As Long
Count = KeyValuePairs.Count
End Property

Public Property Get Items() As Variant ' for compatibility with Scripting.Dictionary
Dim vlist As Variant, i As Long
If 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 = vlist
End If
End Property

Public Property Get Keys() As String()
Dim vlist() As String, i As Long
If 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 = vlist
End If
End Property

Public Property Get KeyValuePair(Index As Long) As Variant ' returns KeyValuePair object
Set KeyValuePair = KeyValuePairs.Item(1 + Index) ' collections are 1-based
End Property

Private Sub AssignVariable(variable As Variant, value As Variant)
If IsObject(value) Then
Set variable = value
Else
Let variable = value
End If
End Sub

Public 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
Next
End 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

 

[CalcSux78]

If you're new to Collections, start here: The Ultimate Guide To Collections in Excel VBA - Excel Macro Mastery Excel Macro Mastery

 

[bernatbosch]

Great! I'll take a good look at it. Thanks very much, indeed.

 

[bernatbosch]

It looks like one of the most important things to take into consideration is whether or not you will need to manipulate values within the group after creating it. For what I can read in the post you just passed me: "You can add or remove an item but you cannot change the value of the item. If you are going to be changing the values in a group of items then you will need to use an array."


Also, in case of using keys to store your values, the fact that to access a value stored in a Collection can be done without having to loop through all the rest of the values in the Collection, as it would be the case of an array, makes it very interesting.


Indeed, for what I could understand, a Dictionary class may help to check if a key does exist or not, but for all the rest it's quite close to a Collection, isn't it?

 

[CalcSux78]

In my experience, I've found the required libraries for using dictionaries are not installed on the work computers I develop for. When applicable, libraries have done the trick.

HTH

 

[RoryA]

Copy the codes to two text files and save them with a .cls extension, then import those two files into your project. You should see two new class modules, one called Dictionary and one called KeyValuePair.

 

[bernatbosch]

Thank you!