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
col.Item(1)
(VB collections are 1-based). Otherwise, you can use
col.Item(sRangeName)
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
or
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:
dict.Item(sRangeName)
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 Else 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.