无法将C#int数组传递给VBA Excel宏 [英] Can't pass C# int array to VBA Excel macro

问题描述

我正在尝试使用C#Interop从我的C#应用​​程序中调用Excel工作表(2003)的宏.

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

在使用Integer参数之前，它可以正常工作.但是现在我需要传递一个整数数组，并且不断收到类型不匹配异常.

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))

C#代码如下:

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);

其中所有参数均为 Int32 类型，但 mergeColumns 是 Int32 [9]

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

Excel模板中的VBA宏如下所示:

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

我尝试在VBA中声明数组 ByRef ，并尝试将其声明为 Variant 数组，但未做任何更改.我尝试将MsgBox放在开头，以查看是否在循环或参数级别发生不匹配，并且不显示MsgBox.有人知道如何解决此问题或原因是什么?

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?

推荐答案

我刚刚尝试过

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);

        }

按如下方式调用VBA

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

这篇关于无法将C#int数组传递给VBA Excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！