VBA调用C#对象方法，传递double数组参数

 VBA方法可以通过COM Interop来调用C#对象方法。基本方法是公开.NET对象通过COM Interop，然后就可以调用.net 对象方法并传递参数了，但是参数如果包含double数组，VBA将出现如下错误信息
"Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic"

下面将解决这个问题

VBA调用C#对象方法

C#类callableClass必须继承自接口interfaceExposer。接口interfaceExposer的方法被公开可以使用VBA调用。

//interfaceExposer.cs
using System;
 
namespace blah
{
         public interface interfaceExposer
         {
                 int callableMethodSimple(double a);
         }
}

//cssClass.cs
using System;
namespace blah
{
         public class callableClass : interfaceExposer
         {
                 public int callableMethodSimple(double a)
                 {
                          return (int)a;
                 }
         }
}

重点：为项目注册COM Interop。VS 2003中选择“项目属性”－“配置属性”－“生成”设置“为COM Interop注册”为True，再编译。

VBA：选择工具引用选择COM对象。

'VBA code
Public cssObject As New SendArray.callableClass  'SendArray 是项目名称
Dim iClass As interfaceExposer
 
Sub MyRoutine()
         Set iClass = cssObject
         Dim result As Integer
         result = IClass.callableMethodSimple(5.0)
End Sub

VBA传递double数组

//cssClass.cs
using System;
using System.Reflection;  
namespace blah
{
         public class callableClass : interfaceExposer
         {
                 .. .
 
                 public int callableMethodArray(object a)
                 {
                          double[] thisVect = LoadComObjectIntoDoubleArray(a);                 
                          return 0;
                 }
 
                 private double[] LoadComObjectIntoDoubleArray(object comObject)
                 {
                          Type thisType = comObject.GetType();
                          Type dblType = Type.GetType("System.Double[*]");
                          double[] doubleArray = new double[1]; 
                          if(thisType == dblType)
                          {
                                   object[] args = new object[1];
                                   int numEntries = (int)thisType.InvokeMember("Length", BindingFlags.GetProperty, null, comObject, null);
                                   doubleArray = new double[numEntries];
                                   for(int j1=0; j1 < numEntries; j1++)
                                   {
                                            args[0] = j1+1; 
                                            doubleArray[j1] = (double)thisType.InvokeMember("GetValue", BindingFlags.InvokeMethod, null, comObject, args);
                                   }
                          } // End if(thisType == dblType)
                          return doubleArray;
                 } // End LoadComObjectIntoDoubleArray()
         }
}

'VBA code
Public cssObject As New SendArray.callableClass  'SendArray 是项目名称
 
Dim iClass As interfaceExposer
 
Sub MyRoutine()
    Set iClass = cssObject
    Dim result As Integer
    Dim SendArray(1 To 2) As Double
    SendArray(1) = 5.2
    SendArray(2) = 7.5
    result = iClass.callableMethodArray(SendArray)
End Sub