|
Pierpaolo Paparo
Posts: 28
Joined: 2012-05-07
|
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 |
|
|
Eugene Astafiev
Add-in Express team
Posts: 8540
Joined: 2007-07-25
|
|
|
Pierpaolo Paparo
Posts: 28
Joined: 2012-05-07
|
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 |
|
|
Eugene Astafiev
Add-in Express team
Posts: 8540
Joined: 2007-07-25
|
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 Cells and Ranges)..NET, Extended MAPI, Windows Phone & RT |
|
|
Pierpaolo Paparo
Posts: 28
Joined: 2012-05-07
|
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 |
|
|
Eugene Astafiev
Add-in Express team
Posts: 8540
Joined: 2007-07-25
|
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 article 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 OOM.NET: Part 2 - Outlook Item Leaks article. Also you may be interested in the Why Outlook is not closing when I run my add-in? article on our technical blog..NET, Extended MAPI, Windows Phone & RT |
|
Forums
Topics
Search
Help
Login
Register