VBA Strongly typed collections

An Excel File with the sample code can be found here

In this blog post I will show you how to create a Strongly Type Collection Class wich has both an Item as default property and for wich we can use the For Each Loop.
First we create a very simple Class named Person with just three properties Name, Surname and Date of Birth

Option Explicit

Dim mName As String
Dim mSurname As String
Dim mDateofBirth As Date

Public Property Get Name() As String
  Name = mName
End Property
Public Property Let Name(strName As String)
   mName = strName
End Property
Public Property Get Surname() As String
   Surname = mSurname
End Property
Public Property Let Surname(strSurname As String)
   mSurname = strSurname
End Property
Public Property Get DateOfBirth() As Date
  DateOfBirth = mDateofBirth
End Property
Public Property Let DateOfBirth(dteDateofBirth As Date)
   mDateofBirth = dteDateofBirth
End Property 

Then we can create the Collection Class called People

Option Explicit

'This is going to be a stroingly type Collection

Private mCol As Collection

Private Sub Class_Initialize()
  Set mCol = New Collection
End Sub
Private Sub Class_Terminate()
  Set mCol = Nothing
End Sub
Property Get Item(Index As Variant) As Person
   'Attribute Item.VB_UserMemId = 0
   'This Attribute makes Item the default property
      
   Set Item = mCol.Item(Index)
End Property
Property Get NewEnum() As IUnknown
  'Attribute NewEnum.VB_UserMemId = -4
  'Attribute NewEnum.VB_MemberFlags = "40"
  
  'The first Attribute makes it the Default Enumerator Property
  'The second Attribute makes the Enumerator a hidden property. This does not work with the VBA intellisense
 
  
  'This Routine  Get the Enumerator for the Collection.
  'To get this to work you must add two attributes
  
  
  
  Set NewEnum = mCol.[_NewEnum]
End Property
Public Sub Add(Item As Person, Optional key As Variant)
  Call mCol.Add(Item, key)
End Sub
Public Function Count() As Long
   Count = mCol.Count
End Function
Public Sub Remove(Index As Variant)
   mCol.Remove (Index)
End Sub

There are few Attributes that you cannot see in the VBA IDE. If you export a file from one of your VB6 procedures and view it, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s). The two properties in question will look something like this:

Property Get Item(Index As Variant) As Parameter
       Attribute Item.VB_UserMemId = 0   

       Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown

   Attribute NewEnum.VB_UserMemId = -4  
   Attribute NewEnum.VB_MemberFlags = "40"

  Set NewEnum = Me.mCollection.[_NewEnum]

End Property

Note that the Attribute directive must be just below the functions signatures, otherwise the code will not work.
Now the above all looks "normal" except for the addition of the three "Attribute" Lines.
In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.
In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)
The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to
(1) Make your Class
(2) SAVE
(3) Export the Class
(4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove")
 (5) Manually add the Attribute Lines as shown above
 (6) Re-Import the edited Class

 An easier way, as one of my reader pointed out is
1) To write the Attributes directives directly on the VBA Ide. You will get a syntax error. Ignore it
2)  Export the Class
3) Remove the Class
4) Reinport it again.
 The Attribute will be in the .cls file, but they will not be visible (you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)
As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.) The alternative is to create your class 100% within VB6 and then import it into your VBA Project. Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

This is the Code you can use to test the Class

Option Explicit

Sub prova()

Dim Employee As Person
Dim Director As Person
Dim Team As People
Dim p As Person
Dim i As LongSet Employee = New Person
Employee.DateOfBirth = "10 Jan 1974"
Employee.Name = "Mario"
Employee.Surname = "Rossi"Set Director = New Person
Director.DateOfBirth = "10 Mar 1970"
Director.Name = "Giulia"
Director.Surname = "Verdi"Set Team = New People
Call Team.Add(Employee)
Call Team.Add(Director)

For i = 1 To Team.Count
 Debug.Print Team(i).Name
  
Next i

For Each p In Team
  Debug.Print p.Name
NextEnd Sub

Exposing COM Collection With Events

This is a piece of code that shows you how to expose COM Collection with Events in C#.
here you can find the code for the Person Class.

There is only one problem with this code. If you declase the class with Event you need to handle it, i.e you need to define in VBA the event sub. You can just put some empty code inside it. You can see onother interesting post here at murat

UPDATE: I have added a try cach statment to the code to sort the problem mentioned above.
When you declare an Object WithEvents in VBA, the Event in C# will not be null, so the != will not work.
This is why I have protected the code with a try, catch, statement.

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;




