如何将数组从C＃（VSTO）项目传递到VBA宏 [英] How to Pass an Array from C# (VSTO) project to VBA Macro

问题描述

我的VSTO解决方案出现性能问题，我相信原因主要是cellColor按单元格的方式设置。

这取决于一个记录集，因此每次都是不同的。 （我不能使用另一行/列的复制格式）

它类似于填充一个值范围，只有那个有几种方法。 >

我想到在C＃中首先创建整个事情（一个XlColorIndex [，]数组），我传递给一个类似于下面的VBA方法：

  Sub fillInterior（ByRef rg As Range，as As Variant）
 // a是表示电子表格颜色的双数组
 Dim r As Long，c As Long 
 Dim tmpRg As Range 
r = 1 
c = 1 
对于每一行
对于每个colorIdx In Row 
设置tmpRg = rg（r，c）
使用tmpRg.Interior 
 .ColorIndex = colorIdx 
 .PatternColorIndex = xlAutomatic 
 .PatternColor = xlSolid 
结束
c = c + 1 
下一个
c = 1 
r = r + 1 
下一个
 End Su b 
  

我一直在尝试以下列方式调用此宏，但尚未成功但是，任何指针都非常感激：

  Excel.Range rg = this.Range [this.Cells [5，3] this.Cells [6，4]]; 
 
 object [，] test2 = new object [2，2]; 
 test2 [0，0] = 15; 
 test2 [0，1] = 15; 
 test2 [1，0] = 15; 
 test2 [1，1] = 15; 
 
 this.Application.Run（Sheet1.fillInterior，rg，test2，
 System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type。缺少
 System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System。 Type.Missing，
 System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing， System.Type.Missing，
 System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type.Missing，System.Type。缺少，System.Type.Missing）; 
  

我试过int [，] -

 
我尝试使用Nullable int或Double > 参数不正确。 （HRESULT的异常：0x80070057（E_INVALIDARG））
  

如果我不尝试可空类型，遵循HRESULT错误（类型missmatch？）

  HRESULT异常：0x800A000D 
  pre> 
 
解决方案
好的，我应该读过这个更好：应该避免变体，所以如果我有选择写我的VBA，我最好没有变体，但是使用正确的数组。
 
 
 
其次，我使用VBA数组错误，我应该为多维数组（源）：
 
 
 
我的VBA代码现在如下所示： 
  Sub fillInteriorMulti（rg As Range，Arr（）As Long）
 Dim N As Long，Ndx1 As Long， Ndx2 As Long 
 Dim icol As Long 
 Dim irow As Long 
 Dim NumDims As Long 
 
 
 //自定义函数：获取数组维数。 
 // NumberOfArrayDimensions将返回0 
 //如果数组未分配。 
 
 NumDims = NumberOfArrayDimensions（Arr：= Arr）
选择案例NumDims 
案例0 
 //未分配的数组
退出子
案例1 
 //单维数组
对于N = LBound（Arr）到UBound（Arr）
用rg（N，1）.Interior 
 .ColorIndex = Arr（N）
 .PatternColorIndex = xlAutomatic 
 .PatternColor = xlSolid 
结束
下一个N 
案例2 
 //二维数组
对于Ndx1 = LBound （Arr，1）对于UBound（Arr，1）
对于Ndx2 = LBound（Arr，2）到UBound（Arr，2）
用rg（Ndx1，Ndx2）.Interior 
。 ColorIndex = Arr（Ndx1，Ndx2）
 .PatternColorIndex = xlAutomatic 
 .PatternColor = xlSolid 
 End with 
 Next Ndx2 
下一个Ndx1 
 Case Else 
 //太多的维 - 不要
结束选择
 End Sub 
 
公共函数NumberOfArrayDimensions（ Arr As Variant）As Integer 
 // NumberOfArrayDimensions 
 //此函数返回数组的维数。未分配的动态数组
 //具有0个维度。此条件也可以用IsArrayEmpty进行测试。 
 
 Dim Ndx As Integer 
 Dim Res As Integer 
 On Error Resume Next 
 //循环，增加维度索引Ndx，直到发生错误。 
 //当Ndx超出数组中的维数
 //时，会发生错误。返回Ndx  -  1. 
 Do 
 Ndx = Ndx + 1 
 Res = UBound（Arr，Ndx）
循环直到Err.Number< 0 
 
 NumberOfArrayDimensions = Ndx  -  1 
结束函数
  
最后测试这个的C＃代码：
  int [] test3 = new int [3]; 
 test3 [0] = 15; 
 test3 [1] = 15; 
 test3 [2] = 48; 
 
 int [，] test4 = new int [2，2]; 
 test4 [0，0] = 15; 
 test4 [0，1] = 15; 
 test4 [1，0] = 15; 
 test4 [1，1] = 15; 
 
 this.Application.Run（Sheet1.fillInteriorMulti，rg​​，test4，
