Search This Blog

Monday, October 15, 2012

MZ-Tools for VBA and VB6

MZ-Tools is a terrific tool for anybody serious in getting some code done in Excel VBA and VB6.

Press here to download this free tool MZ-tools

With this tool is much easier to document your code and speed up some repetivie tasks.
The tools I use most often are

1) Insert and Remove line numbers.
    This is very usefull if you want to add the line number at which an error occurs with the Erl functions

2) Add Module Header. This is the the snipped I use

'----------------------------------------------------------------------
' Module    : {MODULE_NAME}
' Author    : {AUTHOR}
' Date      : {DATE}
' Purpose   : 
'----------------------------------------------------------------------
'

Option Explicit

Private Const cMODULE_NAME As String = "{MODULE_NAME}"


3) Add Procedure Header


'---------------------------------------------------------------------------
' Procedure : {PROCEDURE_NAME}
' Author    : {AUTHOR}
' Date      : {DATE}
' Purpose   : 
'---------------------------------------------------------------------------
'
' Inputs    :
'
' Output    :
'
'---------------------------------------------------------------------------
'

4) Error Handler

    Const cPROC_NAME As String = "{PROCEDURE_NAME}()"

On Error GoTo ErrorHandler

    {PROCEDURE_BODY}

Exit {PROCEDURE_TYPE}

ErrorHandler:
    Call ErrorHandler.Log(cMODULE_NAME, cPROC_NAME, Err.description,)


All those code snippets must be added in the option... dialog box of the MZ-Tools add-in.

Havin a module header and a procedure header for each module and procedure that you write will greatly enhance code maintainance.

In addition I have also and Error Handling snippet. This use my ErrorHandler module.
The ErroHandler module is a piece of code I wrote to manage in a coherent way the error raised by an application.







Property Set, Let, Get


In VBA when we define the properties of an object we have a Let, Set and Get operators.
We use the Get operator to retrieve the value of a property
Whe use the Set operator to set the value of an Object
We use the Let operator to set the value of a variable (not an object : string, double, enumeration...)

The use of the Set operator, force you to use the Set function when setting the value of an object property of type object.


Option Explicit

Private Const cMODULE_NAME As String = "House"
Private mAddress As String
Private mDoors As Collection



Public Property Get Address() As String
  
   Address = mAddress
   
End Property

Public Property Let Address(value As String)
   
   mAddress = value
   
End Property

Public Property Get Doors() As Collection
   
   Set Doors = mDoors
  
End Property

Public Property Set Doors(value As Collection)
   Set mDoors = value
End Property


Buttons and spreadsheet duplicaton with Excel VBA

If you want to add Button on an excel spreadsheet you have two choice

1) Active-x buttons
2) Form buttons


I you plan to duplicate a spreadsheet whic contains buttons using VBA Code, than you must use
Form buttons not Active-x ones.
If you use Active-x buttions you might get a VBA run time errors, which now on top of my head I don't rememeber.

So the tip here is
Use Form buttons if you plan to duplicat using VBA code to duplicate excel spread-sheets that contains them.

Using implements behind an Excel worksheet function

Since an excel worksheet is represented by a class module in vba, you might be tempted, as I was, to use the implements keywords behind a worksheet.
This would allow you to use polymorphically an Excel worksheet and could open-up differ possibilities.
Howev this is my advice

DO NOT USE IMPLEMENTS BEHING A WORKSHEET

I have noticed that despites the code compiles, the overall worksheet becomes unstable and tend to crash!
In additon the TypeOf function applied to the worksheet object that use the implements keyword, does not always behaves as you would expect.
Ex:
if you write on top of an excel worksheet module

Implements IEngine


Some time the test TypeOf sht is IEngine will return false even if it is implementing the interface.



Strongly typed dictionary collection in VBA

In this post, I will show you how to build a strongly type dictionary and how to loop through its elements with a For Each Loop.

The Generic Dictionary Object can be found in the "Microsoft Scripting Runtime" library.
This can be used to store any type of variables or object. However, it is usually very useful to wrap it up so that you can create your own strongly typed dictionary.
As you will see from the code below, a very simple way to loop through the collection of elements in a dictionary is to loop through its keys. The variable key must be variant type.
For Each key In emps.Keys
   Set emp = emps(key)
   Debug.Print emp.Name