namespace MyCOMEvents01
{
    //  To expose properties and methods to COM, you must declare them on the class 
    //  interface and mark them with a DispId attribute, and implement them in the class. 
    //  The order in which the members are declared in the interface is the 
    //  order used for the COM vtable.
    //  ex:
    //  [DispId(1)]
    //  void Init(string userid , string password);
    //  [DispId(2)]
    //    bool ExecuteSelectCommand(string selCommand);

    //Class Interface
    [Guid("09a22bef-9826-4ea6-8e12-83adbbc0efd1"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface IPerson
    {
        [DispId(1)]
        string Id { get; set; }

        [DispId(2)]
        string Name { get; set; }

        [DispId(3)]
        double Age { get; set; }
    }



    // To expose events from your class, you must declare them on the events 
    // interface and mark them with a DispId attribute. 
    // The class should not implement this interface. 

    //Events Interface
    [Guid("94d63c5e-125e-4f7d-aa0a-0d62dd4dc4fd"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface IPersonEvents
    {
        [DispId(101)]
        void OnAfterNameChange(object sender, string name);

        [DispId(102)]
        void OnBeforeNameChange(object sender, string newName, ref bool cancel);
    }



    //The Class can also implement other interfaces. But only
    //the first one will be exposed to COM.
    //COM Class do not support inheritance beyond interface implementation
    //Class Employees : List<Employee> is not COM compatible

    //Class Implement the Class Interface
    [Guid("0836089b-7099-4c0d-be97-39a009d1a9ba"),
     ComVisible(true),
     ClassInterface(ClassInterfaceType.None),
     ComDefaultInterface(typeof(IPerson)),
     ComSourceInterfaces(typeof(IPersonEvents)),
     ProgId("MyCOMEvents01.Person")]
    public class Person : IPerson
    {
        [ComVisible(false)] //Does not need to be visible to COM
        public delegate void OnAfterNameChangeHandler(object sender, string name);

        [ComVisible(false)] //Does not need to be visible to COM
        public delegate void OnBeforeNameChangeHandler(object sender, string newName, ref bool cancel);

        public event OnAfterNameChangeHandler OnAfterNameChange;
        public event OnBeforeNameChangeHandler OnBeforeNameChange;

        public string Id { get; set; }

        private string _Name;
        public string Name
        {
            get { return _Name; }
            set
            {
                bool cancel = false;

                if (OnBeforeNameChange != null)
                {
                    //if we define a COM object WithEvents in VBA, OnPesonAdd will not be null even if we do not associate any code to it.
                    //So we must protect the code.
                    try { OnBeforeNameChange(this, value.ToString(), ref cancel);}
                    catch (Exception){} //Do Nothing
                }

                if (cancel == false)
                {
                    _Name = value;
                    if (OnAfterNameChange != null)
                    {
                        //if we define a COM object WithEvents in VBA, OnPesonAdd will not be null even if we do not associate any code to it.
                        //So we must protect the code.
                        try { OnAfterNameChange(this, _Name); }
                        catch (Exception){} //Do Nothing
                    }
                }
            }
        }

        public double Age { get; set; }

    }
}

And here the VBA code to test it.

Option Explicit

Dim WithEvents ps As MyCOMEvents01.Persons

Sub Test()

 Dim p1 As MyCOMEvents01.Person
 Dim p2 As MyCOMEvents01.Person
 
 Dim key As Variant
 
 Set p1 = New MyCOMEvents01.Person
 Set p2 = New MyCOMEvents01.Person
 Set ps = New MyCOMEvents01.Persons
 
 p1.ID = 1
 p1.Name = "Mario"
 p2.ID = 2
 p2.Name = "Pluto"

 Call ps.Add(p1.ID, p1)
 Call ps.Add(p2.ID, p2)
 For Each key In ps
   Debug.Print ps(key).Name
 Next
 
 
 
End Sub

Private Sub ps_OnPersonAdd(ByVal sender As Variant)
  Debug.Print "Added"
End Sub

Cross, Circular Reference in VBA

Particular care should be used in VBA when we run into a cross-reference, also called circular-reference.

Let's suppose that we have a collection Knots of Knot objects

Dim col as Knots
Dim n as knot

Set col = new Knots
Set n = new Knot
Set n.Parent = col

Set col = Nothing

If we count the reference to the Knots object untill we reach the Set n.Parent = col line, we can see that the sum to 2.
Both col and n.Parent refers to a Knots object in menory.
The new keyword creates the object in memory, a brand new one. The Set n.Parent = Col make the counter to this reference to increment by 1. VBA keeps a counter of each object reference  and it deallocates the momory used by it only if it reaches 0. Each time we use Set col = Nothing VBA reduce the counter by 1, but it will free the memory only when this counter reaches 0.
So if we just set col = Nothing, we will fail to free the memory from the object. The Reference counter will be 1 instead of 0, so VBA will not free memory for it.
If a collection of knots holds n knot objects, this collection will have n+1 reference and they all need to cleaned up to have the memory free from any leaks.
To work around this problem we must make sure that each knot object set its parent property to nothing when is terminated. We can do this creating Terminate sub. For the Knots class  we need to create a  another Terminate methods that will loop each element of the collection to call the knot Terminate() sub.
Please note that if instead we call Set Knot = Nothing, this will not clear the memory.

'For the knot Class

Public Sub Terminate()

   Set Me.Parent = Nothing

End Sub

'For the knots Class
Public Sub Terminate()

  For Each Knot in Knots

    Call knot.Terminate() 

  Next
  Set mCol = Nothing
End Sub

With the addition of these to Terminate Class events, we make sure that all reference to the Knots class coming from its items are terminate, so we don'have any memory leak. Please note that we need to explicitly call the Terminate() method of the knots class before setting knots = Nothing

Example of a COM Dll developed in VB.NET with the COM template

You can find here the code. It is a VS2008 solution file. Just use the .vb classes if you don't have VS2008 to open up the solution

I have already shown in my previous post how to create COM interop assembly in VB.NET and in C#.

You can have a look here and here. For More more detailed info please look also here
where you will find plenty of details on the how COM dll development and deployment works.

I will try to summarize some important point relevant to the VB.NET developer using the COM template here

1) The COM template automatically ticks for you
    Compile / Register for COM interop
    Application / Assembly Infomation... / Make assembly COM Visible
   
The second option is actually a bed idea, becuase it will register for COM all the types you declare in the assembly. If you have some assembly without the GUID it will create them for you generating a registry bloat.
So each time you add a COM Template, go and untick Make assembly COM-Visible.
Once you have done that you need to add as class attributes. (see the code)

2) If you define a Default Property, this will become a default property for your COM object as well. You can also have indexed properties. The ComClassAttribute will associate to it a DispId(0)

3) if you define a GeEnumerator() function that return a IEnumerator than you will enable the For Each ... Next
    loop in VBA. ComClassAttribute will associate to it a DispId(-4)

Public Function GetEnumerator() As System.Collections.IEnumerator Implements              System.Collections.IEnumerable.GetEnumerator
End Function

4) Also public events are exposed.


The code will show you how to create a Collection with a default property and the For Each ... Next loop enabled and how to expose and event.

Employee Class

Imports System.Runtime.InteropServices

<ComClass(Employee.ClassId, Employee.InterfaceId, Employee.EventsId), _
 ComVisible(True)> _
Public Class Employee

#Region "COM GUIDs"
    ' These  GUIDs provide the COM identity for this class 
    ' and its COM interfaces. If you change them, existing 
    ' clients will no longer be able to access the class.
    Public Const ClassId As String = "dd3ef2f6-261f-477d-af54-10abc39a07d9"
    Public Const InterfaceId As String = "a0680708-b5ca-4679-8e8e-1b012479b8ee"
    Public Const EventsId As String = "d7361527-7a80-4e47-9aff-4e603a26812b"
#End Region

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Public Sub New()
        MyBase.New()
    End Sub

    Private _Name As String
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property
End Class 

Employer Class

Imports System.Runtime.InteropServices

<ComClass(Employer.ClassId, Employer.InterfaceId, Employer.EventsId), _
ComVisible(True)> _
Public Class Employer

#Region "COM GUIDs"
    ' These  GUIDs provide the COM identity for this class 
    ' and its COM interfaces. If you change them, existing 
    ' clients will no longer be able to access the class.
    Public Const ClassId As String = "a0513ce8-fac4-4187-8190-0584f59cda1e"
    Public Const InterfaceId As String = "2c55f846-2dc9-4f0f-9b82-5e16dfefee52"
    Public Const EventsId As String = "2f99d8e4-afe8-46ef-a4e0-d62b4db18a4d"
#End Region

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Public Sub New()
        MyBase.New()
    End Sub

    Public Event OnNameChange(ByRef newName As String)

    Private _Name As String
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            RaiseEvent OnNameChange(value)
            _Name = value

        End Set
    End Property

End Class

Collection Class

Imports System.Runtime.InteropServices


<ComClass(MyCol.ClassId, MyCol.InterfaceId, MyCol.EventsId), _
ComVisible(True)> _
Public Class MyCol
    Implements IEnumerable

