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