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

Question

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?

lastGroupRowExcel, firstGroupRowExcel, mergeColumns, rightFormatMergeColumn, multiRowColumn, — sertsedat
– sertsedat, Commented Jun 24, 2016 at 10:25
Regarding that question, I would like to refer to the sentence below the C# code: "where all the parameters are of type Int32, except mergeColumns which is an Int32[9]" — Manuel Hoffmann
– Manuel Hoffmann, Commented Jun 24, 2016 at 10:31

Nathan_Sav · Accepted Answer · 2016-06-24 13:29:05Z

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 Jun 24, 2016 at 13:29

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Manuel Hoffmann Over a year ago

So, how do I get the integer array from the variant?

Nathan_Sav Over a year ago

For i = 0 To UBound(lastGroupRow) Debug.Print i Next I

Manuel Hoffmann Over a year ago

I tried passing the array as a Long array while leaving it as int[] in C# and it worked. So I'll be using the Long array solution, but I'm pretty sure this solution would work as well. Since you were also the one to direct me to the Long array solution, I'm going to accept this answer while leaving the hint that it works with passing the array as Long ()

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related