Next

The first class is a strongly typed dictionary of Employee object, called Employees.
A usual to make the .Item property to be the default property you need to add the

Attribute Item.VB_UserMmeId = 0

just below the definition of the Get Item property

Option Explicit

Private Const cMODULE_NAME As String = "Employees"Private mDic As Dictionary


Private Sub Class_Initialize()
  Set mDic = New Dictionary
End Sub
Private Sub Class_Terminate()
  Set mDic = Nothing
End Sub

Public Sub Add(key As Variant, Item As Employee)
  
    Call mDic.Add(key, Item)
  
End Sub

Public Property Get Item(key As Variant) As Employee
   'Attribute Item.VB_UserMemId = 0
   'This Attribute makes Item the default property
   'In VBA, uncomment the first line. Export, Remove and import the file again. To make it work

   Set Item = mDic.Item(key)
       
End Property

Public Function count() As Long
   count = mDic.count
End Function

Public Function Exists(key As Variant) As Boolean
  Exists = mDic.Exists(key)
End Function

Public Function items() As Variant
 items = mDic.items
End Function

Public Function Remove(key As Variant)
  mDic.Remove (key)
End Function

Public Function RemoveAll()
  mDic.RemoveAll
End Function

Public Function Keys() As Variant
  Keys = mDic.Keys
End Function


This is the the Employee Class


Option Explicit

Private Const cMODULE_NAME As String = "Employee"
Private mIdentifier As Long
Private mName As String
Private mAge As Long

Public Property Get Identifier() As Long
  Identifier = mIdentifier
End Property

Public Property Let Identifier(value As Long)
    mIdentifier = value
End Property

Public Property Get Name() As String
   Name = mName
End Property

Public Property Let Name(value As String)
   mName = value
End Property

Public Property Get Age() As Long
  Age = mAge
End Property

Public Property Let Age(value As Long)
  mAge = value
End Property

This is the Sub to test the Code


Sub TestCollection()

Dim emp As Employee
Dim emps As Employees
Dim key As VariantSet emps = New Employees
Set emp = New Employee
emp.Identifier = 1
emp.Name = "Mario"
emp.Age = 34
Call emps.Add(emp.Identifier, emp)

Set emp = New Employee
emp.Identifier = 2
emp.Name = "Gino"
emp.Age = 12
Call emps.Add(emp.Identifier, emp)

For Each key In emps.Keys
   Set emp = emps(key)
   Debug.Print emp.Name
NextEnd Sub

Environ. A useful function to get environment infos

The Environ function is a pretty useful VBA function that gives you back many important info about the pc.
If you copy and paste this code snippet, you will see in the immediate window the full list of parameter to use and what they return.

One of the most useful are

USERNAME
APPDATA
CommonProgramFiles



Public Sub EnvironParameters()
    
    Dim nCount As Integer
    nCount = 0
    nCount = nCount + 1
    Do Until Environ(nCount) = ""
        Debug.Print Environ(nCount)
        nCount = nCount + 1
    Loop

   
End Sub

This is the complete list of parameters

ALLUSERSPROFILE

APPDATA
CommonProgramFiles
COMPUTERNAME
ComSpec
FP_NO_HOST_CHECK
HOMEDRIVE
HOMEPATH
HOMESHARE
LOGONSERVER
NUMBER_OF_PROCESSORS
OS
Path
PATHEXT
PROCESSOR_ARCHITECTURE
PROCESSOR_IDENTIFIER
PROCESSOR_LEVEL
PROCESSOR_REVISION
ProgramFiles
PSModulePath
SESSIONNAME
SystemDrive
SystemRoot
TEMP
TMP
TNS_ADMIN
UATDATA
USERDNSDOMAIN
USERDOMAIN
USERNAME
USERPROFILE
VS90COMNTOOLS
WecVersionForRosebud.224
windir

Thursday, August 16, 2012

VB6 IsMissing() and optional arguments



