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 𝑛 be the .Count of the Collection, and suppose we have the "worst case" scenario, where the keys are a sequence 𝕂 of 𝑛 numeric Strings: 𝕂 = {"1", "2", "3", ..., CStr(n)}. Even so, there must exist some 𝑖 ∈ 𝕀 in the sequence 𝕀 = {1, 2, 3, ..., 𝑛, 𝑛 + 1} of 𝑛 + 1 numbers, such that CStr(i) ∉ 𝕂.

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'

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