Code can be found here and here
Visual Studio 2008 and Excel 2003-2008 required
Further details con be found on this post
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
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.