10
\$\begingroup\$

Bit of background info first - I've been struggling with a problem for a while now. How could you have one single event handler to handle every control (or all of a particular type of control) on a form? I've never been able to find a complete solution online either. The two 'easy' methods have glaring issues. For sake of example, let's say we want every text box to change its border color when it gains focus. The code for changing one text box border on focus is easy:

Private Sub TextBox0_GotFocus()
    TextBox0.BorderColor = vbBlue
End Sub

But doing this for every single text box causes a problem. The brute-force option would be to manually add a handler to every text box, and have them all call a master handler (http://sharetext.org/SWek). That creates a lot of duplicate code, fast. And it's time-consuming, boring, easy to forget to do... and as a programmer I'm too lazy to do it like this.

The other option is to set the handler for every text box to a constant expression in the form designer (eg =AnyTextBox_HandleGotFocus()), but doing so makes you lose which text box actually got the focus:

Private Sub AnyTextBox_HandleGotFocus()
    ' ??? Which text box just fired this? I have no way of knowing!
End Sub

I'd need to loop through the form's entire control collection every single time this thing fires. I also lose the ability to grant specific behaviour to an individual control. Unless I whack a massive Switch statement inside the loop or something. Shudder.

After a long time rolling it around in my head, I think I have a solution. It isn't perfect, but the caveats of this are much smaller than the caveats of the above.


First off, we want to build a collection of the controls we want to use. We can then loop through it and assign all of their event handlers to a single handler programatically. This is the only loop in this code.

Private iTextBoxes As Collection

Private Sub Form_Load()
    BuildControlCollection Me, iTextBoxes, eTextBox

    Dim lTextBox As TextBox
    For Each lTextBox In iTextBoxes
        lTextBox.OnGotFocus = "=AnyTextBox_GotFocus(" & lTextBox.Name & ")"
    Next lTextBox
End Sub

Not relevant to the question, but BuildControlCollection here turns iTextBox into a collection of all the text boxes on the form. Don't worry about the details of my Hungarian notation either.

Next, let's add the 'master' event handler:

Public Function AnyTextBox_GotFocus(ByRef mpTextBox As TextBox)
    mpTextBox.BorderColor = RGB(100, 150, 215)
    FireControlSingleEvent mpTextBox, "GotFocus"
End Function

That FireControlSingleEvent is the important part (the name is a bit awkward, suggestions welcome!) - this fires the control's 'unique' event handler, so I can add handling for a specific control.

Private Function FireControlSingleEvent(ByRef mpControl As Control, _
                                        ByVal ipEventProcName As String)
Try:
    On Error GoTo Catch
    Dim iProcName As String
    iProcName = Me.Controls(mpControl.Name).EventProcPrefix & "_" & ipEventProcName

    Select Case ipEventProcName
    Case "Click", "AfterUpdate", "Change", "GotFocus", "LostFocus", "Enter":
        CallByName Me, iProcName, VbMethod
    Case "KeyPress":
        CallByName Me, iProcName, VbMethod, mLastKeyPressedAscii
    Case Else:
        Debug.Print "Multi event handling cannot support the " & ipEventProcName & " event."
    End Select

    GoTo Finally

Catch:
    If Err.Number = 2465 Then
        Debug.Print "Procedure " & Forms("Form1").Controls(mpControl.Name).EventProcPrefix & "_" & ipEventProcName & _
            " does not exist or is private"
    Else
        Err.Raise Err.Number
    End If

Finally:
    On Error GoTo 0
End Function

This attempts to call a method named with the standard convention, according to the control and event procedure name it's given. For example, if it's handed TextBox0 and GotFocus, it will attempt to call TextBox0_GotFocus(). The error handling stops it crashing if TextBox0_GotFocus() doesn't exist, removing the necessity for having empty handlers for every single control.

The Select statement is there as I haven't worked out how to handle every single event yet - only events that don't need arguments work with this code, so things like mouse events are currently out.(KeyPress is a special case - by turning Key Preview on at the form level, I can capture the KeyAscii required into mLastKeyPressedAscii in the form's Form_KeyPress event, and pass that)


Known caveats

  • As mentioned above, event handlers that take arguments - such as MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) - aren't compatible without losing the data that would be passed to the parameters.
  • The TextBox0_GotFocus()-type handlers must be altered to Public rather than Private - CallByName can't find Private handlers.

Any suggestions or improvements on this code? There's a few bits I'm iffy about - always nervous about using errors as an intentional part of the code structure, for eg. Feedback welcome.

\$\endgroup\$
  • 1
    \$\begingroup\$ If anyone's wondering what the hungarian notation means: i this variable shouldn't be changed once initially set ('immutable'); m this variable is expected to be changed ('mutable'); p parameter; l index / object for a loop; e Enum value. It's experimental, just been trying it on for size recently. \$\endgroup\$ – Kai Mar 14 '14 at 16:20
  • \$\begingroup\$ this looks exciting, I will have to see what happens by the time I get home tonight. you are missing some indentation on your case statement too by the way \$\endgroup\$ – Malachi Mar 14 '14 at 16:42
  • \$\begingroup\$ That's the way the VBA IDE forces it to appear - kinda funny that it's one of very few places it attempts to auto-indent, and it does it in a strange way :P \$\endgroup\$ – Kai Mar 14 '14 at 18:10
  • \$\begingroup\$ I am still trying to Learn VBA as I go. we only use it sparingly for certain things pertaining to a third party application. we create and maintain certain small pieces that are needed in the application and they are coded in VBScript, kind of VBA I still can't figure out what the difference is. \$\endgroup\$ – Malachi Mar 14 '14 at 18:14
  • \$\begingroup\$ This post has just taught me about CallByName - and now there's a whole new world of refactoring possibilities that have just appeared right before my eyes... I'm so upvoting this, the minute I get votes back (out of ammo right now..). \$\endgroup\$ – Mathieu Guindon Mar 14 '14 at 19:28
7
\$\begingroup\$

I don't have lots of time, so I'll just make a couple of points:

  • Drop the "Hungarian" notation altogether, it's not doing you any good. Instead declare variables close to their usage, and strive to keep procedures < 25 lines.
  • If you have to stick to that notation, seriously reconsider the lowercase "L" prefix. There's practically zero difference between "l" and "1" in Courier New, the IDE's default font.

But those are merely nitpicks. The main thing that bothers me is your try/catch/finally emulation. Obviously you're coming from a language that handles errors with exceptions. VB6/VBA doesn't have that.

GoTo Finally

Why use GoTo instead of restructuring the flow?

goto-spawns-raptors

Sub DoSomething()
    On Error GoTo ErrHandler

    'do something

ErrHandler:     
    'this part runs regardless of error state
    If Err.Number <> 0 Then
        'handle specific and/or general error cases
    End If
    'single exit point
End Sub

The only recommendable usage for GoTo is when it immediately follows an On Error statement.

Error number 2465 should have a comment that says what it is, or even better, a properly named constant that conveys its meaning:

If Err.Number = HELL_BROKE_LOOSE Then

Lastly, On Error GoTo 0 is not needed, since On Error GoTo ErrHandler is scoped to the method; however it would be necessary if you had On Error Resume Next somewhere in there.

\$\endgroup\$

protected by Malachi Jun 17 '15 at 16:50

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.