CODEWITHSUNDEEP
.netvbac#-4.0excel
Maciej
Maciej

Reputation: 10805

How to create VBA.Collection() object in c#

I need create Interop VBA.Collection object from C# code
I have reference to Interop.VBA in my project

When I'm calling that: 

var col = new VBA.Collection()

In runtime I've got an error saying that dll is not registered...
I found that: http://support.microsoft.com/kb/323737/en-us

It might work but I don't have VB6 compiler on my box.
I wonder you know other workaround (or maybe someone can compile this ActiveX to me?)

Upvotes: 5

Views: 6253

Answers (2)

toddmo
toddmo

Reputation: 22416

I have adapted this for vb.net, and had to fix the key coming to the add, because it's null when missing.

I will edit this post after I test it. I need to make sure it works when VB6 calls a .Net dll, passing it a vba collection as a parameter, and the .Net dll passes another vba collection back as the return value. Man, if it works this will save me so much trouble!

Public Class VBACollection
  Implements VBA._Collection

  Private _items As New Dictionary(Of Object, Object)

  Public Sub Add(ByRef Item As Object, Optional ByRef Key As Object = Nothing, Optional ByRef Before As Object = Nothing, Optional ByRef After As Object = Nothing) Implements VBA._Collection.Add
    ' Ignoring the Before and After params for simplicity
    Key = If(Key, Item)
    _items.Add(Key, Item)
  End Sub

  Public Function Count() As Integer Implements VBA._Collection.Count
    Return _items.Count
  End Function

  Public Function GetEnumerator() As System.Collections.IEnumerator Implements VBA._Collection.GetEnumerator, System.Collections.IEnumerable.GetEnumerator
    Return _items.Values.GetEnumerator()
  End Function

  Public Function Item(ByRef Index As Object) As Object Implements VBA._Collection.Item
    Return _items(Index)
  End Function

  Public Sub Remove(ByRef Index As Object) Implements VBA._Collection.Remove
    _items.Remove(Index)
  End Sub
End Class

EDIT:

No, this does not work with VB6. VB6 says:

"Class does not support Automation or does not support expected interface"

The class it's talking about here is my class that uses VBACollection instead of VBA.Collection. VBACollection is not an identical stand-in for VBA.Collection. I'd like to find out why and try to fake COM out into accepting it.

Upvotes: 0

Foole
Foole

Reputation: 4850

I haven't tried this, but it might work.

Create an import library for VB6's VBA6.dll. Create your own implementation of its _Collection interface. Use this implementation in place of the VBA.Collection class.

class MyCollection : VBA._Collection
{
    private Dictionary<object, object> _items = new Dictionary<object, object>();

    public void Add(ref object Item, [System.Runtime.InteropServices.OptionalAttribute]ref object Key, [System.Runtime.InteropServices.OptionalAttribute]ref object Before, [System.Runtime.InteropServices.OptionalAttribute]ref object After)
    {
        // Ignoring the Before and After params for simplicity
        _items.Add(Key, Item);
    }

    public int Count()
    {
        return _items.Count;
    }

    public System.Collections.IEnumerator GetEnumerator()
    {
        return _items.Values.GetEnumerator();
    }

    public dynamic Item(ref object Index)
    {
        return _items[Index];
    }

    public void Remove(ref object Index)
    {
        _items.Remove(Index);
    }
}

Upvotes: 5

Related Questions