Dictionary and objects

[agibsonsw]

Hello.

Can the Microsoft Scripting Runtime, Dictionary object store and manage objects? That is, can I store Object, Key value pairs?

Thanks, Andy.

[HansV]

Yes, that works. Here is an extremely simple example (in Excel):

Code: Select all

Dim rng1 As Range
Dim rng2 As Range
Dim dic As New Scripting.Dictionary

' Set object variable
Set rng1 = Range("A1")
' Add object to dictionary
dic.Add "MyCell", rng1

' Retrieve object from dictionary
Set rng2 = dic("MyCell")
' Do something with it
Debug.Print rng2.Address, rng2.Value

[agibsonsw]

Thank you. Collection and Dictionary objects seem quite similar. Is the main difference the speed with which a Dictionary item can be retrieved with the key?
Andy.

[HansV]

I haven't tested whether dictionaries or collections are faster.

A dictionary has more features than a collection. For example, it has an Exists method that can be used to find out whether a specific key exists; this is more efficient than the looping that is required for collections. If you need this extra functionality, you should use a dictionary. If not, you're better off with a collection, since it's built into VBA and doesn't require setting a reference (and in some environments, Scripting is disabled because it might pose a security risk).

[agibsonsw]

Thanks again.

[rory]

In addition to what Hans said, Dictionaries are supposed to be more lightweight and therefore faster in code. You can't insert items at a specific position though, so if you need that you should use a Collection.

[agibsonsw]

That's helpful. Thanks, Andy.