Pass an array from vba to C # using com-interop

Question

What is the appropriate way to pass an array of custom classes from vba to .net (specifically C #) using com-interop?

Here is my C # code. If I call Method1 from vba, this will lead to an error with "Array or expected user type" or "Function uses automation type not supported in visual base".

public class MyClass { public Method1(UserDefinedClass[] Parameters) { ... } public Method2(Object Parameters) { ... } } 

I read a little about the MarshallAsAttribute class. Could this be the missing element in C # code?

Here is the vba code I'm using:

 Dim udt As New UserDefinedClass Dim myArray() myArray(1) = udt myClass.Method1(myArray) myClass.Method2(myArray) 

Answer 1

IIRC you need to pass arrays by reference.

Try declaring your method as

 public class MyClass { public void Method1([In] ref UserDefinedClass[] Parameters) { ... } ... } 

If you do not want to pollute your class with ref parameters for .NET clients, you can define the ComVisible interface that will be used by COM clients and implement it explicitly:

 [ComVisible(true)] public interface IMyClass { void Method1([In] ref UserDefinedClass[] Parameters) { ... } ... } public class MyClass : IMyClass { void IMyClass.Method1(ref UserDefinedClass[] Parameters) { this.Method1(Parameters); } public Method1(UserDefinedClass[] Parameters) { ... } } 

** In response to the comment ** If you want to expose the collection instead of an array in VBA, you just need to expose an enumerator and any other methods that you want the VBA code to call (for example, "Add", "Delete", "Insert "," Clear ", ...), for example.

 [ComVisible] public interface IUserDefinedClassCollection { IEnumerator GetEnumerator(); int Count { get; }; IUserDefinedClass this[int index] { get; } int Add(IUserDefinedClass item); // etc, other methods like Remove, Clear, ... } 

Then you can use it as usual in VBA:

 Dim objUserDefinedClasses As UserDefinedClassCollection ... objUserDefinedClasses.Add objUserDefinedClass ... For nIndex = 0 To objUserDefinedClasses.Count Next nIndex