Simulating Objects with Collections in VBA

Question

Background

I am writing a VBA module which requires the functionality of a custom object. This object will support "regular" functions (UDFs) within the module.

Due to reasons beyond the scope of this question, standard solutions are impossible: I cannot...

1. ...define a custom Type in my module, which has the required properties. My module is an "optional dependency", so dependents check for its presence during execution; but Type operations fail to even compile in its absence.
2. ...convert my module into a class for late binding, which should solve the compilation issue. Its "regular" functions must be usable in Excel formulae; but this is impossible (I think) with methods from a class.
3. ...use a native (Dictionary) object, which has the required functionality. My module must be platform-agnostic; but Dictionary is unavailable on Mac.
4. ...import a custom object into my module. My module must be a standalone application; but this custom class adds another dependency.

Solution

It struck me that a keyed Collection resembles an object with named fields (ie. properties). Indeed, we can mimic both fields and methods in the following way! Here obj is an object and clx is a Collection that mimics it.

Object	Collection
obj.Field = 1	clx.Add 1, "Field"
obj.Field	clx.Item("Field")
obj.Method(...)	Method(clx, ...)

Now the native Collection solves all 4 roadblocks:

1. its declaration will always compile; and
2. it is viable in regular modules, whose functions are usable in Excel; and
3. it is available on Mac; and
4. it requires no further dependencies.

So all we need is a comprehensive API of procedures, to maintain the illusion of an object: the New_Obj() function mimics the constructor; and the Obj_*() family mimics the accessors and methods.

Functionality	Object	Mimicry API	Mimicry Description
Construction	Set x = New Obj	Set x = New_Obj()	Return a Collection populated by uninitalized values under keys.
Getting	x.Field	Obj_Field(x)	Return the value under the "Field" key.
Setting	x.Field = 1	Obj_Field x, 1	Update the value under the "Field" key.
Invocation	x.Method(...)	Obj_Method(x, ...)	A function that takes a Collection and acts like .Method().

Question

What is the best approach for the API? I have laid out several variations below.

Code

Each of the approaches below relies on three helpers, which "extend" the Collection. See the Helpers section for their source code.

• Clx_Exists(): Test if an item exists under a given key (or at a given position).
• Clx_Set(): Update the value under a given key.
• Assign(): Assign any value (scalar or objective) to a variable.

Prototype

This illustration involves an "object" with a set of "fields"...

• Field1 which is an Integer
• Field2 which is a String
• Field3 which is a Collection
• ...

...and a set of "methods":

• Method1() which simply multiplies x by Field1
• ...

We implement the New_Obj() function to mimic a constructor; and the Obj_*() family of functions to mimic its accessors and methods.

' ############
' ## Fields ##
' ############

Private Const OBJ_KEY_FIELD1 As String = "Field1"
Private Const OBJ_KEY_FIELD2 As String = "Field2"
Private Const OBJ_KEY_FIELD3 As String = "Field3"
' ...



' #########
' ## API ##
' #########

' Constructor.
Public Function New_Obj( _
    Optional ByVal Field1 As Integer, _
    Optional ByVal Field2 As String, _
    Optional ByVal Field3 As Collection, _
    ' ...
) As Collection
    Dim obj As Collection: Set obj = New Collection
    
    ' Set the values for each field.
    Obj_Field1 obj, val := Field1
    Obj_Field2 obj, val := Field2
    Obj_Field3 obj, val := Field3
    ' ...
    
    ' Ensure the result is fully initialized.
    Obj_Initialize obj
    
    ' Return the result.
    Set New_Obj = obj
End Function


' Field accessors.
Public Function Obj_Field1(ByRef obj As Collection, _
    Optional ByVal val As Variant _
) As Integer
    ' Get the field value...
    Obj_Field1 = obj.Item(OBJ_KEY_FIELD1)
    
    ' ...and optionally set it.
    If Not IsMissing(val) Then
        Dim f1 As Integer: f1 = val
        Clx_Set obj, OBJ_KEY_FIELD1, f1
    End If
End Function


Public Function Obj_Field2(ByRef obj As Collection, _
    Optional ByVal val As Variant _
) As String
    ' Get the field value...
    Obj_Field2 = obj.Item(OBJ_KEY_FIELD2)
    
    ' ...and optionally set it.
    If Not IsMissing(val) Then
        Dim f2 As String: f2 = val
        Clx_Set obj, OBJ_KEY_FIELD2, f2
    End If
End Function


