VBA Procedure Builder with multiparameter

There are two special VBA events associated to macros or even shapes that could be build on automation. These are the Application.OnKey(Procedure), and the expression.OnAction (beeing expression an Excel.Shape, an Excel.FormsControl or even a CommandBar control). For such events any procedure in the Workbook can be called (has to be declared as Public). And they can even hold parameter(s), none/one/several. It’s very frustrating to deal with such a mess of [‘] and [“] characters, so it’s really come handy to have a call builder procedure. Just paste this code to any module:

Public Function fBuildCaller(ByVal bWorkbookName As Boolean, _
                             ByVal ProcName As String, _
                             ParamArray Args() As Variant) As Variant
' Function to build procedure with variable number of arguments
' Take care that if bWorkBookName = True, will be permanent linked to the Workbook
    Dim oItem As Variant
    Dim oSubItem As Variant
    Dim strDebug As String
    Dim lgRetVal As Long

    For Each oItem In Args
        If IsArray(oItem) Then
            For Each oSubItem In oItem
                strDebug = strDebug & " """ & oSubItem & ""","
            Next oSubItem
        Else
            strDebug = strDebug & " """ & oItem & ""","
        End If
    Next oItem
            
    If bWorkbookName Then
        lgRetVal = VBA.MsgBox("If bWorkbookName is set to True, will be permanently linked to Workbook, go with it?", _
                              vbYesNo + vbExclamation, "I N F O")
        If lgRetVal = vbNo Then bWorkbookName = False
    End If
    If strDebug = vbNullString Then
        strDebug = VBA.IIf(bWorkbookName, "'" & ThisWorkbook.Name & "'!", "") & _
                   "'" & ProcName & "'"
    Else
        strDebug = VBA.IIf(bWorkbookName, "'" & ThisWorkbook.Name & "'!", "") & _
                   "'" & ProcName & VBA.Mid$(strDebug, 1, Len(strDebug) - 1) & "'"
    End If
    fBuildCaller = strDebug
End Function

Public Function fBuildCaller2(ByVal ProcName As String, _
                              ParamArray Args() As Variant) As Variant
' Only working for PopUpMenus... better use fBuildCaller
' Has the advantage that "OnAction" is not linked to the WorkBook name
    Dim oItem As Variant
    Dim strDebug As String

    For Each oItem In Args
        strDebug = strDebug & Chr(34) & oItem + Chr(34) & ","
    Next

    If strDebug = vbNullString Then
        strDebug = ProcName
    Else
        strDebug = ProcName & "(" & VBA.Mid$(strDebug, 1, Len(strDebug) - 1) & ")"
    End If
    fBuildCaller2 = strDebug
End Function

And that’s it. Note1: as stated, parameter bWorkbookName is noticeable important. If the worksheets changes its name or it’s beeing copy/pasted, the item -with the event declaration-, the link to the original will be carried wherever it the item goes, and so Excel will complain on opening. Better of to set it to “False”. Note2: In fBuildCaller Arg variable can handle even an array, but it has to be exploded from 2D to 1D, as a linear array. Watch how the oSubItem works to deal with arrays. Note3: fBuildCaller2 is a similar procedure, but seems it’s only working with CommandBars, it has as advantange the better readibility of the procedure, but has its disadvantages too.