Dictionary vs Collection

A pretty common requirement in programming is to be able to create a group of related objects, and put them into some sort of container. This means they can effectively be treated as one object, while still being accessible individually. For example, if I’m tracking the stats of a sports team over the course of a game, I could create a bunch of Player objects, all of which have individual attributes, but which I can view collectively as a Team object.

One way to group the Players would be to put them into a Team array. A weakness of this approach is that the only way to access one of the Players is to use their position in the array. This is ok for arrays where the positions of the members aren’t going to change, but if this isn’t the case it gets harder to keep track of things the longer the array persists in memory and the more changes need to be made to it. What is useful in this case is to be able to retrieve a Player object by using a key that uniquely identifies them, such as their name for instance, and not have to worry about which position in the Team array they are in.

The VB Collection object allows you to do this, by giving you the option of defining a ‘key’ value when the item is added to it – for instance, the below adds the range object identified by the name RangeName to a collection, and uses RangeName as the key:

Dim col As Collection
Dim rng As Excel.Range
Dim sRangeName As String
Set col = New Collection
sRangeName = "RangeName"
Set rng = Range(sRangeName)
col.Add rng, sRangeName

Now, when you wish to retrieve a particular item from the collection, you have the option of either specifying its position (index), or using the key value. If you know this was the first item added and its position hasn’t changed, you can use


(VB collections are 1-based). Otherwise, you can use


You can also iterate over the collection using For Each…Next syntax, which is both more concise and faster than accessing an array one item at a time.

Collections are fine – and pretty much all the container structures in the Excel object model use them, which is why you can say

For Each wb in Workbooks


For Each cell in rng.Cells

However, I hardly ever use them for implementing my own containers – where key-based lookup is required I prefer the VBScript Dictionary. The syntax for adding objects to a dictionary is very similar to adding items to a collection, apart from the reversal of the key and item arguments:

Dim dict As Scripting.Dictionary
Dim rng As Excel.Range
Dim sRangeName As String
Set dict = New Scripting.Dictionary
sRangeName = "RangeName"
Set rng = Range(sRangeName)
dict.Add sRangeName, rng

as is the retrieval of items:


except for the fact that you have to use the key (which is safer in any case).

What really sells the dictionary to me over the collection are the following 3 methods:

dict.Exists(k) – boolean to let you know whether or not the key k has been added to the dictionary. This is very useful, as it avoids the need for the clunky idiom you’re forced to use to check the same thing with collections:

Public Function Exists(ByRef col As Collection, _
                    ByVal sKey As String) As Boolean

    Dim lCheck as Long
    On Error Resume Next
    lCheck = VarType(col.Item(sKey))
    If Err.Number = 0 Then
        Exists = True
        Exists = False
    End If

End Function

dict.Keys – Return an array containing all keys in the dictionary, which can then be iterated over. This for me is where the collection really falls down, as while it’s certainly possible to iterate over the items in a collection, the key cannot be returned from the item – which means it’s not available. A good example of why you might want this is the following from the CRXRowDict.Test function in the last post:

For iKey = LBound(mdicValues.Keys) To UBound(mdicValues.Keys)
        sKey = mdicValues.Keys(iKey)
        Set objRegex = mdicValues(sKey)
        sTest = clsRow.Item(sKey)
        Debug.Print objRegex.Pattern, sTest
        ' if any test fails, return false
        If Not objRegex.Test(sTest) Then
            bResult = False
            Exit For
        End If
    Next iKey

The key is required in two places – once to retrieve the matching regexp object from the dictionary and once to retrieve the test string from the CRowDict instance. The only way I could see to achieve the same result with a collection would be to populate and iterate over a separate array – not pretty.

dict.RemoveAll – As the name suggests, clean out all key-item pairs in the dictionary. No need to loop through either keys or items here, quick and easy.


9 thoughts on “Dictionary vs Collection

  1. AlexJ says:

    Nice article. I’ve used collections for quite a while, but couldn’t get dictionaries to work.

    set coll = nothing
    works pretty well to clear the collection.

    • geoffness says:

      Thanks Alex. That’s true, you can clear the reference to the collection – but that’s not the same as emptying it. In the first case, you then need to set coll = new collection in order to re-use it, but in the second you don’t. Whether or not this is a big deal depends on what you’re doing with it I guess…

  2. Roy MacLean says:

    I’ve long wondered why Dictionary hasn’t been migrated to the standard VBA library – it isn’t at all specific to Scripting (whatever that is).

    Passing thought: is the Keys array in any particular order (e.g. of insertion)? I’m guessing not – will test.


    • geoffness says:

      Very good point Roy, I do find it annoying to have to add the reference to the scripting dll. I don’t know if anyone uses VBScript much these days, but I believe that both Dictionary and RegExp were added to provide similar functionality to that available in other ‘scripting’ languages – Perl, Python, JavaScript etc.

      Personally I think that we may see many more changes to the Excel object model, but I doubt very much that we’ll see any changes to the VBA language itself…

  3. Roy MacLean says:

    Yes, the order of elements in the Keys array, does seem to be that of insertion: so the first key is of the oldest (surviving) item, and the last key is that of the most recent (surviving) item. Could possibly be useful…


  4. Patrice Villeneuve says:

    I was wondering…

    I’m trying to get the Key back… is that possible ??
    WITHOUT iterate all through loop…

    what I mean is this:

    If (gdicCommand.Exists(strTheKey) False) Then
    strTheKey = ???
    End if

    ok, you might go “DUH” here… because I already GOT the key,… but in fact I DON’T…
    The key is CASE sensitive and I used: gdicCommand.CompareMode = TextCompare

    but when I LOG the command in the log file, I want to use the “real” key… and not the one the user entered wich can be in any letter “case”

    • Patrice Villeneuve says:

      ok, WordPress kind of screw my syntax,
      If (gdicCommand.Exists(strTheKey) False) Then

      should be read as:

      If (gdicCommand.Exists(strTheKey) is NOT False) Then

    • geoffness says:

      Hi Patrice

      I get the issue with case… my recommendation is to avoid, where possible, the use of vbTextCompare, simply because it can lead to these kinds of issues. If you can, convert the user entered key to either upper or lower case on the way in, and that way you won’t have to perform any other conversion on the way out to the log.

      Also, a minor point, but you should never need to compare a boolean return value to either true or false in order to return true or false. Particularly when the test amounts to a double negative. To save VBA a very small amount of work, you can rewrite
      If (gdicCommand.Exists(strTheKey) is NOT False)
      If gdicCommand.Exists(strTheKey)

      Let me know if I’ve misunderstood the point of the question, feel free to email me direct if you like. Address at https://excelicious.wordpress.com/about/


Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s