Collection Utilities for VBA

Question

Background

I am building a complex, standalone class. It has a property called .Mapping, which should behave like a Dictionary.

This must work on Mac as well as Windows, so I cannot employ the native CreateObject("Scripting.Dictionary"). Yet it must work without dependencies, so I cannot use (say) the excellent Dictionary class by @CristianBuse.

So I am designing a UDT called "Dix", within my class module, to emulate a Dictionary.

Private Type Dix
    Count As Long
    Keys As Collection
    Items As Collection
    ' CompareMode As VBA.VbCompareMethod
End Type

This requires safe and consistent operations on Collections like .Keys and .Items. So I have written custom Clx_*() utilities, which should be useful here and universally.

Questions

Here are some questions that come to mind, but feel free to pose (and address) your own!

1. Currently the "retrieval" functions return a Variant which IsEmpty() when the item doesn't exist: Clx_Get() and Clx_Set() and Clx_Cut(). They record its existence (True) or nonexistence (False) in a Boolean argument of the form has*, which is passed and modified by reference.

   Should we instead omit all has* arguments, and simply return an Error value via CVErr()? This declutters usage and is sometimes recommended for functions that may otherwise crash, but it comes with disadvantages:

   • Such values are now ambiguous. Did Clx_*() actually encounter an error in execution, or did the Collection simply contain this Error value as one of its items?
   • Can CVErr() even process error codes, beyond the xlErr enumeration for cell errors in Excel? I have found conflicting information on this.
   • Are we really interested in the actual error codes? The Clx_*() functions "safely" trap relevant errors while propagating all others: so Clx_Get() traps only a nonexistent key (error 5) or position (error 9).
   • It adds an extra step to use IsError() on the output, rather than having this information already recorded within has*.

2. For consistency with other "safe" functions, should Clx_Array() and Clx_Clone() trap errors when extracting items? In other words, should they populate the targets (arr and clone) with only the subset of keys (and their items) found in clx?

   Currently they use .Item() for efficiency, which throws an error when any keys are absent from clx. But they could use Clx_Get() instead, and skip population when has* shows a False. Among other things, this would avoid leaving the arr "half-baked", with values overwritten up to the first error.

   However, would this impede performance at scale? It introduces an extra If-statement to each iteration, along with a more complex call to Clx_Get().

3. How much effort should Clx_Array() devote to resizing arr?

   When the user omits the arr argument, then Clx_Array() simply returns a Variant() array that it creates from scratch. But when the user supplies arr, then Clx_Array() copies the values into arr, so the user can enforce stricter types like a String() array. The user may also supply count to save computation time, rather than running clx.Count which is really an iterative function.

   Note: If we omit missing keys in Clx_Array() and Clx_Get(), we must resize the result once more, and shorten arr to address missing items overlooked by count.

4. Does the For Each properly maximize efficiency for iterating over Collections in 𝒪(𝑛) time?

   To my knowledge, it is prohibitively slow to access large Collections by index, because they are essentially linked lists: so we degenerate into 𝒪(𝑛²) time by looping numerically on clx.Item(i) from i = 1 To n.

5. The Clx_*() functions are intended as "safe" wrappers for comparable clx.*() methods. While the use of (say) Clx_Cut() is nifty within Clx_Set(), it also slows performance and deepens the call stack by delegating to another Clx_*() function, rather than simply using the corresponding clx.*() method(s).

   Which approach better balances performance with (conceptual) consistency?

Code

' ###############
' ## Utilities ##
' ###############

' Assign any value (objective or scalar) to a variable.
Private Sub Assign(ByRef var As Variant, ByVal val As Variant)
    If VBA.IsObject(val) Then
        Set var = val
    Else If
        Let var = val
    End If
End Sub


' Throw the latest error object.
Private Sub Err_Raise()
    VBA.Err.Raise number := VBA.Err.Number, _
        source := VBA.Err.Source, _
        description := VBA.Err.Description, _
        helpFile := VBA.Err.HelpFile, _
        helpContext := VBA.Err.HelpContext
End Sub



' ########################
' ## Utilities | Arrays ##
' ########################

' Get the length (along a dimension) of an array.
Private Function Arr_Length(ByRef arr As Variant, _
    Optional ByVal dimension As Long = 1 _
) As Long
    Const EMPTY_ERR_NUMBER As Long = 9  ' Subscript out of range.
    
    On Error GoTo BOUND_ERROR
    Arr_Length = UBound(arr, dimension) - LBound(arr, dimension) + 1
    Exit Function
    
BOUND_ERROR:
    Select Case VBA.Err.Number
        Case EMPTY_ERR_NUMBER
            Arr_Length = 0
        Case Else
            Err_Raise
    End Select
End Function



' #############################
' ## Utilities | Collections ##
' #############################

