Document Sample
Create Powered By Docstoc
					?Did you find this article useful? For more useful tips and hints, points to ponder and
keep in mind, techniques, and insights pertaining to Internet Business, do please
browse for more information at our websites.

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"
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

my_dictionary.Remove("Key 2")


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

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 & _
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

Shared By: