Making modular VBA function (MS Access)

问题:

I am writing up a simple multi-condition search form. The Access VBA function set for the first toggle button looks like this:

Private Sub ToggleQ1_Click()
Select Case ToggleQ1.Value
    Case True
        CondQ1 = "AND"
        ToggleQ1.Caption = CondQ1
    Case False
        CondQ1 = "OR"
        ToggleQ1.Caption = CondQ1
End Select
End Sub

ToggleQ1 = button's name

CondQ1 = variable to be used with a string to create conditional search.

It would likely be absurd to create 50 more of the same button code, differ only in its name (ex. "ToggleQ50" and "CondQ50")

Is there any way to make it modular and reusable? Thank you very much in advance.

回答1:

In the form's module create a function (not sub) like this:

Private Function SetCaption()
    Dim clickedButton As Control
    Dim CondQ1 As String

    Set clickedButton = Me.ActiveControl
    Select Case clickedButton.Value
        Case True
            CondQ1 = "AND"
            clickedButton.Caption = CondQ1
        Case False
            CondQ1 = "OR"
            clickedButton.Caption = CondQ1
    End Select
End Function

In form designer select all 50 buttons and type in property On Click

=SetCaption()

So, you won't need to create event handler for each button.

回答2:

Create another sub and send the clicked button to it. Similar to this:

Private Sub cmdTest01_Click()
    SetCaption cmdTest01
End Sub

Private Sub cmdTest02_Click()
    SetCaption cmdTest02
End Sub

Private Sub SetCaption(clickedButton As CommandButton)
    Dim CondQ1 As String

    Select Case clickedButton.Caption
        Case "Test01"
            CondQ1 = "AND"
            clickedButton.Caption = CondQ1
        Case "Test02"
            CondQ1 = "OR"
            clickedButton.Caption = CondQ1
     End Select
End Sub

Case blocks can be simplified to

Case "Test01"
    clickedButton.Caption = "AND"
Case "Test02"
    clickedButton.Caption = "OR"

回答3:

Use WithEvents. That takes a little code when loading and unloading the form, but zero code for each button.

A similar example with full code, which you should be able to adapt, can be found here:

Create Windows Phone Colour Palette and Selector using WithEvents

and at GitHub: VBA.ModernTheme

Code snippet:

Private ControlCollection   As Collection

Private Sub Form_Load()

    ' Load events for all colour value textboxes.

    Dim EventProcedure  As ClassTextboxSelect
    Dim Control         As Access.Control

    Set ControlCollection = New Collection

    For Each Control In Me.Controls
        If Control.ControlType = acTextBox Then
            Set EventProcedure = New ClassTextboxSelect
            EventProcedure.Initialize Control
            ControlCollection.Add EventProcedure, Control.Name
        End If
    Next

    Set EventProcedure = Nothing
    Set Control = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)

    Dim Index   As Integer

    ' Set colour palette.
    For Index = 0 To 20
        Me("Box" & CStr(Index + 1)).BackColor = PaletteColor(Index)
        Me("Name" & CStr(Index + 1)).Value = LiteralWpThemeColor(PaletteColor(Index))
        Me("Css" & CStr(Index + 1)).Value = RGBHex(PaletteColor(Index))
        Me("Vba" & CStr(Index + 1)).Value = PaletteColor(Index)
        Me("Hex" & CStr(Index + 1)).Value = "&H" & Hex(PaletteColor(Index))
    Next

End Sub

Private Sub Form_Unload(Cancel As Integer)

    ' Unload events for all colour value textboxes.

    Dim EventProcedure  As ClassTextboxSelect

    For Each EventProcedure In ControlCollection
        EventProcedure.Terminate
    Next

    Set EventProcedure = Nothing
    Set ControlCollection = Nothing

End Sub