带参数的 Excel VBA CommandBar.OnAction 很困难/未按预期执行

Question

所以，我在 Google 上搜索了一下，似乎在制作自定义弹出菜单时，如果想传递参数，那么这是可能的，但对我来说有两个主要问题:

1) 您调用的函数将会执行，但您将无法在其上激活断点，甚至无法使用 Stop。

2)奇怪的是，它似乎被调用了两次，这也不是很有帮助。

说明代码(请放在模块中，而不是放在工作表中)

Option Explicit

Sub AssignIt()

    Dim cbrCmdBar As CommandBar
    Dim strCBarName As String

    On Error Resume Next

    strCBarName = "MyNewPopupMenu"

    'Delete it first so multiple runs can occur without appending
    Application.CommandBars(strCBarName).Delete

    ' Create a menu bar.
    Set cbrCmdBar = Application.CommandBars.Add(Name:=strCBarName, Position:=msoBarMenuBar)

    ' Create a pop-up menu.
    strCBarName = "MyNewPopupMenu"
    Set cbrCmdBar = Application.CommandBars.Add(Name:=strCBarName, Position:=msoBarPopup)

    'DEFINE COMMAND BAR CONTROL
    With Application.CommandBars(strCBarName).Controls.Add(Type:=msoControlButton)
    .Caption = "MyMenu"
    .OnAction = BuildProcArgString("MyProc", "A", "B", "C") 'You can add any number of arguments here!
    End With

    'DEFINE COMMAND BAR CONTROL
    With Application.CommandBars(strCBarName).Controls.Add(Type:=msoControlButton)
        .Caption = "Test No Args"
        .OnAction = "CallWithNoArgs"
    End With


    Application.CommandBars(strCBarName).ShowPopup

End Sub


Sub CallWithNoArgs()

    MsgBox "No Args"

End Sub

'FUNCTION TO BUILD PROCEDURE ARGUMENTS (You just have to plop this in any of your modules)
Function BuildProcArgString(ByVal ProcName As String, ParamArray Args() As Variant)

    Dim TempArg
    Dim Temp

    For Each TempArg In Args
        Temp = Temp + Chr(34) + TempArg + Chr(34) + ","
    Next

    BuildProcArgString = ProcName + "(" + Left(Temp, Len(Temp) - 1) + ")"

End Function

'AND FINALLY - THE EXECUTABLE PROCEDURE!
Sub MyProc(x, y, z)

    MsgBox x & y & z
    Debug.Print "arrgggh why won't the breakpoint work, and why call twice!!!!!!"

End Sub

如果有人能帮忙解决这个问题，那就太好了。过去似乎有另一个开发人员碰壁了，因此对于 5 个项目，我们有 Method_1 ... Method_5，并将数字传递到 Method_Core(ByVal i As Integer) 样式。我想我也会走这条路，虽然很丑，但它比我下面模拟的效果更好。

PS。这是一个快速模型，因此我不会公开专有代码等

Answer 1

您可以使用.Parameter 属性。这是生产中的代码示例(仅包含感兴趣的行):

        Dim i As Integer
        Set cl = MainForm.Controls("classroomList")
        For i = 0 To cl.ListCount - 1
            With .Controls.Add(Type:=msoControlButton)
                .Caption = cl.List(i)
                .faceId = 177
                .OnAction = "'" & ThisWorkbook.Name & "'!" & "assignClassroom"
                .Parameter = cl.List(i)
            End With
        Next i

程序可能类似于:

Public Sub assignClassroom(Optional someArg as SomeType)
' code here
CommandBars.ActionControl.Parameter 'The parameter here
' more code here
End Sub