.net - c# enumerable class - compatible with VBA -

can instruct me on how code c# enumerable class such "for each" construct in excel vba works properly? tried out test class called people implements ienumerable , contains array of person objects. "foreach" construct works fine in c#, in vba able loop old fashioned way.

this vba code works fine:

dim p person dim pp new people  = 0 pp.count - 1     set p = pp(i)     debug.print p.firstname + " " + p.lastname next 

but fails @ run time ("object doesn't support property or method"):

for each p in pp     debug.print p.firstname + " " + p.lastname next p 

here c# code (compiled com visible in vs 2008 use excel vba - office 2010):

using system; using system.collections; using system.runtime.interopservices;  public class person {     public person(string fname, string lname)     {         this.firstname = fname;         this.lastname = lname;     }     public string firstname;     public string lastname; }  public class people : ienumerable {     private person[] _people;                           // array of people     public int32 count() { return _people.length; }     // method return array size      // indexer method enable people[i] construct, or in vba: people(i)     public person this[int32 personno] { { return _people[personno]; } }      // constructor - hardcode initialize w 3 people (for testing)     public people()     {         _people = new person[3]         {             new person("john", "smith"),             new person("jim", "johnson"),             new person("sue", "rabon"),         };     }      // test method make sure c# foreach construct works ok     public void test()      {          foreach (person p in this) system.diagnostics.debug.writeline(p.firstname + " " + p.lastname);      }      //implementation of basic getenumerator     ienumerator ienumerable.getenumerator()     {         return (ienumerator)getenumerator();     }      //implementation of people getenumerator     public peopleenum getenumerator()     {         return new peopleenum(_people);     } }  // people enumerator class definition public class peopleenum : ienumerator {     public person[] _people;      int position = -1;      public peopleenum(person[] list)     {         _people = list;     }      public bool movenext()     {         position++;         return (position < _people.length);     }      public void reset()     {         position = -1;     }      object ienumerator.current     {                 {             return current;         }     }      public person current     {                 {             try             {                 return _people[position];             }             catch (indexoutofrangeexception)             {                 throw new invalidoperationexception();             }         }     } } 

try adding [dispid(-4)] getenumerator() method. flags dispid_newenum member. in order vba work collection using each, needs implement _newenum via com.

this can done implementing enumerator , attributing proper dispid. typically done via implementing custom interface specified, though there other mechanisms available.