Update Member "In Place" within VBA Collection

Question

Background

I encountered this issue a while back, where I tried to "set" the value of a member "in place", without changing its position within the Collection. Unfortunately, there is no way to determine the position of a key in a Collection, so the best I could do was

1. .Remove() the old item, wherever it was; and
2. .Add() its replacement, onto the end.

Now a custom class could have solved this, like KeyedCollection or VBA-FastDictionary by @Cristian Buse. But while my use case required extensibility to Mac, it also precluded further dependencies.

Solution

But then I had a brainwave! The .Add() method has the before and after arguments, which can insert a member next to an existing key.

So if the key already exists, we can

1. mark the spot before it with a placeholder; and
2. .Remove() its member; then
3. .Add() its replacement after the placeholder, under the original key; and finally
4. .Remove() the placeholder.

This leaves the replacement value under the same key and in the same position!

There remained the challenge of generating a unique placeholder, which would not clash with existing keys. Now let n be the .Count of the Collection, and suppose we have the "worst case" scenario, where the keys are a sequence K of n numeric Strings: K = {"1", "2", "3", ..., CStr(n)}. Even so, there must exist some i ∈ I in the sequence I = {1, 2, 3, ..., n, n + 1} of n + 1 numbers, such that CStr(i) ∉ K.

I use this principle to generate the unique placeholder in linear time, with the Private helper Clx_NewKey().

Code

Clx.bas

The API exposes the following functions:

• Clx_Set(): Set the value of a member "in place", either by key or anonymously by position.
• Clx_Has(): Test if a member exists, either under a key or at a position.

Attribute VB_Name = "Clx"
' Set the value of a member "in place".
Public Sub Clx_Set(ByRef clx As Collection, _
 ByVal index As Variant, _
 ByRef value As Variant _
)
 If WorksheetFunction.IsNumber(index) Then
 clx.Add item := value, after := index
 clx.Remove index := index
 Else
 Dim placeholder As String: placeholder = Clx_NewKey(clx)
 
 clx.Add item := Null, key := placeholder, before := index
 clx.Remove index := index
 
 clx.Add item := value, key := index, after := placeholder
 clx.Remove index := placeholder
 End If
End Sub
' Test if a member exists.
Public Function Clx_Has(ByRef clx As Collection, _
 ByVal index As Variant _
) As Boolean
 On Error GoTo Fail
 clx.Item index := index
 
 Clx_Has = True
 Exit Function
 
Fail:
 Clx_Has = False
End Function
' Generate a unique key.
Private Function Clx_NewKey(ByRef clx As Collection, _
 Optional ByVal seed As Long = -2147483648, _
 Optional ByVal increment As Long = 1 _
) As String
 Dim i As Long: i = seed
 Dim key As String
 
 Do
 key = CStr(i)
 i = i + increment 
 Loop While Clx_Has(clx, index := key)
 
 Clx_NewKey = key
End Function

Test_Clx.bas

See below for the output from testing.

Attribute VB_Name = "Test_Clx"
Public Sub Test()
 Dim colx As Collection: Set colx = New Collection
 
 
 ' ########################
 ' ## Initial Collection ##
 ' ########################
 
 Debug.Print "===== Initial Collection ====="
 Debug.Print
 
 colx.Add "val_1", "key_1"
 colx.Add "val_2", "key_2"
 colx.Add "val_3", "key_3"
 
 
 Debug.Print "colx(1) = '" & colx(1) & "'"
 Debug.Print "colx('key_1') = " & colx("key_1") & "'"
 Debug.Print
 
 Debug.Print "colx(2) = '" & colx(2) & "'"
 Debug.Print "colx('key_2') = '" & colx("key_2") & "'"
 Debug.Print
 
 Debug.Print "colx(3) = '" & colx(3) & "'"
 Debug.Print "colx('key_3') = '" & colx("key_3") & "'"
 Debug.Print: Debug.Print: Debug.Print
 
 
 
 ' ################
 ' ## Set by Key ##
 ' ################
 
 Debug.Print "===== Set by Key ====="
 Debug.Print
 
 Debug.Print "Clx_Has(colx, 'key_1') = " & Clx.Clx_Has(colx, "key_1")
 Debug.Print "Clx_Set colx, 'key_1', 'val_1.2'"
 Clx.Clx_Set colx, "key_1", "val_1.2"
 Debug.Print
 
 Debug.Print "colx(1) = '" & colx(1) & "'"
 Debug.Print "colx('key_1') = " & colx("key_1") & "'"
 Debug.Print
 
 Debug.Print "colx(2) = '" & colx(2) & "'"
 Debug.Print "colx('key_2') = '" & colx("key_2") & "'"
 Debug.Print
 
 Debug.Print "colx(3) = '" & colx(3) & "'"
 Debug.Print "colx('key_3') = '" & colx("key_3") & "'"
 Debug.Print: Debug.Print: Debug.Print
 
 
 
 ' #####################
 ' ## Set by Position ##
 ' #####################
 
 Debug.Print "===== Set by Position ====="
 Debug.Print
 
 Debug.Print "Clx_Has(colx, 1) = " & Clx.Clx_Has(colx, 1)
 Debug.Print "Clx_Set colx, 1, 'val_1.3'"
 Clx.Clx_Set colx, 1, "val_1.3"
 Debug.Print
 
 Debug.Print "colx(1) = '" & colx(1) & "'"
 ' Debug.Print "colx('key_1') = " & colx("key_1") & "'"
 Debug.Print
 
 Debug.Print "colx(2) = '" & colx(2) & "'"
 Debug.Print "colx('key_2') = '" & colx("key_2") & "'"
 Debug.Print
 
 Debug.Print "colx(3) = '" & colx(3) & "'"
 Debug.Print "colx('key_3') = '" & colx("key_3") & "'"
 Debug.Print: Debug.Print: Debug.Print
