Reducing Decoupling in VBA Object Event Wrapper-CodePudding

tl;dr Is there a way to enable events for built-in objects without coupling the event to the original object's parent, assuming the event interacts with the parent?

Disclaimer 1: I don't have access to MS Office on my home machine and therefore type all code from memory. I'm sorry if something's incorrect.

Disclaimer 2: This post is incredibly lengthy because I've been trying to figure out how to do this process for several years but never quite hit the correct Google terms to figure it out. I do a lot of explaining in the hopes that it might help someone with the same issues.

The Original Problem

I've had this longstanding issue of having Userforms with near-identical event handling but no way to compact the code into a generic solution. For example, let's say I have a Userform with a bunch of Command Buttons that all do the same thing when clicked. Traditionally, you would have to include something like the following in Userform1

Private Sub CommandButton1_Click()
    Me.DoSomething CommandButton1.Name
End Sub

Private Sub CommandButton2_Click()
    Me.DoSomething CommandButton2.Name
End Sub

 '...a bunch more of these...'

Private Sub CommandButtonN_Click()
    Me.DoSomething CommandButtonN.Name
End Sub

This is annoying to setup and hurts readability for a large number of buttons.

The Naive Solution

I recently discovered that wrapper classes can be utilized to make a generic WithEvents handler for built-in objects. Applying this to our previous example, we create an EventCommandButton.cls Class with the following code

Private WithEvents mCommandButton as MSForms.CommandButton

Private Sub mCommandButton_Click()
    mCommandButton.Parent.DoSomething(mCommandButton.Name) 
End Sub

Property Get CommandButton() as MSForms.CommandButton
    Set CommandButton = mCommandButton
End Property

Property Set CommandButton(cmdBtn as MSForms.CommandButton)
    Set mCommandButton = cmdBtn
End Property

And Userform1 turns into

Private EventCommandButtons() as New EventCommandButton

Private Sub Userform1_Initialize()
    For Each ctl in Me.Controls
        If TypeName(ctl) = "CommandButton" Then
            i = i   1
            ReDim Preserve EventCommandButtons(1 to i) 
            Set EventCommandButtons(i).CommandButton = ctl
        End If
    Next
End Sub

This approach saves space and looks comparatively nice, but it presents (at least) 2 major issues:

All of Userform1's control events are no longer housed in its own code
Our EventCommandButton requires a specific procedure (DoSomething(str)) to exist in its parent or else we'll get an error.

A Slight Refinement

The solution I'm currently implementing is to take a more intuitive approach that returns control of the event handling back to where you'd expect it to be. In EventCommandButton.cls we add a new property to specify where we expect to find the return code:

Private mCommandButton as MSForms.CommandButton
Private mCallback as Object

Private Sub mCommandButton_Click()
    'Some error handling should be here to check that mCallback is set
    mCallback.EventCommandButton_Click(mCommandButton) 
End Sub

Property Get Callback() as Object
    Set Callback = mCallback
End Property

Property Set Callback(ParentObject as Object)
    'Let's not assume it's always the .Parent
    Set mCallback = ParentObject
End Property 

Property Get CommandButton() as MSForms.CommandButton
    Set CommandButton = mCommandButton
End Property

Property Set CommandButton(cmdBtn as MSForms.CommandButton)
    Set mCommandButton = cmdBtn
End Property

And in Userform1

Private EventCommandButtons() as New EventCommandButton

Public Sub EventCommandButton_Click(cmdBtn as MSForms.CommandButton)
    Me.DoSomething cmdBtn.name
End Sub

Private Sub Userform1_Initialize()
    For Each ctl in Me.Controls
        If TypeName(ctl) = "CommandButton" Then
            i = i   1
            ReDim Preserve EventCommandButtons(1 to i)
            Set EventCommandButtons(i).CommandButton = ctl
            Set EventCommandButtons(i).Callback = Me         'Set new property
        End If
    Next
End Sub

This approach feels close to the intuitive solution of the original problem (with some extra steps involved) and resolves issue #1 from the previous, but we still have issues:

There's still coupling between the Class and Userform, now requiring that each parent object must have corresponding pseudo-event procedures of the form Public Sub [ClassName]_[EventName]([OriginalObject], Optional [EventParams]), which isn't intuitive and looks weird amongst the sea of Private Event Subs.
The coupling now depends on the class name, which may not always be ideal. Renaming the class will require editing the events to reflect that.
For the wrapper to be "complete", it must include all events and error handling to ignore the ones that aren't setup on the Parent side. At some point I'd think having all these On Error GoTo EoF statements in each class instance will have a performance impact.

The Question

Is there a way that this process can be further improved to reduce the coupling between (in this case) the Class and Form code? With VBIDE we could detect the classname and generate the pseudo-events, but without VBIDE access it seems like it requires some upkeeping and instruction to properly utilize the class.

In Python (and I'm sure other languages), you could just pass a reference to a function to direct the event returns; however, VBA doesn't seem to support this.

CodePudding user response：

If you can pass the method name from the parent as a string you could use something like CallByName mCallback, vbMethod, mProcName, mCommandButton from within the class instance, to call the method mProcName on the parent, passing the clicked-on button.

For example:

Event class (properties changed to public fields for brevity)

Option Explicit

Public WithEvents mCommandButton As MSForms.CommandButton
Public mCallback As Object  '<< object on which the callback method is to be called
Public mProcName As String  '<< name of the callback method

Private Sub mCommandButton_Click()
    CallByName mCallback, mProcName, VbMethod, mCommandButton
End Sub

Form code:

Private EventCommandButtons  As Collection

Public Sub ButtonClick(cmdBtn As MSForms.CommandButton)
    MsgBox "clicked on button " & cmdBtn.Caption
End Sub

Private Sub Userform_Initialize()
    Dim ctl As Object
    Set EventCommandButtons = New Collection
    For Each ctl In Me.Controls
        If TypeName(ctl) = "CommandButton" Then
            EventCommandButtons.Add NewClickHandler(ctl)
        End If
    Next
End Sub

Function NewClickHandler(btn As Object) As EventCommandButton
    Set NewClickHandler = New EventCommandButton
    Set NewClickHandler.mCommandButton = btn
    Set NewClickHandler.mCallback = Me
    NewClickHandler.mProcName = "ButtonClick"
End Function