Can't pass C# int array to VBA Excel macro

0

I'm trying to call a macro of my Excel worksheet (2003) from my C# application with the C# Interop.

It worked fine before with Integer parameters. But now I need to pass an Array of Integers and I keep getting a type mismatch exception.

COMException: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

The C# code looks like this:

object m = Type.Missing;    
xlApp.Run("MergeColumnsKeepValues", lastGroupRowExcel, firstGroupRowExcel, mergeColumns, rightFormatMergeColumn,
                        multiRowColumn, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m);

where all the parameters are of type Int32, except mergeColumns which is an Int32[9]

The VBA macro inside my Excel template looks like this:

Sub MergeColumnsKeepValues(lastGroupRow As Integer, firstGroupRow As Integer, mergeColumns() As Variant, helpColumnMerge As Integer, multiRowColumn As Integer)
    ... <- no use of array, just declaring variables and stuff
    Dim i As Integer
    For i = LBound(mergeColums) To UBound(mergeColumns)
        Set targetMergeCells = Range(Cells(firstGroupRow, mergeColumns(i)), Cells(lastGroupRow, mergeColumns(i)))
        Call targetMergeCells.PasteSpecial(xlPasteFormats, xlPasteSpecialOperationNone, False)
    Next
End Sub

I tried declaring the Array ByRef in VBA and I tried declaring it as Variant array but nothing changed. I tried to place a MsgBox at the start to see if the mismatch occurs in the loop or at the parameter level and it doesn't show the MsgBox. Does anyone know how to fix this or what the cause is?

c#
vba
excel
macros
asked on Stack Overflow Jun 24, 2016 by Manuel Hoffmann • edited Jun 27, 2016 by Manuel Hoffmann

1 Answer

1

I just tried this

static void Main(string[] args)
        {

            XL.Application xlapp = new XL.Application();
            xlapp.Visible = true;
            xlapp.Workbooks.Open("c:/test/test_c.xlsm");
            int[] x = new int[] { 1, 2, 3, 4 };
            xlapp.Run("MergeColumnsKeepValues", x, 2, 3, 4,5);

        }

calling VBA as follows

Public Sub MergeColumnsKeepValues(ByRef lastGroupRow As Variant, _
                                    firstGroupRow As Integer, _
                                    mergeColumns As Integer, _
                                    helpColumnMerge As Integer, _
                                    multiRowColumn As Integer)
    MsgBox "Hello"
End Sub
answered on Stack Overflow Jun 24, 2016 by Nathan_Sav

User contributions licensed under CC BY-SA 3.0