#Region "COM GUIDs"
    ' These  GUIDs provide the COM identity for this class 
    ' and its COM interfaces. If you change them, existing 
    ' clients will no longer be able to access the class.
    Public Const ClassId As String = "994ba5ce-1301-455b-9334-409e28aea0c3"
    Public Const InterfaceId As String = "87280e58-8be8-40f8-8987-d3fac317c6c3"
    Public Const EventsId As String = "11d12ead-4562-4ab1-a04b-5ef7fa9fba4c"
#End Region

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Dim _SortedList As SortedList
    Public Sub New()
        MyBase.New()
        _SortedList = New SortedList
    End Sub

    Default Public Property Item(ByVal key As Object)
        Get
            Return _SortedList(key)
        End Get
        Set(ByVal value)
            _SortedList(key) = value

        End Set
    End Property

    Public ReadOnly Property Count()
        Get
            Return _SortedList.Count
        End Get
    End Property


    Public Sub Remove(ByVal key As Object)
        _SortedList.Remove(key)
    End Sub


    Public Sub Add(ByVal key As Object, ByVal value As Object)
        _SortedList.Add(key, value)
    End Sub


    Public Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
        'Return _SortedList.GetEnumerator()
        Dim keys As ICollection = _SortedList.Keys
        Return CType(keys.GetEnumerator, IEnumerator)
    End Function
End Class

VBA Code to test the class

Option Explicit
Dim WithEvents a As TestCOMVisible01.Employer


Sub prova()
If a Is Nothing Then
   Set a = New TestCOMVisible01.Employer
End If
a.Name = "Gino"

Debug.Print a.Name

Dim emp1 As New TestCOMVisible01.Employee
Dim emp2 As New TestCOMVisible01.Employee
Dim col As New TestCOMVisible01.MyCol

emp1.Name = "mario"
emp2.Name = "pluto"
Call col.Add("1", emp1)
Call col.Add("2", emp2)

Dim key As Variant
For Each key In col
 Debug.Print col(key).Name
 Next




End Sub

Private Sub a_OnNameChange(newName As String)
   newName = "ho cambiato il nome"
End Sub

Developing a COM Class Collection using VB.NET COM Template

You can find the code here

In this post I will show you how to develop a COM Class Collection in VB.NET using the COM Template.
It is actually very easy, much easier that doing it manually. Here you can see the manual procedure.

The VB.NET ComClassAttribute, used by the COM Class template will generate for you automatically all the interface that you need to be exposed to COM.


If you define a Default indexed property, it will make it the default property for the COM Object, i.e. it will associate a DispId(0) to the Default indexed property.

In addition if you define a function

Function GetEnumerator() as System.Collection.IEnumerator
End Function

it will mark it as DispId(-4) to make it usable for the VB6/VBA For Each ... Next loop.

The COM Add-in will also create for you all the necessary GUID.

As you create a COM Class using the template, the template automatically will tick for you
1) Register for COM interop in Project Property/Compile/Register for COM Interop
2) It will make the assembly COM Visible. It will tick Project Property/Application/Assembly Infomatin/Make Assemby COM Visible.

The second part is usually a bad idea, this is because every type you include in the libray will be exported to COM. In case you do not provide some GUID for the type, each time you build the assembly the project will create some new ones for you, thus creating a dll hell.

The best thing you can do is to Untick Make Assembly COM Visible (and do it every time you use the COM template) and add a attribute ComVisible(true) on top of the class.
See an exampe here

Imports System.Collections
Imports System.Runtime.InteropServices



<ComClass(Employees.ClassId, Employees.InterfaceId, Employees.EventsId)> _
Public Class Employees
    Implements System.Collections.IEnumerable

#Region "COM GUIDs"
    ' These  GUIDs provide the COM identity for this class 
    ' and its COM interfaces. If you change them, existing 
    ' clients will no longer be able to access the class.
    Public Const ClassId As String = "4999e186-4ea8-4ce1-8da4-12db6f8600e8"
    Public Const InterfaceId As String = "43ecbe2f-714b-4dc9-a76c-85a84320b66d"
    Public Const EventsId As String = "069d7776-4953-44c2-bd17-0ff75cb5748b"#End Region

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Dim _SortedList As SortedList
    Public Sub New()
        MyBase.New()
        _SortedList = New SortedList
    End Sub

    Default Public Property Item(ByVal key As Object)
        Get
            Return _SortedList(key)
        End Get
        Set(ByVal value)
            _SortedList(key) = value

        End Set
    End Property

    Public ReadOnly Property Count()
        Get
            Return _SortedList.Count
        End Get
    End Property


    Public Sub Remove(ByVal key As Object)
        _SortedList.Remove(key)
    End Sub


    Public Sub Add(ByVal key As Object, ByVal value As Object)
        _SortedList.Add(key, value)
    End Sub


    Public Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
        'Return _SortedList.GetEnumerator()
        Dim keys As ICollection = _SortedList.Keys
        Return CType(keys.GetEnumerator, IEnumerator)
    End FunctionEnd Class

