How can I pass a Parameter Array to a COM object?

Question

There are similar questions about passing parameter arrays and they work within managed code. When working with Microsoft.Office.Interop.Excel you can run a macro in an Excel file with the Application.Run method. The signature of the method shows that it takes a macro name, and an arbitrary list of arguments that get passed to the Excel VBA Procedure. This looks and behaves like a Parameter array but it is not.

When you pass a parameter array to the argument section of .Run you get:

System.Runtime.InteropServices.COMException: 'Parameter not optional. (Exception from HRESULT: 0x8002000F (DISP_E_PARAMNOTOPTIONAL))'

Which suggests that the Parameter Array is not getting passed correctly, likely due to COM interop not having a ParamArray type.

How would I expand my arbitrary length ParamArray into an arbitrary list of arguments that can be passed to the COM object?

EXAMPLE CODE:

    ''' <summary>
    ''' Run an Excel macro silently in the background. Quits at the end.
    ''' </summary>
    ''' <param name="excelFileName">Full path to Excel file.</param>
    ''' <param name="macroName">The macro you want to run.</param>
    ''' <param name="args">Arguments to pass into that macro procedure.</param>
    Public Shared Sub RunMacro(excelFileName As String, macroName As String, ParamArray args As String())
        ' Create new Excel instance
        Dim excelApp = New Application With {
            .Visible = True,
            .EnableEvents = False   ' Suppress the Workbook_Open event
        }

        Dim excelBooks As Workbooks = excelApp.Workbooks

        ' Open the Excel workbook
        Dim thisWorkbook As Workbook = excelBooks.Open(excelFileName)

        ' Hide the window while the macro runs
        excelApp.Visible = False

        ' Run the VBA procedure.
        excelApp.Run(macroName, args)  ' <-- Throws because it can't accept ParamArray

        ' Cleanup
        thisWorkbook.Close(SaveChanges:=False)
        excelApp.Quit()
    End Sub

This is a point I've raised in discussions for twinBasic. VBA cannot forward a ParamArray as a list of item. To meet the needs of Application run, you will need to deconstruct your param array. — freeflow, Dec 07 '22 at 17:50
How about one of the solutions here? https://stackoverflow.com/questions/20783170/pass-array-to-paramarray Don't know how/if they translate to .Net... — Tim Williams, Dec 07 '22 at 18:59
@TimWilliams, I saw that before. That's for unboxing and not for passing to COM. — HackSlash, Dec 07 '22 at 19:08
I cannot understand what you try accomplishing... If you try calling a `Sub`/`Function` named as `macroName` having a single `ParamArray()` parameter, **you cannot call it passing an array**. Even if you pseudo declare it as `ParamArray()`. **Not only through a COM interop**. **You cannot do it in Excel VBA, neither**. In VBA, `ParamArray()` is used only to pass an undefined number of parameters/types. But **you must call the function using all these parameters**!. If you want passing an array, do it, **but not declaring as 'ParamArray()` and declaring in the called sub `arr () As Variant`**. — FaneDuru, Dec 07 '22 at 20:39
In conclusion, Excel (VBA, COM, whatever) uses, admits a `ParamArray()` **only as a called Sub/function last parameter**. And does that only to allow an undefined number of arguments/types when make the call, as stated above... Please, see [here](https://bettersolutions.com/vba/arrays/paramarray.htm)... — FaneDuru, Dec 07 '22 at 20:47
@FaneDuru For you to understand what I'm doing you would have to know what the signature of `Application.Run` is. The documentation says it can take 0-30 arguments much like a ParameterArray would. https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.Run — HackSlash, Dec 07 '22 at 21:03
Yes, this is its definition. But i suggested above a simpler way, which looks to not be understood... If you use `args As Variant` and not use anymore `Sub macroName(ParamArray() As Variant)`. If you use `Sub macroName(arrP As Variant)` you can pass the whole `args` array as large as you need and process it in Excel workbook procedure **as you want**... — FaneDuru, Dec 07 '22 at 21:18
I was attempting to write a wrapper that matched the signature of `Application.Run` and I eventually did. I think what you are suggesting requires a modification of the VBA code, where what I am doing will work with *any* Excel macro. — HackSlash, Dec 07 '22 at 21:23
In VBA you would call it as `Application Run "WorkbookName!macroName", args`. Convert and use it calling through COM and it will work. Now I need to close my laptop. — FaneDuru, Dec 07 '22 at 21:26

score 2 · Accepted Answer · answered Dec 07 '22 at 21:20

The answer I found is often the case when facing an unpossible situation. "Don't do that".

The interop method of .Run won't take a ParameterArray and there is no way to dynamically pass arguments. So we need a different method. In the case of COM interop you can manually .InvokeMember which accepts parameters as an array of objects. This gets us around the fact that the .Run takes one mandatory string and up to 30 arguments by allowing us to pass it all at once as an array.

Final method looks like this:


''' <summary>
''' Run an Excel macro silently in the background. Quits at the end.
''' </summary>
''' <param name="excelFileName">Full path to Excel file.</param>
''' <param name="macroName">The macro you want to run.</param>
''' <param name="args">Arguments to pass into that macro procedure.</param>
Public Shared Sub RunMacro(excelFileName As String, macroName As String, ParamArray args As String())

    ' Make an array of object of string for the invoker
    Dim argumentArray = args.ToArray()
    Dim objectArray = New Object(argumentArray.Length) {}
    objectArray(0) = macroName
    If argumentArray IsNot Nothing Then argumentArray.CopyTo(objectArray, 1)

    Dim excelApp As Application = Nothing
    Dim excelBooks As Workbooks = Nothing
    Dim thisWorkbook As Workbook = Nothing
    Try
        ' Create new Excel instance
        excelApp = New Application With {
            .Visible = False,       ' Hide the window while the macro runs
            .EnableEvents = False   ' Suppress the Workbook_Open event
        }

        ' Open the Excel workbook
        excelBooks = excelApp.Workbooks
        thisWorkbook = excelBooks.Open(excelFileName)
        excelApp.GetType().InvokeMember(
        "Run", Reflection.BindingFlags.Default Or Reflection.BindingFlags.InvokeMethod, Nothing, excelApp, objectArray
        )
    Finally ' Cleanup
        If thisWorkbook IsNot Nothing Then
            thisWorkbook.Close(SaveChanges:=False)
            Marshal.ReleaseComObject(thisWorkbook)
        End If

        If excelBooks IsNot Nothing Then Marshal.ReleaseComObject(excelBooks)

        If excelApp IsNot Nothing Then
            excelApp.Quit()
            Marshal.ReleaseComObject(excelApp)
        End If
    End Try
End Sub

How can I pass a Parameter Array to a COM object?

1 Answers1