10

I just discovered that I can create some .Net classes from VBA using the CreateObject method which can create COM classes. This is pretty cool but the created class is late bound so you don't get any intellisense etc. So what I hoped to do was write VBA wrapper classes and delegate all the method calls to an internal reference to the .Net object.

So this works well for an ArrayList object for everything but trying to reference the enumerator. There is a hack for VBA which allows you to create your own collections and use the For Each ... syntax to enumerate your collections. The following is an example:

Public Property Get NewEnum() As IUnknown Attribute NewEnum.VB_UserMemId = -4 Attribute NewEnum.VB_MemberFlags = "40" Set NewEnum = <<< pass a reference to the enumerator here. End Property

Most implementations hold a reference to a VBA Collection object and pass the enumerator for the Collection object. However since I am holding a .Net ArrayList I'd like to pass that out. However I get "Invalid procedure call or argument" when I try.

My current attempt is this:

Public Function NewEnum() As IUnknown Dim enumerator As Object Set enumerator = internalList.GetEnumerator() <<<< Error occurs here. Set NewEnum = enumerator End Function

I'm pretty sure that its possible to make this work because it is possible to iterate the ArrayList collection directly without the wrapper. E.g.

Public Sub TestCreateArrayList() Dim list As Object Set list = CreateObject("System.Collections.ArrayList") list.Add "an item." list.Add "another item." Dim Item As Variant For Each Item In list Debug.Print Item Next End Sub

I can live without the For Each functionality but it would be nice if I could get it to work especially when it seems like its almost there.

Edit: Its probably worth mentioning that instantiating an ArrayList and then calling GetEnumerator yields the same error even outside of a wrapper class.

Further edit: Note that trying to set a variable of type IUnknown to the result of the GetEnumerator method still gives the same error.

Public Sub TestCreateArrayList() Dim list As Object Set list = CreateObject("System.Collections.ArrayList") Dim iterator As IUnknown Set iterator = list.GetEnumerator() <<<< error occurs here. End Sub

Final Edit: Thanks to @RegEdit's comments below I was able to get this to work and thought I'd add the code:

Private internalList As Object Private Sub Class_Initialize() ' create a .Net Array list for the internal data store. Set internalList = CreateObject("System.Collections.ArrayList") End Sub Private Sub Class_Terminate() If Not internalList Is Nothing Then On Error Resume Next internalList.Dispose Err.Clear End If End Sub Public Function NewEnum() As IUnknown Attribute NewEnum.VB_UserMemId = -4 Dim enumerator As IUnknown Set enumerator = internalList.GetEnumerator(0, internalList.Count) Set NewEnum = enumerator End Function ' ... other wrapped methods elided
Improve this question
7
  • Have you tried to create a derived class from ArrayList with an extra method like this: [DispId(-4)] public IEnumerator GetMyEnumerator() { return GetEnumerator(); }, and get MyEnumerator instead of GetEnumerator? Commented Aug 31, 2014 at 7:29
  • @SimonMourier VBA does not support inheritance. You can't derive from other classes I am afraid. Commented Aug 31, 2014 at 9:25
  • I'm not talking about the VBA side, but the .NET side. Also, add the ComVisible(true) attribute to the new derived class. Commented Aug 31, 2014 at 12:27
  • 1
    You are helping too much. A System.Collections.IEnumerator is automatically mapped to a COM enumerator (IEnumVARIANT). Just expose a method that returns IEnumerator (call GetEnumerator in its implementation) and you're set to For Each. Commented Aug 31, 2014 at 15:11
  • 1
    @HansPassant Yes you are right, the IEnumerable interface has the correct COM setup. However the call to GetEnumerator fails with the above method. See edited code above. Commented Aug 31, 2014 at 21:30

1 Answer 1

Reset to default
11
+100

When you use

 Dim enumerator As Object

you are declaring a COM object. But COM implements IEnumerator as IEnumVARIANT, which inherits IUnknown directly, and COM is not happy trying to set the GetEnumerator return value into an Object. Instead, you can use

Dim enumerator As IEnumVARIANT

EDIT: Note that although ArrayList implements IEnumerable, it overloads GetEnumerator with a method that takes index and count. So you have to use the Nothing keyword in VBA when you want to call the overload with no parameters.

Set iterator = list.GetEnumerator(Nothing, Nothing)

You can see that it's working, because if you include your original lines of code that add two items to the list, and then use a call such as 

Set iterator = list.GetEnumerator(1, 3) ' 3 is out of bounds

you now get a new (expected) error informing you that offset/length are out of bounds.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Cool, I think you're right. However changing the variable type to IEnumVARIANT or IUnknown still fails. Try:Public Sub TestCreateArrayList() Dim list As Object Set list = CreateObject("System.Collections.ArrayList") Dim iterator As IEnumVARIANT Set iterator = list.GetEnumerator() End Sub
@Rossco ah, forgot to mention, that's because ArrayList has an overloaded GetEnumerator. I've updated my answer to explain about this.
@RegEdit you are a legend. That was exactly the problem! I didn't even consider that VBA might try to use the overloaded method. I thought if I supply no arguments that it would try to use the one with no arguments. So I use: Set iterator = list.GetEnumerator(0, internalList.Count)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.