In VB6/VBA the IsMissing function is used to test for optional arguments passed to user-defined function procedures as variants. Default values were not required for optional arguments; if no value was passed then IsMissing returned true. It was also possible to specify a default value of a primitive type to pass to the Variant argument.

The important point is that IsMissing only work for optional argurment of Variant Type.
If the optional argument is not of variant type, you should not use the IsMissing() function.
In this case always supply a default value, even if the compiler does not require it.






Monday, August 13, 2012

VBA / VB6 Interface implementation

Few months ago I blogged about interface implementation in VB6. You can find the post here
However, I discovered that the code had a memory leak caused by a circulare reference.
You can find a solution here.

The Keyword Implements in VBA/VB6 allows you to do Interface Implementation, which in turns allows for Polymorphism. This is indeed a great capability of the VB6 language that is often overlooked.
While the VB6 version of interface implementation is not so elegant as the one from .NET environment, it still allows you to implement most of the pattern of the GoF books
I will show you a very easy example on how to use it in VBA.
The Idea is to create an interface called IInstrumet with just one property "Id", and have a Security class that implements its interface. You could also have a Fund, Porftolio or a Security Class that implements this interface. This wil allow for polymorphism.

Dim inst as IInstrument
Dim sec as Security
Dim fn as Fund

Set sec = new Security
Set fn = new Fund

Set inst = sec
Set inst = fn

As you can see, bot a security and a fund can be assigned to an Instruments object!
We first define a Class called IInstrument. The code is here

'This is an Interface for the Generic Financial Instrument

Public Property Get Id() As String
'Only Signature
End Property

Public Property Let Id(value As String)
'Only Signature
End Property


We now create a new Class called Security that Implements the IInstrument one. This is a bit more tricky.
Once we implement an Interface, the Class that implements it in VB6 will declare those method as Private like that.

Private Property Get IInstrument_Id() As String
   IInstrument_Id = mId
End Property

This is a kind of unsual behaviour, because if I know that the Security class implements the IInstrument interface, I expect to have access to the same methods and property made available by the interface obejcts. For this reason, I usually expose as public member the same properties and functions that are available in the interface. When I implement them, I delegate the job to the interface method


3) I implement the interface methods as I normally do. a declare a module level variable mId

Private Property Get IInstrument_Id() As String

    IInstrument_Id = mId  
End Property

Private Property Let IInstrument_Id(value As String)
   mId =value
End Property

4) I crate public properties / methods mirroring the interface delegating their implementation to the
    mInstrument object
Public Property Get Id() As String

   Id =IInstrument_Id
End Property

Public Property Let Id(value As String)
  IInstrument_Id = value
End Property

Here you can find the Security Class Code


Implements IInstrument

Private mId As String
Public Ticker as String



Private Sub Class_Initialize()

End Sub

Private Property Get IInstrument_Id() As String
       IInstrument_Id = mId
End Property


Private Property Let IInstrument_Id(value As String)
  mId = value
End Property


'Public Interface

Public Property Get Id() As String
     Id = IInstrument_Id
End Property

Public Property Let Id(value As String)
   IInstrument_Id = value
End Property

We can now test the code

Sub TestSecurity()
 Dim Sec1 As Security
 Dim Inst As IInstrument
 Dim Sec2 As Security
 Set Sec1 = New Security
 
 Sec1.Id = 10
 Sec1.Ticker = "MXEU"
 
 Set Inst = Sec1 'Upcast: A Security in an Instruments
 Debug.Print Inst.Id
 
 'DownCast, this should have been done explicit, but VBA does not support CType.
 'VB6 does. So instead of CType(Inst, "Security") we can do
 If TypeName(Inst) = "Security" Then
    Set Sec2 = Inst
    End If
 
 
 Set Sec2 = Inst

 Debug.Print Sec2.Id
 Debug.Print Sec2.Ticker
End Sub

Thursday, August 9, 2012

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

Tuesday, July 24, 2012

C# Express Tools Options

Here are some usefull Options to tick in the C# 2010 Options Dialog Box

To Show the Configuration and Platform options in the Project property Debug Window
Projects and Solutions - General - Show Advance build configurations


To redirect output to the immediate window
Debbugging - General - Redirect all output window text to the Immediage window

