The Scripting Runtime has a dictionary object which allows to index variables by keys or numbers. In other programming languages, this feature is also referred to as hashes or associative arrays.
In order to use the dictionary object (or the scripting runtime for that mattter), the respective reference needs to be added:
thisWorkbook.vbProject.references.addFromGuid _
GUID :="{420B2830-E718-11CF-893D-00A0C9054228}" , _
major := 1 , _
minor := 0
It is also possible to create a dictionary object using createObject():
set dict = createObject("scripting.dictionary")
Methods and properties
add(keyName, val)
Adds a new (named) key with the given value. If keyName already exists, an error is thrown.
exists(keyName)
Check if a key with the name keyName already exists.
items
Returns an array of variants that contains the values (without key names) of the dictionary.
keys
Returns an array of variants that contains the key names of the dictionary.
remove(keyName)
Remove the key whose name is keyName
removeAll
Remove all key/value pairs thus emptying the dictionary.
compareMode
This property sets or gets the way with which key value names are compared and can be set to vbUseCompareOption (use value of option compare), vbBinaryCompare, vbTextCompare, vbDatabaseCompare (MS Access only). A value greater than 2 is interpreted as the
count
This property corresponds to the number of stored key value pairs
item(keyName)
Sets or gets the value of the key whose name is keyName. When getting the value of an inexisting key, the key is created. When setting the value of an existing key, the value is overwritten.
key(keyname) = …
Overwrites the value for an already existing key. If key does not exist, an error is thrown.
Adding and removing keys
An attempt to add an already existing key throws an error with number 457.
An attempt to remove an unexisting key throws an error with number 32811.
option explicit
sub main() ' {
on error goto err_
dim dict as new dictionary
debug.print "adding k1"
dict("k1") = "v1"
debug.print "adding k2"
dict.add "k2", "v2"
debug.print "adding k1"
dict.add "k1", "...."
debug.print "removing k1"
dict.remove "k1"
debug.print "removing inexisting-key"
dict.remove "inexisting-key"
debug.print "removing k2"
dict.remove "k2"
exit sub
err_:
if err.number = 457 then
debug.print "Error: key is already present in dictionary!"
resume next
end if
if err.number = 32811 then
debug.print "Error: trying to remove key that does not exist!"
resume next
end if
debug.print "unhandled error " & err.number & ": " & err.description
end sub ' }
All keys of a given dictionary can be removed with removeAll:
option explicit
sub main() ' {
dim dict as new dictionary
dict("k1") = "v1"
dict("k2") = "v2"
dict("k3") = "v3"
debug.print "elements in dict: " & dict.count
dict.removeAll
debug.print "elements in dict: " & dict.count
end sub ' }
isEmpty(dict, key) allows to check whether a key is present in the dictionary:
option explicit
sub main() ' {
dim D as new dictionary
if isEmpty(D("K")) then
debug.print "is empty"
else
debug.print "is not empty"
end if
D("K") = "val"
if isEmpty(D("K")) then
debug.print "is empty"
else
debug.print "is not empty"
end if
end sub ' }
option explicit
sub main() ' {
dim capital as new scripting.dictionary
capital.add "France" , "Paris"
capital.add "Germany" , "Berlin"
capital.add "Switzerland", "Bern"
capital.add "Italy" , "Rome"
dim country as variant
for each country in capital
debug.print country & ": " & capital(country)
next country
end sub ' }