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