End Sub

---

Output

===== Initial Collection =====
colx(1) = 'val_1'
colx('key_1') = val_1'
colx(2) = 'val_2'
colx('key_2') = 'val_2'
colx(3) = 'val_3'
colx('key_3') = 'val_3'
===== Set by Key =====
Clx_Has(colx, 'key_1') = True
Clx_Set colx, 'key_1', 'val_1.2'
colx(1) = 'val_1.2'
colx('key_1') = val_1.2'
colx(2) = 'val_2'
colx('key_2') = 'val_2'
colx(3) = 'val_3'
colx('key_3') = 'val_3'
===== Set by Position =====
Clx_Has(colx, 1) = True
Clx_Set colx, 1, 'val_1.3'
colx(1) = 'val_1.3'
colx(2) = 'val_2'
colx('key_2') = 'val_2'
colx(3) = 'val_3'
colx('key_3') = 'val_3'

• Cristian Buse: I did something similar :) but using Rnd - see here. However, I don't use that class anymore as I now have FastDictionary
• Greg: Hey @CristianBuse, long time no see! :) That's awesome that we now have a dictionary compatible with Mac! This use case arose on a project dear to my heart, which I will be releasing (version 0.1.0) in a few weeks. After all the advice you've given me in VBA, I can't wait to show you!

Answer 1

Some thoughts in no special order

1. What you've done here might be called extension methods in another language, extending the functionality of the collection class. I would drop the Clx_ prefix and rename the module CollectionExtensions. The callsite would look like If Has(bookCollection, "Great Gatsby") Then ... which is clear enough but you could also qualify with the module name for clarity or intellisense CollectionExtensions.Has
2. That said, removing the Clx_ prefix leaves us with Set, Has, NewKey -one of which is a keyword and the others are quite vague. Maybe consider HasKey, KeyInCollection, since Has could reasonably refer to the value not the key.
3. Has is an implementation detail of NewKey, I would probably inline it, or at the very least make it private, since if you modify the implementation of NewKey so that it no longer requires it, then you'll have to change your public API or maintain a function in your library you may not need.
4. The performance of NewKey may deteriorate if the caller is setting keys in the same way you are with incrementing values. 4 options: 1) ideally the user can specify a function for generating new keys themselves, but VBA functions can't be passed around so this may be impractical 2) allow the user to specify the seed value so they can avoid most clashes 3) use a GUID which statistically won't have any collisions 4) require a convention -eg reserved value for the temp key. You would have to profile in your usecases.
5. Clx_Set could be renamed and implemented as a property Set/Let

Property Set/Let ItemAtIndex(ByRef clx As Collection, ByVal index As Variant, ByRef value As Variant)

Called like

Set ItemAtIndex(clx, "foo") = obj
ItemAtIndex(clx, "foo") = value 

6. Tests are good, you should test negative position, empty key, null key, key already in collection matching the first candidate for NewKey to ensure that function can handle collisions correctly. I would also use Rubberduck to Assert these things since then you can drop all the debug.print as Rubberduck gives you a graphical representation of the test results, and handles the errors automatically.

Last note, if performance is critical there is probably an O(1) or at least a faster way to do this by accessing the in-memory collection directly, but at that level of effort I would probably just adapt the code to use a Dictionary which allows in place update

• Greg: Thank you for reviewing! Apologies for the delayed reply: I've got a lot going on, and haven't had time to give my full attention to your review. That said, I'd like to address one positive and one negative. I love your suggestion for the Property Get and Set/Let! Does Property Let work for both objects and scalars, or only for objects? But as for the Dictionary, it is unavailable on Mac, so I'm afraid it's out of the question: "But while my use case required extensibility to Mac, it also precluded further dependencies."