制作模块化VBA功能(MS Access)

Question

我正在写一个简单的多条件搜索表单。为第一个切换按钮设置的Access VBA函数如下所示：

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 =按钮的名称

CondQ1 =与字符串一起使用以创建条件搜索的变量。

再创建50个相同的按钮代码可能是荒谬的，只是名称不同(例如"ToggleQ50“和"CondQ50")

有没有办法让它变得模块化和可重用？非常提前感谢您。

Answer 1

在表单的模块中创建一个函数(不是sub)，如下所示：

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

在窗体设计器中，选择所有50个按钮，然后键入属性On Click

=SetCaption()

因此，您不需要为每个按钮创建事件处理程序。

Answer 2

创建另一个子组件，并将单击的按钮发送给它。如下所示：

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模块可以简化为

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

Answer 3

使用WithEvents。这在加载和卸载表单时需要一些代码，但每个按钮都不需要代码。

这里有一个类似的示例，其中包含完整的代码，您应该能够适应它：

Create Windows Phone Colour Palette and Selector using WithEvents

在GitHub：VBA.ModernTheme

代码片段：

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