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.
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.
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:
Userform1
's control events are no longer housed in its own codeEventCommandButton
requires a specific procedure (DoSomething(str)
) to exist in its parent or else we'll get an error.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:
Public Sub [ClassName]_[EventName]([OriginalObject], Optional [EventParams])
, which isn't intuitive and looks weird amongst the sea of Private Event Subs.On Error GoTo EoF
statements in each class instance will have a performance impact.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.
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