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 O(n) time?

   To my knowledge, it is prohibitively slow to access large Collections by index, because they are essentially linked lists: so we degenerate into O(n2) 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

• PeterT: Though I haven't reviewed your code, I believe the exercise and path you're taking to build your own Dictionary class is certainly worthy. You may want to look at Cristian Buse's Dictionary implementation for an example of how some of your questions were addressed.
• Greg: @PeterT I am not developing a class for a Dictionary. Rather, I am developing a (standalone) class which has (something like) a Dictionary as one of its properties. I have edited my question to clarify.
• Greedo: When you say no dependencies you mean your entire codebase must be a single file? For context why is that? And why could you not copy the code from an existing implementation into your module, if the license is very permissive?
• Cristian Buse: @Greg I see nothing wrong in simply using a Dictionary. Your class will still be standalone and you can just ask your users to use either the Scripting.Dictinary, Tim Hall's dict, my dict, or any other of the available options that you deem fit. It will save you a lot of headache. Who knows, maybe Microsoft will roll out a native dict like they recently did with RegExp.
• Greg: @Greedo My project has two beneficiaries in mind: (1) a developer ("dev") who builds UDFs in VBA; and (2) their lay user ("user") who enjoys these UDFs in Excel, and need not know of VBA. My project helps the dev greatly and painlessly enhance the experience for their user. The dev pastes a snippet of my code into their module. If my class is already installed, then everything works as intended; but if my class is absent, then the user sees a pop-up in Excel, which prompts them to install my class. To keep installation simple for lay users, my class should be independent.