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?
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
User contributions licensed under CC BY-SA 3.0