You no doubt recall when snb wrote about Scripting.Dictionaires. Well, there’s more.
I use Collection objects in my custom class modules almost exclusively. It’s the only object, that I know of, that I can enumerate using For Each.
Outside of custom class modules, I use Dictionary objects. I used to avoid them because they weren’t built in to the language. I was always afraid of some dependency problem. But I’ve never seen one in all my years, so I’m over that now. The advantage of the Exists property and the ability to produce an array of keys or items is awesome. it’s probably more awesome than For Each, but I just haven’t made that leap yet.
And I never use ArrayLists because I never remember them. That’s not totally true. When I’m writing a procedure with a Dictionary and I need to sort, I kick myself for not using an ArrayList.
Here’s some features of Collections, Dictionaries, and ArrayLists.
| Feature | Collection | Dictionary | ArrayList |
|---|---|---|---|
| New Enum in class | Yes | No | No |
| Exists | No | .Exists | .Contains |
| Key Value paradigm | Yes | Yes | No |
| Unique keys | Yes | Yes | NA |
| Key data types | String | Any | NA |
| Get keys | No | Yes | NA |
| Auto create items | No | Yes | No |
| Insert anywhere | .Add(,,before,after) | No | .Insert |
| Output to array | No | .Keys or .Items | .ToArray |
There are other differences. Those are just the ones that are important to me. If there’s a difference that’s important to you, leave a comment. You can read everything you ever wanted to know about these objects at one of the pages below:
Collections: http://www.snb-vba.eu/VBA_Collection_en.html
Dictionaries: http://www.snb-vba.eu/VBA_Dictionary_en.html
ArrayLists: http://www.snb-vba.eu/VBA_Arraylist_en.html