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

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

CodePudding user response：

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