Excel VBA CommandBar.OnAction with params is difficult / does not perform as expected

Question

So, I have Googled about and it seems that while making custom Pop up menus, if one wants to pass parameters then this is possible but for me comes with 2 major problems:

1) The function you call will execute, but you will not be able to activate a breakpoint on it, or even use Stop.

2) Oddly it seems to get called twice, which isn't very helpful either.

Code to illustrate ( please put in a module and not in a sheet )

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

If someone could help with this, that would be great. It seems another developer in the past hit the wall and so for the 5 items we have Method_1 ... Method_5 with the number passed into Method_Core(ByVal i As Integer) style. I think I will take this route too although very ugly, it works better than what I have mocked up below.

PS. This is a quick mockup so I don't expose proprietary code etc

Answer 1

You can use the .Parameter property. This is an example of a code in production (with only the lines of interest):

        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

And the procedure could be something like:

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