' Get an item (safely) from a Collection.
Private Function Clx_Get(ByRef clx As Collection, _
    ByVal index As Variant, _
    Optional ByRef has As Boolean _
) As Variant
    Const POS_ERR_NUMBER As Long = 9  ' Subscript out of range.
    Const KEY_ERR_NUMBER As Long = 5  ' Invalid procedure call or argument.
    
    On Error GoTo ITEM_ERROR
    Assign Clx_Get, clx.Item(index)
    
    has = True
    Exit Function
    
ITEM_ERROR:
    Select Case VBA.Err.Number
        Case POS_ERR_NUMBER, KEY_ERR_NUMBER
            has = False
        Case Else
            Err_Raise
    End Select
End Function


' Test if an item exists.
Private Function Clx_Has(ByRef clx As Collection, _
    ByRef index As Variant _
) As Boolean
    Clx_Get clx, index := index, has := Clx_Has
End Function


' Remove (and record) an item.
Private Function Clx_Cut(ByRef clx As Collection, _
    ByRef index As Variant, _
    Optional ByRef has As Boolean _
) As Variant
    ' Record any item...
    Assign Clx_Cut, Clx_Get(clx, index := index, has := has)
    
    ' ...and remove it.
    If has Then clx.Remove index
End Function


' Set (and record) an item.
Private Function Clx_Set(ByRef clx As Collection, _
    ByRef key As String, _
    ByRef item As Variant, _
    Optional ByRef has As Boolean _
) As Variant
    ' Remove (and record) any item...
    Assign Clx_Set, Clx_Cut(clx, index := key, has := has)
    
    ' ...and add the new value under its key.
    clx.Add item, key := key
End Function


' Rekey an item.
Private Sub Clx_Key(ByRef clx As Collection, _
    ByRef key As String, _
    ByRef newKey As String, _
    Optional ByRef has As Boolean, _
    Optional ByRef hasNew As Boolean _
)
    ' Record existence and value of old key.
    Dim item As Variant
    Assign item, Clx_Get(clx, index := key, has := has)
    
    ' Short-circuit for unaltered key.
    If key = newKey Then
        hasNew = has
        Exit Sub
    End If
    
    ' Record existence of new key.
    Dim hasNew As Boolean
    hasNew = Clx_Has(clx, index := newKey)
    
    ' Rename existing key as unused key.
    If has And Not hasNew Then
        clx.Add item, key := newKey, after := key
        clx.Remove key
    End If
End Sub


' Copy a Collection into an array: by position or by key.
Private Function Clx_Array(ByRef clx As Collection, _
    Optional ByRef keys As Collection, _
    Optional ByRef arr As Variant, _
    Optional ByVal base As Long = 0, _
    Optional ByVal count As Long = -1 _
) As Variant
    ' Create the array if it is not supplied.
    If VBA.IsMissing(arr) Then
        Dim a() As Variant
        arr = a()
    End If
    
    ' Count the items by default.
    If count < 0 Then
        If keys Is Nothing Then
            count = clx.Count
        Else
            count = keys.Count
        End If
    End If
    
    ' Empty the array in the absence of items...
    If count = 0 Then
        Erase arr
        
    ' ...and otherwise copy the items into the array.
    Else
        ' Measure the array.
        Dim lng As Long: lng = Arr_Len(arr, dimension := 1)
        Dim resize As Boolean
        
        Dim low2 As Long: low2 = base
        Dim up2 As Long: up2 = low + count - 1
        
        ' Determine if resizing is needed...
        If lng = 0 Then
            resize = True
        Else
            Dim low1 As Long: low1 = LBound(arr, 1)
            Dim up1 As Long: up1 = UBound(arr, 1)
            
            resize = low1 <> low2 Or up1 <> up2
        End If
        
        ' ...and resize accordingly.
        If resize Then ReDim arr(low To up)
        
        ' Copy values into array: by position...
        Dim i As Long: i = low
        If keys Is Nothing Then
            Dim item As Variant
            For Each item In clx
                Assign arr(i), item
                i = i + 1
            Next item
            
        ' ...or by key.
        Else
            Dim key As String
            For Each key in keys
                Assign arr(i), clx.Item(key)
                i = i + 1
            Next key
        End If
    End If
    
    ' Return the result.
    Clx_Array = arr
End Function


' Clone a Collection: its items and (optionally) its keys.
Private Function Clx_Clone(ByRef clx As Collection, _
    Optional ByRef keys As Collection _
) As Collection
    Dim clone As Collection
    
    ' Short circuit if original is uninitialized.
    If clx Is Nothing Then Exit Function
    
    ' Copy any items into the clone: without keys...
    Set clone = New Collection
    If keys Is Nothing Then
        Dim item As Variant
        For Each item In clx
            clone.Add item
        Next item
        
    ' ...or with their respective keys.
    Else
        Dim key As String
        For Each key In keys
            clone.Add clx.Item(key), key := key
        Next key
    End If
    
    Set Clx_Clone = clone
End Function