Developing a COM Class Collection in VB.NET without using the COM Template

you can find the code here

This is an example of a COM Class Collection written in VB.NET.

You need to start a new project of type library, and set the project property Build -> Register for COM Interop
Do not check: Application, Assembly Information, Make Class COM Visible.
We are using the COMVisible attribute to decide which class is visible for us
The class will have both a default property  and an iterator. The iterator is exposed defining a public function.

Function GetEnumerator() as IEnumerator
End Function


It is also a Good Idea having the class to implement IEnumerable

Function GetEnumerator() as IEnumerator Implements IEnumerable.GetEnumerator

End Function

In order to get the new GUID use can either use the VB.NET COM template of the Tools- Create GUID tool.
In addtion you can also use my C# Com template to start with, and translate the code with a C# to VB.NET tool.
Other wise, you can just use the VB.NET COM tool. It is kind of easy to use and much faster

Imports System.Runtime.InteropServices
Imports System.Collections

'Wee first define the interface of the Collection
<Guid("8beb176f-5357-4bb9-a5c1-38bdd0f7d3df"), _
InterfaceType(ComInterfaceType.InterfaceIsDual), _
ComVisible(True)> _
Public Interface INewEmployees
    Inherits System.Collections.IEnumerable


    <DispId(-4)> Shadows Function GetEnumerator() As IEnumerator 'Iterator
    <DispId(1)> Sub Add(ByVal key As Object, ByVal value As Object)
    <DispId(2)> ReadOnly Property Count()
    <DispId(3)> Sub Remove(ByVal key As Object)
    <DispId(0)> Default Property Item(ByVal key As Object)

End Interface

'We define the event interface
<Guid("e96bda2f-596f-419b-840c-4bd165930c4d"), _
InterfaceType(ComInterfaceType.InterfaceIsIDispatch), _
ComVisible(True)> _
Public Interface INewEmployeesEvents

End Interface



'<ComClass(NewEmployees.ClassId, NewEmployees.InterfaceId, NewEmployees.EventsId)> _
<Guid("67d85fea-43d6-457e-8db1-cc9601bdd9ec"), _
ClassInterface(ClassInterfaceType.None), _
ComSourceInterfaces(GetType(INewEmployeesEvents)), _
ComDefaultInterface(GetType(INewEmployees)), _
ComVisible(True)> _
Public Class NewEmployees
    Implements INewEmployees


#Region "COM GUIDs"
    ' These  GUIDs provide the COM identity for this class 
    ' and its COM interfaces. If you change them, existing 
    ' clients will no longer be able to access the class.
    Public Const ClassId As String = "67d85fea-43d6-457e-8db1-cc9601bdd9ec"
    Public Const InterfaceId As String = "8beb176f-5357-4bb9-a5c1-38bdd0f7d3df"
    Public Const EventsId As String = "e96bda2f-596f-419b-840c-4bd165930c4d"
#End Region

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Dim _SortedList As SortedList
    Public Sub New()
        MyBase.New()
        _SortedList = New SortedList
    End Sub



    Default Public Property Item(ByVal key As Object) Implements INewEmployees.Item
        Get
            Return _SortedList(key)
        End Get
        Set(ByVal value)
            _SortedList(key) = value

        End Set
    End Property

    Public ReadOnly Property Count() Implements INewEmployees.Count
        Get
            Return _SortedList.Count
        End Get
    End Property


    Public Sub Remove(ByVal key As Object) Implements INewEmployees.Remove
        _SortedList.Remove(key)
    End Sub


    Public Sub Add(ByVal key As Object, ByVal value As Object) Implements INewEmployees.Add
        _SortedList.Add(key, value)
    End Sub


    Public Function GetEnumerator() As System.Collections.IEnumerator Implements INewEmployees.GetEnumerator, System.Collections.IEnumerable.GetEnumerator
        'Return _SortedList.GetEnumerator()
        Dim keys As ICollection = _SortedList.Keys
        Return CType(keys.GetEnumerator, IEnumerator)
    End Function


End Class

Example of a COM Class Collection Written in C#

This is an example of a COM Class Collection written in C#.
You can use as starting point my template or use the Tools - Create Guid Tool on VS 2008.
You need to start a new project of type library, and set the project property Build -> Register for COM Interop
Do not check: Application, Assembly Information, Make Class COM Visible.
We are using the COMVisible attribute to decide which class is visible for us
The class will have both a defaul property (the indexers) and an iterator

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Collections;

