Arrays vs Collections vs Dictionary Objects (and Dictionary help)


Newton Excel Bach, not (just) an Excel Blog

A recent comment by Lori Miller at Daily Dose of Excel suggested that (for the particular application being discussed) the VBA Collection object was quicker than using scripting dictionaries.  Since I hardly ever use collections (other than the built-in ones), and recently I have made quite extensive use of dictionaries I thought I would investigate further. In the process I found a comprehensive article on the scripting dictionary, which I recommend to Microsoft to see how to write truly helpful help articles:

Using the Dictionary Class in VBA

Commenting on the differences between dictionaries and collections, this article says:

For relatively simple needs, such as identifying only the distinct items in a list, there is no advantage to using a Dictionary from a feature functionality perspective.  However, if you must:

  • Retrieve keys as well as the items associated with those keys;
  • Handle case-sensitive keys; and/or
  • Be able to accommodate changes…

View original post 301 more words