Can anyone instruct me on how to code a C# enumerable class such that the "for each" construct in Excel VBA works properly? I tried this out with a test class called People that implements IEnumerable and contains an array of Person objects. The "foreach" construct works fine in C#, but in VBA I am only able to loop the old fashioned way.
This VBA code works just fine:
Dim P As Person
Dim PP As New People
For i = 0 To PP.Count - 1
Set P = PP(i)
Debug.Print P.firstName + " " + P.lastName
Next i
But this fails at run time ("Object doesn't support this property or method"):
For Each P In PP
Debug.Print P.firstName + " " + P.lastName
Next P
Here is the C# code (compiled COM visible in VS 2008 for use with 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 to return array size
// indexer method to enable People[i] construct, or in VBA: People(i)
public Person this[Int32 PersonNo] { get { return _people[PersonNo]; } }
// constructor - hardcode to 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 just to make sure the 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
{
get
{
return Current;
}
}
public Person Current
{
get
{
try
{
return _people[position];
}
catch (IndexOutOfRangeException)
{
throw new InvalidOperationException();
}
}
}
}