How to change the Exception Handling Options
Tools - Customize - Commands
Menu bar: Debug
Click where you want to put the new Command, in the controls dialog box
Add Command, Debug, Exceptions...
Ok, Close

Friday, July 20, 2012

Excel Tip of the Day: INDIRECT

The INDIRECT function is a pretty hard function to understand at first glance. However all you need to know is this

1) It converts a string into a cell Reference
2) It does not work with named formula
3) INDIRECT is a volatile function
4) It is often used in conjuction with the function ADDRESS


 1) It converts a string into a cell Reference


 =INDIRECT("A1")

Is equivalent to a foruma =A1

=INDIRECT("TblOrders")

it gives you back a reference to the TblOrders Table. This is Equivalent to a formula =TblOrders
The advantage is that you can form the string using formulas to make dyamically build reference to table objects


2) INDIRECT does not work with named formula

if you have a named formula like   myrange  =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)
and then you use =INDIRECT("myrange") this will not be equivalent to =myrange.

However if you do something like myrange = $A$3:$C$10
and then you type =INDIRECT("myrange") this will work fine and will be equal to =myrange

This means that if you are trying to use in a list validation INDIRECT(C3) where C3="mylist" and
mylist  = OFFSET($A$1,1,0,COUNTA($A:$A)-1,1),
this dynamic validation procedure will fail - INDIRECT is poiting to a named formula

You will instead need to use something like

 C3 = "mylistheader", whe mylistheader is a named cell,
mylistheader = $A$1.
mylistheadeCol = $A:$A

OFFSET(INDIRECT(C3),0,0,COUNTA(INDIRECT(A1&"Col")),1)

INDIRECT(C3) = a referece to $A$1
COUNTA = will count the name in the list

This will work just fine

3) INDIRECT is a volatile function

This mean that Excel recomputes it each time it recalculate the spread sheet. It make the spreadsheet very heavy. Use it sparingly.

4) It is often used in conjuction with the function ADDRESS
    to dynamically build range reference 

Monday, July 16, 2012

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

Exposing COM Events

Hi,
This is a quick sample code that shows you how to expose events to COM.
Just remember to "register the assembly for COM interop" and please do not tick "Make Assembly COM Visible". We use the ComVisible attribute to decise what to make visible for COM interop.

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; }

    }
}

You can find here a quick VBA code to test the classs


Option Explicit

Dim WithEvents p As MyCOMEvents01.Person


Sub test()
 Set p = New MyCOMEvents01.Person
 p.Name = "Mario"
 p.Name = "Ciccio"
End Sub


Private Sub p_OnAfterNameChange(ByVal sender As Variant, ByVal Name As String)
   Debug.Print Name
End Sub

Private Sub p_OnBeforeNameChange(ByVal sender As Variant, ByVal newName As String, cancel As Boolean)
  If newName = "Ciccio" Then
    cancel = True
    Debug.Print "Do not Change Name"
  End If
End Sub

Saturday, June 30, 2012

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

VBA Error Handling

In my previous post I showed the difference between

Break on All Errors
Break in Class module
Break on unhandled errors

After looking at each of them we came to the conclusion that we should really use Break on unhandled errors as our default option. See my post
We this option on, however we need to set up o more sofisticated approach to make our life easier.
I looked into the problem and the best approach I could find is the one described in details in the book,
Professional Excel Developers, Chapter 15, VBA Error Handling.
The Error Handling system described there are two: the functin return value and the re-throw method.
I will not go into the details of the two system in this post, I will just add few comments of mine and present you the main ideas.
To start with, The main Vba keyword to deal with Error handling are

1) The object Err
2) On Error Goto Label
3) On Error Resume Next
4) Resume / Resume Next / Resume Label
5) On Error Goto 0

The Err object is a global object whose property are filled by Vba as an error occurs.
Err.Number, Err.Source, Err.Descripton and Err.Raise are by far the most important ones.

Each time we meet a Exit Sub, Exit Fucntion, Exit Property, End Sub, End Function, End Property, Resume and On Error statement the property of the object error are reset. So some time we want to be carefull and store then into some variables.


An Error Handler is a labeled section of a procedure that will run as an error occur.