Public Function Obj_Field3(ByRef obj As Collection, _
    Optional ByVal val As Variant _
) As Collection
    ' Get the field value...
    Set Obj_Field3 = obj.Item(OBJ_KEY_FIELD3)
    
    ' ...and optionally set it.
    If Not IsMissing(val) Then
        Dim f3 As Collection: Set f3 = val
        Clx_Set obj, OBJ_KEY_FIELD3, f3
    End If
End Function


' Methods.
Public Function Obj_Method1(ByRef obj As Collection, _
    ByVal x As Integer _
) As Integer
    Dim f1 As Integer: f1 = Obj_Field1(obj)
    
    Obj_Method1 = x * f1
End Function

' ...



' #############
' ## Helpers ##
' #############

' Initializer.
Private Sub Obj_Initialize(ByRef obj As Collection)
    ' Ensure the "obj" collection is ready.
    If obj Is Nothing Then Set obj = New Collection
    
    
    ' Initialize fields that do not exist already.
    Dim f1 As Integer
    If Not Clx_Exists(obj, OBJ_KEY_FIELD1) Then Obj_Field1 obj, val := f1
    
    Dim f2 As String
    If Not Clx_Exists(obj, OBJ_KEY_FIELD2) Then Obj_Field2 obj, val := f2
    
    ' I want the "Field3" collection to exist AND be ready for action.
    Dim f3 As Collection
    If Clx_Exists(obj, OBJ_KEY_FIELD3) Then
        Set f3 = Obj_Field3(obj)
        If f3 Is Nothing Then Obj_Field3 obj, val := New Collection
    Else
        Obj_Field3 obj, val := New Collection
    End If
    
    ' ...
Sub

Keys

We have keyed the "fields" with String constants. Thus we maintain them centrally, rather than hard-coding Strings everywhere.

' ############
' ## Fields ##
' ############

Private Const OBJ_KEY_FIELD1 As String = "Field1"
Private Const OBJ_KEY_FIELD2 As String = "Field2"
Private Const OBJ_KEY_FIELD3 As String = "Field3"
' ...

However, we could also use an enumeration, which would require translation into a key String. In this case, Obj_Key(*) would replace every OBJ_KEY_*.

Private Enum ObjField
    Field1
    Field2
    Field3
    ' ...
End Enum

' ...

Private Function Obj_Key(ByVal fld As ObjField) As String
    Select Case fld
        Case ObjField.Field1: Obj_Key = "Field1"
        Case ObjField.Field2: Obj_Key = "Field2"
        Case ObjField.Field3: Obj_Key = "Field3"
        ' ...
    End Select
End Function

Optional Fields

Unlike actual objects or custom Types, which always have their full set of fields, a Collection may lack some elements. As such, our "object" can treat certain "fields" as optional.

When this "object" has all its "fields", it technically mimics "subclass" of a similar "object" with missing "fields". The advantage is that the absence of a field communicates different information than its uninitialized presence, so we gain flexibility of meaning. The disadvantage is a slight divergence from the behavior of an actual object.

Here is an illustration of making Field2 an optional "field":

' Constructor.
Public Function New_Obj( _
    ' ...
    Optional ByVal Field2 As String = vbNullString, _
    ' ...
    ' ...
) As Collection
    ' ...
    
    ' Optionally include "Field2".
    If Field2 <> vbNullString Then Obj_Field2 obj, Field2
    
    ' ...
End Function

' ...

' Fields.
Public Function Obj_Field2(ByRef obj As Collection, _
    Optional ByVal val As Variant _
) As String
    ' Get any field value that MIGHT exist...
    If Clx_Exists(obj, OBJ_FLD_FIELD2) Then Obj_Field2 = obj(OBJ_FLD_FIELD2)
    
    ' ...
End Function

' ...

' Initializer.
Private Sub Obj_Initialize(ByRef obj As Collection)
    ' ...
    
    ' ' Skip initialization for "Field2".
    ' Dim f2 As String
    ' If Not Clx_Exists(obj, OBJ_FLD_FIELD2) Then Obj_Field2 obj, val := f2
    
    ' ...
End Sub

Extent of Helpers

For Private helpers, there are many tradeoffs between functionality, brevity, elegance, consistency, and ease of maintenance.

1. To protect certain "fields" from mutation, we might omit the val argument from their accessors. But we can no longer use (say) Obj_Field1 obj, val := ... to update the value within New_Obj() and Obj_Initialize().

