Simulating Objects with Collections in VBA

Question 1

Update

I have released a prototype (v0.1.0) of mySOb project, which is a generalized framework for easilysimulatingobjects.

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...

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

Solution

It struck me that a keyedCollection resembles an object with named fields (ie. properties). Indeed, we can mimic both fields andmethods in the following way! Hereobj is an object andclx is aCollection that mimics it.

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

Now the nativeCollection solves all 4 roadblocks:

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

So all we need is a comprehensive API of procedures, to maintain the illusion of an object: theNew_Obj() function mimics the constructor; and theObj_*() 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" theCollection. See theHelpers 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 anInteger
Field2 which is aString
Field3 which is aCollection
...

...and a set of "methods":

Method1() which simply multipliesx byField1
...

We implement theNew_Obj() function to mimic a constructor; and theObj_*() 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 = objEnd 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 IfEnd FunctionPublic 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 IfEnd FunctionPublic 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 IfEnd 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 * f1End 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" withString constants. Thus we maintain them centrally, rather than hard-codingStrings 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 anenumeration, which would require translation into a keyString. In this case,Obj_Key(*) would replace everyOBJ_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 SelectEnd Function

Optional Fields

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

When this "object" hasall its "fields", it technically mimics"subclass" of a similar "object" withmissing "fields". The advantage is that theabsence of a field communicates different information than itsuninitialized 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 makingField2 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

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

To protect certain "fields" from mutation, we might omit theval argument from their accessors. But we can no longer use (say)Obj_Field1 obj, val := ... to update the value withinNew_Obj() andObj_Initialize().
To independently maintainObj_Field1(), without fear of disrupting functionality, we could replace its calls withClx_Set obj, OBJ_FLD_FIELD1, .... But this is inconsistent with usingObj_Field1(obj) to simply retrieve the value.
To preserve consistency, we could replace all calls likeObj_Field1(obj) withobj.Item OBJ_FLD_FIELD1. But this makes the whole thing visually messy.
To restore visual elegance, we could implement these operations asPrivate procedures:Obj_HasField1() andObj_GetField1() andObj_SetField1(). But this requires 3 separate functions forevery field, so the code expands dramatically with many implementations.

To centrally maintain the mechanisms forObj_Has*() andObj_Get*() andObj_Set*(), we could have them wrap underlying helpers:Obj_Has() andObj_Get() andObj_Set(). But this adds 3more 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 FunctionPrivate Function Obj_Get(ByRef obj As Collection, ByVal fld As String) As Variant    Assign Obj_Get, obj.Item(fld)End FunctionPrivate Function Obj_Set(ByRef obj As Collection, ByVal fld As String, ByRef val As Variant)    Clx_Set obj, fld, valEnd Function

For the sake of brevity, we could replace all instances of
- Obj_Has*(obj) withObj_Has(obj, OBJ_FLD_*)
- Obj_Get*(obj) withObj_Get(obj, OBJ_FLD_*)
- Obj_Set* obj, ... withObj_Set obj, OBJ_FLD_*, ...
This leaves us with only 3 helpers intotal, rather than 3 foreach "field": justObj_Has() andObj_Get() andObj_Set(). But whileObj_Field1(obj) embeds the "field" name within the syntax, the newObj_Get(obj, ...) imposes no structure on the "field" name, and we lose stability.

For the sake of stability, we could restrict the "field" to theObjField enumeration. But this requiresObj_Key(), which must be maintainedin 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 FunctionPrivate Function Obj_Get(ByRef obj As Collection, ByVal fld As ObjField) As Variant    Assign Obj_Get, obj.Item(Obj_Key(fld))End FunctionPrivate Function Obj_Set(ByRef obj As Collection, ByVal fld As ObjField, ByRef val As Variant)    Clx_Set obj, Obj_Key(fld), valEnd Function

Properties

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

' FieldsPublic Property Get Obj_Field1(ByRef obj As Collection) As Integer    Obj_Field1 = obj.Item(OBJ_FLD_FIELD1)End PropertyPublic Property Let Obj_Field1(ByRef obj As Collection, ByVal val As Integer)    Clx_Set obj, OBJ_FLD_FIELD1, valEnd Property' ...Public Property Get Obj_Field3(ByRef obj As Collection) As Collection    Set Obj_Field3 = obj.Item(OBJ_FLD_FIELD3)End PropertyPublic Property Set Obj_Field3(ByRef obj As Collection, ByRef val As Collection)    Clx_Set obj, OBJ_FLD_FIELD3, valEnd 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 = FalseEnd FunctionPrivate 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 := keyEnd FunctionPrivate Sub Assign( _    ByRef var As Variant, _    ByVal val As Variant _)    If IsObject(val) Then        Set var = val    Else        var = val    End IfEnd Sub

Question 2

Would be good if you could give us an example of how this would be used in Excel as User Defined Functions. I understand what you're trying to achive from an API perspective but I have a hard time figuring out what the real use case would be. Thanks!

Question 3

@CristianBuse This "object" and its "methods" arenot exposed to the Excel user. But itsupports other UDFs within my module, whichare intended for Excel formulae. I haveedited my question to clarify.

Question 4

@CristianBuse I forgot to mention: theProperty approach solves both tradeoffs(1) and(2). We can protect any "field" from mutation, simply by using making itsLet/Set aPrivateProperty. We can even protect it fromreading, by doing the same for itsGet. Butwithin the module, both itsGet andSet remain fully accessible to internal procedures. :)

Question 5

Indeed, usingProperty makes more sense. I use this approach myself quite a lot. It's cleaner - less names to pollute the global namespace and indeed you can control scope by making themPrivate. I just read your post again. I am still struggling to understand why is your module optional. Why does the users code need to compile even if your module is missing?

Question 6

Thanks @Greg I read the questions and the top 3 answers - I understand the idea now. This brings me to the next idea - if your module is a class (.cls instead of .bas) then if you have theAttribute VB_PredeclaredId = True attribute, your class will have a global instance which you can use in your code without raising a compilation error. You would only need to have some runtime error handling in case the class is not imported. You can even expose custom types, declared in the class, viaFriend methods.

Movatterモバイル変換

Stack Exchange Network