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
Posts
