Okay, I'll admit I'm a bit of a fan of the Array. You either love or hate an Array. People who dislike the Array will often opt for a Collection instead. Other languages do provide a really cool object called a Dictionary or Hash Table. This is like a Collection that behaves like a Collection combined with an Array with some extra handy methods. VBA does not have this but VBScript does provide a Dictionary object, which is cool, and we can make use of this object within our VBA environment. To build a dictionary object do the following: Dim my_dictionary as Object
Set my_dictionary = CreateObject("Scripting.Dictionary") Voila! We have a dictionary. What can we do with it? We can add items, check for the existence of items, return an array of keys, return an array of items, set how a dictionary compares keys and get the count and so on. An example: 'First create the Dictionary Object
Dim my_dictionary as Object
Set my_dictionary = CreateObject("Scripting.Dictionary") 'When adding an object or value to a dictionary you put the key first and the actual value or object second. The key is mandatory and you cannot add items without it.
my_dictionary.Add "Key 1", "Value 1"
my_dictionary.Add "Key 2", "Value 2"
my_dictionary.Add "Key 3", "Value 3"
my_dictionary.Add "Key 4", "Value 4" So now we've added four values to the dictionary. Let's do some things we cannot do cleanly or at all with a Collection. Say we want to replace "Value 3" with the name "Zebra". Too easy! my_dictionary.Item("Key 3") = "Zebra" You couldn't do that with a collection! In a collection you would have to remove one item and add another, thus losing the order or your items. A dictionary behaves like an Array in this respect. What if we were not sure there was a key called "Key 3" within the dictionary and wanted to avoid an error. Again, easy, we just use the Exists method of the dictionary object: if my_dictionary.Exists("Key 3") then
my_dictionary.Item("Key 3") = "Zebra"
else
my_dictionary.Add "Key 3", "Zebra"
end if We might want to know how many items are in the dictionary, just use the Count method which is the same as the one in a collection. MsgBox my_dictionary.Count If you want to iterate through the items in a dictionary, you can't use an integer counter as you would an Array or Collection but you can use two methods to do so: 'You can just grab the items from the dictionary like so:
Dim items as Variant
items = my_dictionary.Items 'Iterate through the array of items. These items can include objects aswell.
Dim separate_item as Variant
For Each separate_item in items
MsgBox separate_item
Next separate_item 'Or you can extract the keys and iterate through the items (which is another advantage over a Collection that does not give you it's keys or let you know what they are)
Dim keys as Variant
keys = my_dictionary.Keys 'Iterate through the array of items. These items can include objects aswell.
Dim key as Variant
For Each key in keys
MsgBox my_dictionary.Item(key)
Next key Too easy! To remove an item or all items you can use Remove and RemoveAll respectively: my_dictionary.Remove("Key 2") Or my_dictionary.RemoveAll These are the basics. I'll look at the CompareMode method in un minuto. The Dictionary object is a real advantage when we need to build a Collection of Collections or a Class Collection. For example; say we had to collect data on spys and their current missions. Usually we would have to create a Class Object called Spy and hold a Private or Public Collection within the class to which we would add their missions. One class too many (A Collection is a Class)! Let's use a Dictionary instead... Dim my_dictionary As Object Dim missions As Collection Dim spy_name As String Dim keys, key As Variant Set my_dictionary = CreateObject("Scripting.Dictionary") 'Add three lots of spies.
Set missions = New Collection
spy_name = "Alexander Poligraphovich"
missions.Add "Vladivostok"
missions.Add "Ukraine"
missions.Add "Beijing"
my_dictionary.Add spy_name, missions spy_name = "Mohammed Ramadan"
Set missions = New Collection
missions.Add "Munich"
missions.Add "Tehran"
missions.Add "Sydney"
my_dictionary.Add spy_name, missions spy_name = "Sri FitzPatrick"
Set missions = New Collection
missions.Add "Dublin"
missions.Add "San Francisco"
my_dictionary.Add spy_name, missions keys = my_dictionary.Keys
For Each key In Keys
MsgBox key & vbCrLf & _
my_dictionary(key).item(1) & vbCrLf & _
my_dictionary(key).item(2) & vbCrLf & _
my_dictionary(key).item(3)
Next key The CompareMode method lets you set how the dictionary compares it's keys when looking for duplicates etc. There are four compare modes vbBinaryCompare, vbTextCompare, vbDatabaseCompare (for MS Access only) and vbUseCompareOption (which uses the setting in the Option Compare statement at the top of a module). How can we use this? Say we add two values with the Keys of monkey and MONKEY' one in all lowercase and the other in all uppercase. my_dictionary.Add "monkey", "Giraffe"
my_dictionary.Add "MONKEY", "Elephant"
MsgBox my_dictionary.Count The MsgBox will show an item count of 2, because the two keys are essentially different. The dictionary is performing a binary comparison upon the keys so you can add more than one 'monkey' as long as they have some difference in character case. What if we wanted the word monkey in all of it's forms to be compared by name and not content? In other words we don't want more than one 'monkey' in the dictionary. We use CompareMode vbTextCompare: my_dictionary.CompareMode = vbTextCompare
my_dictionary.Add "monkey", "Giraffe"
my_dictionary.Add "MONKEY", "Elephant"
MsgBox my_dictionary.Count On this example we don't even get to the Msgbox, instead we get an error stating "This Key is already associated with an element of this collection.". This stops two keys being added that have the same name. vbBinaryCompare behaves the same way as the first example does (it is the default) and vbDatabaseCompare....Well I read what it did once and never had to remember it again! You can find explanations for these, albeit very succinct, within the MS Help in Access, or better still Google it. Hopefully this gives you an extra tool alongside the Collection or Array and some ideas on future use. A Dictionary makes code structure cleaner and more humanly understandable. This VBScript tool will really pay dividends. |
