ArrayList of COM Object in vba returns n items of

Question

I try to use C# ArrayList to return a list of COM objects (developed in C#) and use this list of COM object in VBA (Excel 2007, .NET 4)

I have 3 C# functions that I call from Excel (VSTO project)

    public ArrayList GetSimpleArray()
    {
        ArrayList arr = new ArrayList();
        arr.Add(3);
        arr.Add(2);
        return arr;
    }
    public ArrayList GetComplexArray()
    {
        ArrayList arr = new ArrayList();
        arr.Add(new Fund() { Id = "2" } );
        arr.Add(new Fund() { Id = "3" });
        return arr;
    }
    public IFund GetComplexObject()
    {
        return new Fund() { Id = "2" };
    }

with Fund and IFund defined:

[ComVisible(true)]
public interface IFund 
{
    string Id { get; set; }        
    string Name { get; set; }
}

[ComVisible(false)]
public sealed class Fund:IFund
{
    public string Id { get; set; }
    public string Name { get; set; }

    public Fund()
    {
    }

    public Fund(string id, string name)
    {
        this.Id     = id;            
        this.Name   = name;
    }
}

In Excel I have:

Public Sub GetComObject()

  Call InitVSTOAddIn ' Initialise automationObject

  Dim complexObject
  Set complexObject = automationObject.GetComplexObject()

  Dim simpleArray
  Set simpleArray = automationObject.GetSimpleArray()

  Dim complexArray
  Set complexArray = automationObject.GetComplexArray()

End Sub

complexObject and simpleArray have the value I am looking for (complexObject is an object with an Id and a Name, simpleArray is an ArrayList with 2 elements "3" and "2").

The problem is for complexArray. It is an ArrayList with 2 items but each item is "No Variables" (see picture attached)

VBA Locals

Do you know why?

Is there a way to return in C# a list of COM objects and use them in VBA ?

Is it good practice to do things like this or is there a better way to use C# objects in VBA ?

Thanks for your help

Have you tried setting ComVisible to true for the complex ArrayList? — Chris Gessler, Jul 06 '12 at 12:10
GetComplexObject() returns a (comvisible) IFund, GetComplexArray returns not comvisible Fund objects. Why did you make Fund comvisible(false) ? — Eddy, Jul 06 '12 at 12:42
I would prefer to have only the interface IFund as COM visible. It works when I return complexObject. To be sure I tried to make Fund ComVisible but still it doesn not work. — Gutti, Jul 06 '12 at 12:56
Just a small pointer [List is preferable over ArrayList](http://stackoverflow.com/questions/725459/c-sharp-when-should-i-use-list-and-when-should-i-use-arraylist) in most situations. — Amicable, Jul 10 '12 at 10:39

score 0 · Accepted Answer · answered Jul 10 '12 at 10:32

I Found a way to solve this issue by creating a IListFund which contains a property IFund[] ListFund

[ComVisible(true)]
public interface IListFund
{
    public IFund[] ListFund { get; set; }
}

I can then have in excel:

Public Sub GetComObject()

  Call InitVSTOAddIn ' Initialise automationObject '

  Dim complexObject As ComEqd.IListFund
  Dim complexArr() As ComEqd.IFund

  Set complexObject = automationObject.GetComplexObject()
  complexArr = complexArr.ListFund

End Sub

If this works, please marked it as the correct answer so others know. — Scott Conover, Aug 07 '12 at 14:40

ArrayList of COM Object in vba returns n items of

1 Answers1