namespace TestEmployeesCol
{
    //Wee first define the interface of the Collection
    [Guid("21C027E8-CF8C-4166-A63B-25D8E790F040"), InterfaceType(ComInterfaceType.InterfaceIsDual), ComVisible(true)]
    public interface INewEmployees : System.Collections.IEnumerable
    {


        [DispId(-4)]
        new IEnumerator GetEnumerator();
        //Iterator
        [DispId(1)]
        void Add(object key, object value);
        [DispId(2)]
        object Count { get; }
        [DispId(3)]
        void Remove(object key);
        [DispId(0)]
        object this[object key] { get; set; }
    }

    //We define the event interface
    [Guid("5C6B8153-D2D6-4e98-80EF-D13A53CC9CDD"), InterfaceType(ComInterfaceType.InterfaceIsIDispatch), ComVisible(true)]
    public interface INewEmployeesEvents
    {

    }



    //<ComClass(NewEmployees.ClassId, NewEmployees.InterfaceId, NewEmployees.EventsId)> _
    [Guid("1692DD4D-6F3E-4e77-AB50-5401F04306DC"), 
    ClassInterface(ClassInterfaceType.None),
    ComSourceInterfaces(typeof(INewEmployeesEvents)), 
    ComDefaultInterface(typeof(INewEmployees)), 
    ComVisible(true)]
    public class NewEmployees : INewEmployees
    {


        #region "COM GUIDs"
        // These  GUIDs provide the COM identity for this class 
        // and its COM interfaces. If you change them, existing 
        // clients will no longer be able to access the class.
        public const string ClassId = "1692DD4D-6F3E-4e77-AB50-5401F04306DC";
        public const string InterfaceId = "21C027E8-CF8C-4166-A63B-25D8E790F040";
        #endregion
        public const string EventsId = "5C6B8153-D2D6-4e98-80EF-D13A53CC9CDD";

        // A creatable COM class must have a Public Sub New() 
        // with no parameters, otherwise, the class will not be 
        // registered in the COM registry and cannot be created 
        // via CreateObject.
        SortedList _SortedList;
        public NewEmployees()
            : base()
        {
            _SortedList = new SortedList();
        }



        public object this[object key]
        {
            get { return _SortedList[key]; }

            set { _SortedList[key] = value; }
        }

        public object Count
        {
            get { return _SortedList.Count; }
        }


        public void Remove(object key)
        {
            _SortedList.Remove(key);
        }


        public void Add(object key, object value)
        {
            _SortedList.Add(key, value);
        }


        public System.Collections.IEnumerator GetEnumerator()
        {
            ICollection keys = _SortedList.Keys;
            return (IEnumerator)keys.GetEnumerator();
        }


    }



}

How to Create a Strongly typed Collection in Vba

An Excel File with the sample code can be found here

In this blog post I will show you how to create a Strongly Type Collection Class wich has both an Item as default property and for wich we can use the For Each Loop.
First we create a very simple Class named Person with just three properties Name, Surname and Date of Birth

Option Explicit

Dim mName As String
Dim mSurname As String
Dim mDateofBirth As Date

Public Property Get Name() As String
  Name = mName
End Property
Public Property Let Name(strName As String)
   mName = strName
End Property
Public Property Get Surname() As String
   Surname = mSurname
End Property
Public Property Let Surname(strSurname As String)
   mSurname = strSurname
End Property
Public Property Get DateOfBirth() As Date
  DateOfBirth = mDateofBirth
End Property
Public Property Let DateOfBirth(dteDateofBirth As Date)
   mDateofBirth = dteDateofBirth
End Property 

Then we can create the Collection Class called People

Option Explicit

'This is going to be a stroingly type Collection

Private mCol As Collection

Private Sub Class_Initialize()
  Set mCol = New Collection
End Sub
Private Sub Class_Terminate()
  Set mCol = Nothing
End Sub
Property Get Item(Index As Variant) As Person
   'Attribute Item.VB_UserMemId = 0
   'This Attribute makes Item the default property
      
   Set Item = mCol.Item(Index)
End Property
Property Get NewEnum() As IUnknown
  'Attribute NewEnum.VB_UserMemId = -4
  'Attribute NewEnum.VB_MemberFlags = "40"
  
  'The first Attribute makes it the Default Enumerator Property
  'The second Attribute makes the Enumerator a hidden property. This does not work with the VBA intellisense
 
  
  'This Routine  Get the Enumerator for the Collection.
  'To get this to work you must add two attributes
  
  
  
  Set NewEnum = mCol.[_NewEnum]