失踪，缺失，缺失，缺失，缺失，缺失，缺失，缺失，缺失，缺少
失踪，失踪，失踪，失踪，失踪，失踪，失踪，失踪，缺失，缺失，
缺失，缺失，缺失，缺失，缺失，缺失，缺失，缺失） 
  
 
I'm having performance issues with my VSTO solution, I believe the reason is mainly the way the cellColor is set cell by cell.

This depends on data from a recordSet and is thus different everytime. (I can't use a copyFormats from another row/column)

it's similar to filling a Range of values, only for that one there are several methods.

I thought about creating the whole thing in C# in Memory first (a XlColorIndex[,] array) which I pass through to a VBA method similar to the one below:
Sub fillInterior(ByRef rg As Range, a As Variant)
    //a is a double array that represents the colors for the spreadsheet
    Dim r As Long, c As Long
    Dim tmpRg As Range
    r = 1
    c = 1
    For Each Row In a
        For Each colorIdx In Row
            Set tmpRg = rg(r, c)
            With tmpRg.Interior
                .ColorIndex = colorIdx
                .PatternColorIndex = xlAutomatic
                .PatternColor = xlSolid
            End With
            c = c + 1
        Next
        c = 1
        r = r + 1
    Next
End Sub
I've been trying to call this Macro in the following way, but haven't been successful yet, any pointers are greatly appreciated:
            Excel.Range rg = this.Range[this.Cells[5, 3], this.Cells[6, 4]];

            object[,] test2 = new object[2, 2];
            test2[0, 0] = 15;
            test2[0, 1] = 15;
            test2[1, 0] = 15;
            test2[1, 1] = 15;

            this.Application.Run("Sheet1.fillInterior",  rg,  test2,
                System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
                System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
                System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
                System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
I've tried int[,] - 

I did get a different error when I tried Nullable int or Double: 
double?[,] (Array of Nullable Double):
The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))
If I don't try nullable types I got the following HRESULT error (Type missmatch?)
Exception from HRESULT: 0x800A000D

解决方案
Okay, I've should have read this better: Variants should be avoided, so if I have the choice of writing my VBA, I better do it without a variant but with a proper Array.

Secondly, I was using VBA arrays wrong, I should do the following for multi-dimensional arrays (source):

My VBA code now looks like this:
Sub fillInteriorMulti(rg As Range, Arr() As Long)
    Dim N As Long, Ndx1 As Long, Ndx2 As Long
    Dim icol As Long
    Dim irow As Long
    Dim NumDims As Long


    // Custom Function: Get the number of array dimensions.
    // NumberOfArrayDimensions will return 0
    // if the array is not allocated.

    NumDims = NumberOfArrayDimensions(Arr:=Arr)
    Select Case NumDims
        Case 0
            // unallocated array
            Exit Sub
        Case 1
            // single dimensional array
            For N = LBound(Arr) To UBound(Arr)
                With rg(N, 1).Interior
                    .ColorIndex = Arr(N)
                    .PatternColorIndex = xlAutomatic
                    .PatternColor = xlSolid
                End With
            Next N
        Case 2
            // 2 dimensional array
            For Ndx1 = LBound(Arr, 1) To UBound(Arr, 1)
                For Ndx2 = LBound(Arr, 2) To UBound(Arr, 2)
                    With rg(Ndx1, Ndx2).Interior
                        .ColorIndex = Arr(Ndx1, Ndx2)
                        .PatternColorIndex = xlAutomatic
                        .PatternColor = xlSolid
                    End With
                Next Ndx2
            Next Ndx1
        Case Else
            // Too many dimensions - Do Nothing
    End Select
End Sub

Public Function NumberOfArrayDimensions(Arr As Variant) As Integer
    // NumberOfArrayDimensions
    // This function returns the number of dimensions of an array. An unallocated dynamic array
    // has 0 dimensions. This condition can also be tested with IsArrayEmpty.

    Dim Ndx As Integer
    Dim Res As Integer
    On Error Resume Next
    // Loop, increasing the dimension index Ndx, until an error occurs.
    // An error will occur when Ndx exceeds the number of dimension
    // in the array. Return Ndx - 1.
    Do
        Ndx = Ndx + 1
        Res = UBound(Arr, Ndx)
    Loop Until Err.Number <> 0

    NumberOfArrayDimensions = Ndx - 1
End Function
Finally the C# Code to test this:
            int[] test3 = new int[3];
            test3[0] = 15;
            test3[1] = 15;
            test3[2] = 48;

            int[,] test4 = new int[2, 2];
            test4[0, 0] = 15;
            test4[0, 1] = 15;
            test4[1, 0] = 15;
            test4[1, 1] = 15;

            this.Application.Run("Sheet1.fillInteriorMulti", rg, test4,
                missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing, missing);


                        
这篇关于如何将数组从C＃（VSTO）项目传递到VBA宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！