The Only way into this part of the code is an error, the only way out of this code should be a resume statement. You can see an example here.

Private Sub MySub()
    On Error Goto ErrorHandler  
   'Some code goes here
  
ExitProc:
  Exit Sub
ErrorHandler:
  'Clean up code goes here
  if CentralErrorHandler("Mymodule","MySub") Then
     Stop
     Resume
  else
     Go to ExitProc
  End if

Exit Sub


The second importan principle is the Single Exi Point. Eache time we write a procedure, we need to make sure that there is a single exit point. In this example is ExitProc.

The call to the CentralErrorHandler funcion happens only when we cannot deal with the error within the code, so an exception must be raised. In my personal implementation I actually changed the name of the function from CentraErrorHandler to Exception.LogMe, or if you want Exception.Inizialize.

The CentralErrorHandler function will be responsible for
1) Log erros to a txt log file
2) Activate or deactivate the Debug mode
3) Show a message to the User is we are at an entry point or in Debug mode
4) Re-raise the error is we are not at an entry point or we are not in Debug mode

The call looks like



Public Function CentralErrorHandler(module,proc,entryPoint,showMessage) as boolean

 module and proc tells the CentralErrorHandler what it the source of the error. In this case MyModule:MySub

entryPoint tells it if we are at an entry point.
showMessage tells it if we need a message displayed.


The CentralErroHandler function looks like

Public Function CentralErrorHandler(module,proc,entrypoint,showmessage) as boolean

  'Store the variable of the Global Error message
   Static errMsg as string


   errNum = Err.Num
   errSource = Err.Source
   errDes = Err.Description
   
   'We cannot allow errorn in the CentralErrorHandler
   On Error Resume Next

   errFullSource = module & ":" & proc 
   errLogTxt = errFullSource & " " &  Err.Num & " " & Err.Des
   
   if len(errMsg) = 0 Then errMsg = Err.Description
 
  'Log the errLogTxt Error into a text file

   if entryPoint OR DebugMode then
      if showMessage Then msgbox(errMsg)
      errMsg = vbNullString  
   else
      On Error Goto 0 
      Err.Raise errNum,  errFullSource, errMsg
      
   end if

End Function


The idea is
1) We store first the property of the Err Object, otherwise they will be reset by the call to Resume
2) We create the new source code and txt to be logged
3) We log the error to the file. We could add: if ToBeLog Then SaveToFile()
4) the errMsg is Static, which means that we will show the original message
5) If we are in DebugMode or at an EntyPoint show a message an reset the string
6) Otherwise re-raise the error

DebugMode is a boolean costant that tells the compiler if we are in DegubMode of not. We can define in at the module level that contains the global error handler.

In few words: when we call the CentralErrorHanlder and we are at an entry point or Debugmode is true a msgbox is shown and the program stops and the errMsg string is cleared. (see the example above on how to call it)
If the debug mode is false and we are not at entry point, a message is re-thrown, with the original Err.Num and Err.Description, but a new Source: MySub:MyModule.

What is an entryPoint?
An entry point is a point from which the user can start execution: menu button, worksheet events...

 For the System to work we need
1) Any Entry point procedure must never call another entry point procedure. If two of them need to run the same code, we can move the code out to a non-entry level procedure
If this happens, we have that the the entry point procedure called, will show a message rather than raising the error up to the caller.

A special case are the Excel User define Functions, which I still need to made my mind up how to treat them.

If we set an Excel UDF as EntryPoint = False and DebugMode = False than an error is re-thrown, so an Excel UDF must have EntryPoint = True

If we set for an Excel UDF EntryPoint = True then as there is an error a msgbox will be diplayed. This is not compliant from what you would expect for an Excel UDF, and image what happens if we had to run hundreds of them.

So we can have Excel UDF EntryPoint = True, showMessage = False.
This gets better, no message anymore.
But if we have 200,000 calls with an error we will log it 200,000 time in the txtfiles, whis is kind of inefficient.
So we could have

ExcelUDF EntryPoint = True, showMessage = False, LogTxt = False

This is getting better, but as you see we have nearly turned off all the Central handling facilities!
No message, no error re-thrown, no txt log. This begs the queston do we need a CentralErrorHandling at all for an Excel UDF?