End Property
Public Sub Add(Item As Person, Optional key As Variant)
  Call mCol.Add(Item, key)
End Sub
Public Function Count() As Long
   Count = mCol.Count
End Function
Public Sub Remove(Index As Variant)
   mCol.Remove (Index)
End Sub

There are few Attributes that you cannot see in the VBA IDE. If you export a file from one of your VB6 procedures and view it, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s). The two properties in question will look something like this:

Property Get Item(Index As Variant) As Parameter
       Attribute Item.VB_UserMemId = 0   

       Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown

   Attribute NewEnum.VB_UserMemId = -4  
   Attribute NewEnum.VB_MemberFlags = "40"

  Set NewEnum = Me.mCollection.[_NewEnum]

End Property

Note that the Attribute directive must be just below the functions signatures, otherwise the code will not work.
Now the above all looks "normal" except for the addition of the three "Attribute" Lines.
In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.
In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)
The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to
(1) Make your Class
(2) SAVE
(3) Export the Class
(4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove")
 (5) Manually add the Attribute Lines as shown above
 (6) Re-Import the edited Class

 An easier way, as one of my reader pointed out is
1) To write the Attributes directives directly on the VBA Ide. You will get a syntax error. Ignore it
2)  Export the Class
3) Remove the Class
4) Reinport it again.
 The Attribute will be in the .cls file, but they will not be visible (you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)
As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.) The alternative is to create your class 100% within VB6 and then import it into your VBA Project. Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

This is the Code you can use to test the Class

Option Explicit

Sub prova()

Dim Employee As Person
Dim Director As Person
Dim Team As People
Dim p As Person
Dim i As LongSet Employee = New Person
Employee.DateOfBirth = "10 Jan 1974"
Employee.Name = "Mario"
Employee.Surname = "Rossi"Set Director = New Person
Director.DateOfBirth = "10 Mar 1970"
Director.Name = "Giulia"
Director.Surname = "Verdi"Set Team = New People
Call Team.Add(Employee)
Call Team.Add(Director)

For i = 1 To Team.Count
 Debug.Print Team(i).Name
  
Next i

For Each p In Team
  Debug.Print p.Name
NextEnd Sub

How to get the scripting dictionary enumerator to use in the for each loop in visual basic

A common practice is writing VB 6.0 or VBA code to wrap the Collection object in order to create strongly type Collections.

An alternative to the collection object is the scripting.Dictionary object which you can find adding a reference to the Microsoft Scripting Runtime.

The Dictionary Object is an Hash Table, so it is preferred to the Collection object when you need to access elements in the collection by key.
In addtion it has few properties and methods that the Collection object is lacking.

How to quick wrap a .NET Collection in a COM component

Here is the code on how to quickly wrap up a .NET Collection and exposed it to COM so that it has a default Item property and a working For Each Loop.

These are the main changes I did to the code compared to my previous post ( see point 9 )

2) The Class NetProva inherits directly form SortedList, while before I used encapsulation and delegation to mimic inheritance. This means that we just need to code the GetEnumerator(). This is because we need this method to return the most generalized interface Collections.IEnumerator. Note the use of the new keyword in the code. We could not ovverride otherwise we would have got a System.Collections.IDictionaryEnumerator return type. Also note that we are return the Enumerator of the Keys becasue COM does not support the DictionaryEntry type.

3) The other method that we might need to implement.ovverride is the indexer. We did not need to do anything here.


