A couple of links to sites with comprehensive information on using the scripting dictionary object, found via Daily Dose of Excel:
I. What is a dictionary ?
A dictionary in VBA is a collectionobject: you can store all kinds of things in it: numbers, texts, dates, arrays, ranges, variables and objects.
Every item in a dictionary gets it’s own unique key.
With that key you can get direct access to the item (reading/writing/adapting).
VBA has several methods to store data: – a dictionary
– a collection
– an array (matrix) variable
– an ActiveX ComboBox
– an ActiveX ListBox
– a Userform control ComboBox
– a Userform control ListBox
– a sortedlist
– an arraylist
Which one to use is dependent of your ultimate goal.
This tutorial doesn’t offer an exhaustive comparison of all these methods.
What a dictionary has to offer will be discussed in detail.
With that knowledge it’s easier to compare different methods and to make a choice between them.
And a link to Experts Exchange, provided by Jeff Weir:
Using the Dictionary Class in VBA
Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful for many programming tasks.
While VBA’s native Collection class offers functionality that is in many respects similar to the Dictionary, the Dictionary class offers many additional benefits. Thus, depending on the exact functionality required for your VBA procedures, the Dictionary class may offer a compelling alternative to the more usual Collection. Indeed, even if the Dictionary’s additional functionality is not relevant to your project, a Dictionary may offer a performance advantage over a Collection.
This article provides:
- An overview of the Dictionary class and its properties and methods;
- A comparison between the Dictionary and the VBA Collection;
- An overview of early binding versus late binding;
- Four illustrative example for using the Dictionary class;
- Common errors and pitfalls (i.e., ‘gotchas) encountered in programming with the Dictionary class; and
- A brief analysis of relative processing speed between the Dictionary and Collection classes
And if that is not enough for you, here are the previous Newton Excel Bach posts on dictionaries.
Hi Doug. I posted a follow up article on this at http://dailydoseofexcel.com/archives/2013/10/23/dictionaries-can-be-rude/ as well.
Anyone interested in using dictionaries should really follow that link, both for the article and the following discussion.