title: ArrayList of COM Object in vba returns n items of <No Variables>
body: <p>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)</p>
  
  <p>I have 3 C# functions that I call from Excel (VSTO project)</p>
  
  <pre><code>    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" };
      }
  </code></pre>
  
  <p>with Fund and IFund defined:</p>
  
  <pre><code>[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;
      }
  }
  </code></pre>
  
  <p>In Excel I have:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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").</p>
  
  <p>The problem is for complexArray. It is an ArrayList with 2 items but each item is "No Variables" (see picture attached)</p>
  
  <p><img src="https://i.stack.imgur.com/QZhcR.jpg" alt="VBA Locals"></p>
  
  <p>Do you know why?</p>
  
  <p>Is there a way to return in C# a list of COM objects and use them in VBA ?</p>
  
  <p>Is it good practice to do things like this or is there a better way to use C# objects in VBA ?</p>
  
  <p>Thanks for your help</p>