All about dictionaries

A couple of links to sites with comprehensive information on using the scripting dictionary object, found via Daily Dose of Excel:

VBA for smarties

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.

This entry was posted in Excel, VBA and tagged , , . Bookmark the permalink.

2 Responses to All about dictionaries

    • dougaj4 says:

      Thanks Jeff.

      Anyone interested in using dictionaries should really follow that link, both for the article and the following discussion.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s