2. To independently maintain Obj_Field1(), without fear of disrupting functionality, we could replace its calls with Clx_Set obj, OBJ_FLD_FIELD1, .... But this is inconsistent with using Obj_Field1(obj) to simply retrieve the value.

3. To preserve consistency, we could replace all calls like Obj_Field1(obj) with obj.Item OBJ_FLD_FIELD1. But this makes the whole thing visually messy.

4. To restore visual elegance, we could implement these operations as Private procedures: Obj_HasField1() and Obj_GetField1() and Obj_SetField1(). But this requires 3 separate functions for every field, so the code expands dramatically with many implementations.

5. To centrally maintain the mechanisms for Obj_Has*() and Obj_Get*() and Obj_Set*(), we could have them wrap underlying helpers: Obj_Has() and Obj_Get() and Obj_Set(). But this adds 3 more functions to an expanding module.

   Private Function Obj_Has(ByRef obj As Collection, ByVal fld As String) As Boolean
       Obj_Has = Clx_Exists(obj, fld)
   End Function
   
   Private Function Obj_Get(ByRef obj As Collection, ByVal fld As String) As Variant
       Assign Obj_Get, obj.Item(fld)
   End Function
   
   Private Function Obj_Set(ByRef obj As Collection, ByVal fld As String, ByRef val As Variant)
       Clx_Set obj, fld, val
   End Function
   

6. For the sake of brevity, we could replace all instances of

   • Obj_Has*(obj) with Obj_Has(obj, OBJ_FLD_*)
   • Obj_Get*(obj) with Obj_Get(obj, OBJ_FLD_*)
   • Obj_Set* obj, ... with Obj_Set obj, OBJ_FLD_*, ...

   This leaves us with only 3 helpers in total, rather than 3 for each "field": just Obj_Has() and Obj_Get() and Obj_Set(). But while Obj_Field1(obj) embeds the "field" name within the syntax, the new Obj_Get(obj, ...) imposes no structure on the "field" name, and we lose stability.

7. For the sake of stability, we could restrict the "field" to the ObjField enumeration. But this requires Obj_Key(), which must be maintained in sync with the enumeration, so maintenance becomes decentralized.

   Private Function Obj_Has(ByRef obj As Collection, ByVal fld As ObjField) As Boolean
       Obj_Has = Clx_Exists(obj, Obj_Key(fld))
   End Function
   
   Private Function Obj_Get(ByRef obj As Collection, ByVal fld As ObjField) As Variant
       Assign Obj_Get, obj.Item(Obj_Key(fld))
   End Function
   
   Private Function Obj_Set(ByRef obj As Collection, ByVal fld As ObjField, ByRef val As Variant)
       Clx_Set obj, Obj_Key(fld), val
   End Function
   

Properties

Could we rewrite the "fields" as modular properties, to allow easy access? We could obviously "protect" certain "fields" by making their Let/Set properties Private.

' Fields
Public Property Get Obj_Field1(ByRef obj As Collection) As Integer
    Obj_Field1 = obj.Item(OBJ_FLD_FIELD1)
End Property

Public Property Let Obj_Field1(ByRef obj As Collection, ByVal val As Integer)
    Clx_Set obj, OBJ_FLD_FIELD1, val
End Property

' ...

Public Property Get Obj_Field3(ByRef obj As Collection) As Collection
    Set Obj_Field3 = obj.Item(OBJ_FLD_FIELD3)
End Property

Public Property Set Obj_Field3(ByRef obj As Collection, ByRef val As Collection)
    Clx_Set obj, OBJ_FLD_FIELD3, val
End Property

My hope is that the API user can access the fields like so:

Debug.Print Obj_Field1(obj)

Obj_Field1(obj) = 2

' ...

Set Obj_Field3(obj) = New Collection

---

Helpers

Here is the code for the three helpers.

Private Function Clx_Exists(ByRef clx As Collection, _
    ByVal index As Variant _
) As Boolean
    On Error GoTo Fail
    clx.Item index
    
    Clx_Exists = True
    Exit Function
    
Fail:
    Clx_Exists = False
End Function

Private Function Clx_Set(ByRef clx As Collection, _
    ByVal key As String, _
    ByRef val As Variant _
)
    If Clx_Exists(clx, key) Then
        clx.Remove key
    End If
    
    clx.Add val, key := key
End Function

Private Sub Assign( _
    ByRef var As Variant, _
    ByVal val As Variant _
)
    If IsObject(val) Then
        Set var = val
    Else
        var = val
    End If
End Sub