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
– freeflow, Commented Dec 7, 2022 at 17:50
How about one of the solutions here? stackoverflow.com/questions/20783170/pass-array-to-paramarray Don't know how/if they translate to .Net... — Tim Williams
– Tim Williams, Commented Dec 7, 2022 at 18:59
@TimWilliams, I saw that before. That's for unboxing and not for passing to COM. — HackSlash
– HackSlash, Commented Dec 7, 2022 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
– FaneDuru, Commented Dec 7, 2022 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... — FaneDuru
– FaneDuru, Commented Dec 7, 2022 at 20:47

HackSlash · Accepted Answer · 2022-12-07 21:20:51Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related