C＃可枚举类-与VBA兼容 [英] c# enumerable class - compatible with VBA

问题描述

任何人都可以指导我如何编写C＃枚举类，以使Excel VBA中的 for each构造正确工作吗？我使用名为People的测试类进行了尝试，该类实现IEnumerable并包含Person对象的数组。 foreach构造在C＃中可以正常工作，但是在VBA中，我只能循环使用老式的方法。

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.

此VBA代码可以正常工作：

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

这里是C＃代码（可见已编译的COM在VS 2008中用于Excel VBA-Office 2010）：

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();
            }
        }
    }
}

推荐答案

尝试将 [DispId（-4）] 添加到您的 GetEnumerator（）方法中。这会将其标记为 DISPID_NEWENUM 成员。为了使VBA与使用For Each的集合一起使用，它需要实现 _ newEnum 。

Try adding [DispId(-4)] to your GetEnumerator() method. This flags it to be the DISPID_NEWENUM member. In order for VBA to work with a collection using For Each, it needs to implement _newEnum via COM.

这可以通过实现枚举器并将其分配给适当的DispId来实现。尽管有其他机制可用。

This can be done by implementing an Enumerator and attributing it with the proper DispId. This is typically done via implementing a custom interface with this specified, though there are other mechanisms available.

这篇关于C＃可枚举类-与VBA兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！