Lastly if we define an ExcelUDF EntryPoint = true, than we cannot call it from any other part of the code, which is pretty limiting.
So what we can do it to move the code from the entry point to another internal function such that


Public Function MyUDFFunc() as Variant
   MyUDFFunc = MyUDFFuncInternal()
End Function



So that we can set MyUDFFuncInternal EntryPoint = False, in such a case the msgbox will be shown only in Debugmode.    EntryPoint = False, showMessage = True , LogTxt = False

Then MyUDFFunc instead does not need any handler at all.
If we are in DebugMode = True, all the debugging will happen in MyUDFFuncInternal, which will show a message and stop
If we are in DebugMode = False, any error will be logged by MyUDFFuncInternal, whill will no show any message because DebugMode = False, EntryPoint = False. If MyUDFFuncInternal is successfull it will pass the value up to MyUDFFunc. If MyUDFFuncInternal will re-throw an error, MyUDFFunc will just show #N/A, becasue it will exit from execution straightaway.

This could be the best solution: All the Excel UDF don't have any CentralErrorHandler at all, they just delegate the work at some internal fucntion, which wil full support the CentralErrorHandler approach.
This will allow us to reuse them in code easily.
I am still not completely convinced that this is the best approach though.

UPDATE:
After a quick chat with the author of Dailydose of excel, I came to realize that he does not use the Centra Error Handling either for Excel UDF. So my suggested soluiton is the best way I suggest to go.
For Excel UDF no central error Handling. We delegate their functon to some Internal function that implements the CentralErrorHandling Approach. We can set for this functin ErrorLog = False, to prevent to log 100,000 or more calls to failing functions




Thursday, June 28, 2012

VBA and VB6 Debugging Options

In this blog post I will explore the VBA Debugging options.
If you go to
Tools - Options - General

You will see the Error Trapping Options

1) Break on All Errors
2) Break in Class Modlue
3) Bread on Unhandled Errors


Depeding on whether you have an error hanler active or not, or if you call the a class of modue function you will get different behavious. Let's test them out.

1) Break on all errors. 

      Caller is a Sub of Function
          It stops at all errors as soon as they occur: ex division by zero or Err.Raise

      Caller is an Excel UDF.
         The code will NOT STOP. It will just end execution at the point where the error is caused
         Excel will Return #VALUE!


2) Break in Class Module

     Caller is a Sub or Function
          It stops only on Unhandled errors. If it meets an Err.Raise in a Class module it will stop
          in any case.

    Caller is an Excel UDF
          It terminate only on Unhandled errors. If it meets an Err.Raise in a Class modue it will raise
          Err.num 440, irrespective or the error number raised. VERY STRANGE
 
3) Break on Unhandled Errors
      Caller is a Sub or Functin or UDF.
      It stops only on Unhandler errors.


As you can see Opton number 3 is the one that gives the most consistency, followed by option number 1 and
finally option number 2.
I would recommend to use always "Break on Unhandled Errors" and switch to any of the other two options only if you are debugging difficult code.
Option 2 is interesting especially when you are developing an ActiveX component and you want to stop the debugger in the class.

Again, use "Break on Unhandled Errors" and you will save a lot of time trying to put up with the inconsistency between on how the debbuger behaves in case you are using an Excel UDF or just simply a sub or function.







Friday, May 25, 2012

C# Emumeration with custom ToString method



When using Enum in any language you want to have a method to pretty print them. A very easy way to accomplish this in C# is with extension methods.
Here is a quick example

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

namespace RickysGuitars
{
    public enum GuitarType
    {
        ACOUSTIC,
        ELECTRIC
    }

    public static class GuitarTypeExtentions
    {
        public static string ToPrettyString(this GuitarType source)
        {
            switch (source)
            {
                case GuitarType.ACOUSTIC:
                    return "Acoustic Guitar";
                case GuitarType.ELECTRIC:
                    return "Electric Guitar";
            }
            return null;
        }

    }

    
}


An example of the method call would be

GuitarType a = GuitarType.ELECTRIC;
            Debug.WriteLine(a.ToPrettyString());

which prints: Electric Guitar



Monday, May 7, 2012

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