TIP
You might be tempted to omit the inheritance relationship when defining a COM
interface because the base methods need to be defined anyway and you don’t have to deal with the
mess of multiply defined members. However, don’t omit the relationship because it’s
still important for proper operation on both .NET and COM sides. Not only does it
provide the expected behavior in .NET clients using such interfaces (such as implicitly
converting an INetProva type to an IEnumerable type, but for COM as well because
a CCW makes QueryInterface calls on the derived interface succeed for any of its
base interfaces.

namespace Collections01
{
    //  To expose properties and methods to COM, you must declare them on the class 
    //  interface and mark them with a DispId attribute, and implement them in the class. 
    //  The order in which the members are declared in the interface is the 
    //  order used for the COM vtable.
    //  ex:
    //  [DispId(1)]
    //  void Init(string userid , string password);
    //  [DispId(2)]
    //    bool ExecuteSelectCommand(string selCommand);

    //Class Interface
    [Guid("2c280db5-99d0-4b5d-a014-13f6a3dfe271"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface INetProva : IEnumerable {
        [DispId(-4)] //Iterator
        new IEnumerator GetEnumerator();
        [DispId(2)]
        void Add(object key, object value);
        [DispId(3)]
        int Count { get; }
        [DispId(4)]
        void Remove(object key);
        [DispId(0)] //Default Property
        object this[object key] { get; set; }
   
    }



    // To expose events from your class, you must declare them on the events 
    // interface and mark them with a DispId attribute. 
    // The class should not implement this interface. 

    //Events Interface
    [Guid("23aaa0da-ba82-48a1-95fb-789e8e061be5"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface INetProvaEvents
    {
    }



    //The Class can also implement other interfaces. But only
    //the first one will be exposed to COM.
    //COM Class do not support inheritance beyond interface implementation
    //Class Employees : List<Employee> is not COM compatible

    //Class Implement the Class Interface
    [Guid("4a37e6a5-2efc-42d5-91db-52787a258d85"),
     ComVisible(true),
     ClassInterface(ClassInterfaceType.None),
     ComDefaultInterface(typeof(INetProva)),
     ComSourceInterfaces(typeof(INetProvaEvents)),
     ProgId("Collections01.NetProva")]
    public class NetProva : SortedList,  INetProva
    {
        public new IEnumerator GetEnumerator()
        {
            ICollection keys = this.Keys;
            return (IEnumerator)keys.GetEnumerator();
         
        }
    }
}

C# COM Exposed Collection

Code can be found here and here 
Visual Studio 2008 and Excel 2003-2008 required
Further details con be found on this post

Hi,
You can find attache a project that contains code that will allow you to create a C# COM exposed collection so that
1) It has Item as default property. So from VB 6.0 you can just do employees(1).Name
2) It is a IEnumerable object
3) It has a GetEnumerator() function that can be used to create VB 6.0 strongly typed collection that can be iterated using the For Each loop

 Public Function NewEnum() As IUnknown
   Set NewEnum = mNetList.GetEnumerator
End Function

Please do not to register the CLASS for COM interop just go to Project/Properties/Build Register for COM Interop.

Do not use the option Project/Properties/Application/Assembly Information/Make assembly COM Visible.
I am using the attribute  ComVisible(true) to decide which class should be visible to COM on individual basis.

How to create custom collection in VBA tricks

This post has been update here


You can find attached here the code that shows you how to create a strongly typed collection in VBA that has both a default Item property and that can be iterated with the For Each Loop.

As you will see from this blog post, some vba attributes are needed to be assigned to specific Collection properties, however these are not visible in the VBA IDE, but you can see them using notepad.




This solution was taken from a forum entry I found on the web
In VBA You can create both a defaul property and a default enumerator, but the process is a bit more manual.
If you export a .cls file from one of your VB6 proceedures and view it in a Notepad, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s).

The two properties in question will look something like this:
Property Get Item(Index As Variant) As Parameter
     Attribute Item.VB_UserMemId = 0
     Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Attribute NewEnum.VB_MemberFlags = "40"
    Set NewEnum = Me.mCollection.[_NewEnum]
End Property


It is important to note that the Attribute directive are just below the Property Signatures.
If they are not there, the code will not work.

Now the above all looks "normal" except for the addition of the three "Attribute" Lines.

In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.

In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)

The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to (1) Make your Class, (2) SAVE, (3) Export the Class, (4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove") and then (5) Manually add the Attribute Lines as shown above, (6) Re-Import the edited Class.

As for one comment in this post another way is
1) Add those attributes in the VBA IDE. You will get  a syntax error. Ignore it
2) Click on the Class and Remove
3) Say Yes when you are asked to Export it
4) Import it again

Pay attention to not delete the class. Otherwise do: Export, Delete, Import.


(Btw, you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)

As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.)

The alternative is to create your class 100% within VB6 and then import it into your VBA Project.

Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

Developing COM exposed classes in C#

Press  here to download the template. CSharp_Com_Class.zip will be downloaded.
For some code example see this  post

Here you can find a series of notes I took as reminders to develpod a COM Class in C#.

I will put it here just as a reference, I hope I will have more time in the future to show you a full example.
You can also find here a C# template you can use to start develop C# COM exposed class. You can delete all the comments out of it. I just add them there for my reference.



To install the template in your VS2008 you need first to find out where they are stored.
To do this, go to File, Export Template. After a few click you should find out where your exported templates
are stored
On my PC for example, they are store here
C:\Users\PP\Documents\Visual Studio 2008\My Exported Templates\WFA01.zip

Once you know this path, just copy the CSharp_Com_Class.zip in the following directory.
You must copy the .zip file. Do not unzip them.

C:\Users\PP\Documents\Visual Studio 2008\Templates\ItemTemplates\Visual C#.

If things go well (and it took me sometime to figure out how to do it) you should have a new template in your
Add New Item, Visual C# Item