VB.NET COM Collection exposed

Add-in Express™ Support Service
That's what is more important than anything else

VB.NET COM Collection exposed
How to get the For each loop running in VBA 
Pierpaolo Paparo


Guest


Hi,

I manage to create a COM Collection using the COM template in VB.Net, however I cannot get the For Each Loop running fine.
I have defined the GetEnumerator() Function, but I don't know how to get VBA to use it in the For Each Loop.

I have also manually created all the interface (see sample code below), associating a DispId(-4) to the GetEnumerato() function. But it does not work either.


Can Anybody help?

First Code


Imports System.Collections



<ComClass(Employees.ClassId, Employees.InterfaceId, Employees.EventsId)> _
Public Class Employees
#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 IEnumerator
Return _SortedList.GetEnumerator()
End Function



End Class


Second Code
Imports System.Runtime.InteropServices
Imports System.Collections

'Wee first define the interface of the Collection
<Guid("8beb176f-5357-4bb9-a5c1-38bdd0f7d3df"), ComVisible(True)> _
Public Interface INewEmployees
Inherits 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 INetSortedListEvents

End Interface



'<ComClass(NewEmployees.ClassId, NewEmployees.InterfaceId, NewEmployees.EventsId)> _
<Guid("67d85fea-43d6-457e-8db1-cc9601bdd9ec"), _
ClassInterface(ClassInterfaceType.None), _
ComSourceInterfaces(GetType(INetSortedListEvents)), _
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()
End Function

End Class
Posted 07 May, 2012 06:47:21 Top
Eugene Astafiev


Guest


Hi Pierpaolo,

Do you develop a COM add-in or VBA macro?

Anyway, I have found a sample http://msdn.microsoft.com/en-us/library/ff766902.aspx in MSDN for you. Also please note that we do not recommend using the for each loops in VB.NET add-ins. You can read more about this in the http://www.add-in-express.com/creating-addins-blog/2008/10/30/releasing-office-objects-net/ article.
Posted 07 May, 2012 07:04:41 Top
Pierpaolo Paparo


Guest


Hi, I have read the article and it is clear to me that I need to release con object that I create manually.
Hower I did not get what is wrong using the for each loop in the .net addi in code.

Kind of


For each cell in range
Do something
Next


What should I do instead?

Thanks
Posted 08 May, 2012 16:53:24 Top
Eugene Astafiev


Guest


Hi Pierpaolo,

Please note that the Excel Object Model doesn't provide the Cell class. Even the Cells property of the Range class returns an instance of the Range class. Please read more about this in the series of articles in MSDN (see http://msdn.microsoft.com/en-us/library/aa272263%28v=office.11%29.aspx).
Posted 09 May, 2012 07:51:44 Top
Pierpaolo Paparo


Guest


Hi Eugene,
I was trying to understand why you do not advice to use the for each next loop

In my example you can thin Cell as a range object to get it work
Dim cell as Range
for each cell in SomeRange
do semethign
next

Pierpaolo
Posted 11 May, 2012 03:31:37 Top
Eugene Astafiev


Guest


Hi Pierpaolo,

In case of fo reach loop the cell object is not released each time you iterate the loop. Thus, you get a huge set of unreleased objects in the code. The http://www.add-in-express.com/creating-addins-blog/2008/10/30/releasing-office-objects-net/ have the following paragraph:

Q: What is the item leak that I've heard about?
A: This is an Outlook-specific result of non-releasing COM objects. It?Â?Ð?és described at this MSDN blog. Outlook re-uses an item if it is loaded in memory. If you don?Â?Ð?ét release the item, the Outlook user that works with the item in the UI may not be able to save changes. You may also get an item leak, if you don?Â?Ð?ét release the item?Â?Ð?és children: Attachments, UserProperties, Recipients, etc. Another way of provoking item leaks is using a foreach loop. In fact, in such loops you have a number of item leaks at once. Use for loops instead.


You can read more about this in the http://blogs.msdn.com/b/mstehle/archive/2007/12/07/oom-net-part-2-outlook-item-leaks.aspx article. Also you may be interested in the http://www.add-in-express.com/creating-addins-blog/2006/08/17/outlook-not-closing/ article on our technical blog.
Posted 11 May, 2012 05:52:04 Top