I've been using COMclass objects I create in vb.net (2003) from Excel VBA for
a while now, and I like the abilities it allows. Up until now, I haven't
needed to make a COMclass that includes a collection object (that inherits
from System.Collections.CollectionBase), but I just tried that now. When I
used the collection in VBA, I was surprised to see that it didn't show the
collection's Item() property in the Locals window, even though I declared it
as Public (as the default property). I was able to access items in the
collection in the Immediate window (e.g.: MsgBox collection.Item(1).prop1, or
collection(1).Prop1). However, when I tried to use the For..Each
functionality, I got a message saying the object doesn't support this
property or method. Is there something else I need to do when creating
collections in vb.net to allow them to be used properly in VBA?

Thanks,
Jason

how about something like this:

For n = 1 To collection.Item.Count

--


Gary

"Jason" wrote in message
...
I've been using COMclass objects I create in vb.net (2003) from Excel VBA for
a while now, and I like the abilities it allows. Up until now, I haven't
needed to make a COMclass that includes a collection object (that inherits
from System.Collections.CollectionBase), but I just tried that now. When I
used the collection in VBA, I was surprised to see that it didn't show the
collection's Item() property in the Locals window, even though I declared it
as Public (as the default property). I was able to access items in the
collection in the Immediate window (e.g.: MsgBox collection.Item(1).prop1, or
collection(1).Prop1). However, when I tried to use the For..Each
functionality, I got a message saying the object doesn't support this
property or method. Is there something else I need to do when creating
collections in vb.net to allow them to be used properly in VBA?

Thanks,
Jason

I tried For n = 1 To collection.Count

It didn't work, and I noticed that the .Count property didn't show up in the
intellisense window as I typed it. it seems as though not all the properties
that should come with the Inherits System.Collections.CollectionBase part of
the class declaration is being made available through the COM interop.

Jason

"Gary Keramidas" wrote:

how about something like this:

For n = 1 To collection.Item.Count

--


Gary

"Jason" wrote in message
...
I've been using COMclass objects I create in vb.net (2003) from Excel VBA for
a while now, and I like the abilities it allows. Up until now, I haven't
needed to make a COMclass that includes a collection object (that inherits
from System.Collections.CollectionBase), but I just tried that now. When I
used the collection in VBA, I was surprised to see that it didn't show the
collection's Item() property in the Locals window, even though I declared it
as Public (as the default property). I was able to access items in the
collection in the Immediate window (e.g.: MsgBox collection.Item(1).prop1, or
collection(1).Prop1). However, when I tried to use the For..Each
functionality, I got a message saying the object doesn't support this
property or method. Is there something else I need to do when creating
collections in vb.net to allow them to be used properly in VBA?

Thanks,
Jason

Good morning Jason,

You would need to manually expose the Count property from the VB.NET class
that inherits CollectionBase. Here is an example:

====VB.NET class exposed as a COM component====

Imports System.Collections

<ComClass() _
Public Class Employee
Private m_Name As String
Private m_Salary As Decimal

Public Sub New(ByVal theName As String, ByVal curSalary As Decimal)
m_Name = theName
m_Salary = curSalary
End Sub

Public ReadOnly Property Name() As String
Get
Return m_Name
End Get
End Property

Public ReadOnly Property Salary() As Decimal
Get
Return MyClass.m_Salary
End Get
End Property

End Class

<ComClass() _
Public Class Employees
Inherits System.Collections.CollectionBase

Public Sub Add(ByVal aEmployee As Employee)
List.Add(aEmployee)
End Sub

Public Sub Remove(ByVal index As Integer)
If index Count - 1 Or index < 0 Then
Console.WriteLine("Can't add this item")
Else
List.RemoveAt(index)
End If
End Sub

Public Shadows ReadOnly Property Count() As Integer
Get
Return MyBase.Count
End Get
End Property

Default Public ReadOnly Property Item(ByVal index As Integer) As
Employee
Get
Return CType(List.Item(index), Employee)
End Get
End Property

End Class

<ComClass() _
Public Class SimpleClass

Public Function GetCollection()
Dim coll As New Employees
coll.Add(New Employee("Mike", 10))
coll.Add(New Employee("Mary", 20))
Return coll
End Function
End Class

=====VBA code that consumes the component======
1. add reference to the COM component
2. run the code:
Sub test()
Dim obj As New TestCOMClass.SimpleClass

Dim coll As Employees
Set coll = obj.GetCollection
MsgBox coll.Count
For n = 0 To coll.Count - 1
MsgBox coll.Item(n).Name
Next
End Sub

In this example, I exposed the Count property manually:
Public Shadows ReadOnly Property Count() As Integer
Get
Return MyBase.Count
End Get
End Property

The generated typelib for the GetCollection method is:
interface _SimpleClass : IDispatch {
[id(0x00000001)]
HRESULT GetCollection([out, retval] VARIANT* pRetVal);
};

And the Employees interface looks like:

[
odl,
uuid(60778372-FA23-3B3E-A626-9F6833937782),
version(1.0),
dual,
oleautomation,
custom(0F21F359-AB84-41E8-9A78-36D110E6D2F9,
"TestCOMClass.Employees+_Employees")

]
interface _Employees : IDispatch {
[id(0x00000001)]
HRESULT Add([in] _Employee* aEmployee);
[id(0x00000002)]
HRESULT Remove([in] long index);
[id(0x00000003), propget]
HRESULT Count([out, retval] long* pRetVal);
[id(00000000), propget]
HRESULT Item(
[in] long index,
[out, retval] _Employee** pRetVal);
};

Please let me know whether this example is helpful to you or not.

Best Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/...tance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

That did it. Thanks!

I was also able to Shadow the GetEnumerator method to make the
For..Each..Next looping work.

Thanks for the help,
Jason

""Jialiang Ge [MSFT]"" wrote:

Good morning Jason,

You would need to manually expose the Count property from the VB.NET class
that inherits CollectionBase. Here is an example:

====VB.NET class exposed as a COM component====

Imports System.Collections

<ComClass() _
Public Class Employee
Private m_Name As String
Private m_Salary As Decimal

Public Sub New(ByVal theName As String, ByVal curSalary As Decimal)
m_Name = theName
m_Salary = curSalary
End Sub

Public ReadOnly Property Name() As String
Get
Return m_Name
End Get
End Property

Public ReadOnly Property Salary() As Decimal
Get
Return MyClass.m_Salary
End Get
End Property

End Class

<ComClass() _
Public Class Employees
Inherits System.Collections.CollectionBase

Public Sub Add(ByVal aEmployee As Employee)
List.Add(aEmployee)
End Sub

Public Sub Remove(ByVal index As Integer)
If index Count - 1 Or index < 0 Then
Console.WriteLine("Can't add this item")
Else
List.RemoveAt(index)
End If
End Sub

Public Shadows ReadOnly Property Count() As Integer
Get
Return MyBase.Count
End Get
End Property

Default Public ReadOnly Property Item(ByVal index As Integer) As
Employee
Get
Return CType(List.Item(index), Employee)
End Get
End Property

End Class

<ComClass() _
Public Class SimpleClass

Public Function GetCollection()
Dim coll As New Employees
coll.Add(New Employee("Mike", 10))
coll.Add(New Employee("Mary", 20))
Return coll
End Function
End Class

=====VBA code that consumes the component======
1. add reference to the COM component
2. run the code:
Sub test()
Dim obj As New TestCOMClass.SimpleClass

Dim coll As Employees
Set coll = obj.GetCollection
MsgBox coll.Count
For n = 0 To coll.Count - 1
MsgBox coll.Item(n).Name
Next
End Sub

In this example, I exposed the Count property manually:
Public Shadows ReadOnly Property Count() As Integer
Get
Return MyBase.Count
End Get
End Property

The generated typelib for the GetCollection method is:
interface _SimpleClass : IDispatch {
[id(0x00000001)]
HRESULT GetCollection([out, retval] VARIANT* pRetVal);
};

And the Employees interface looks like:

[
odl,
uuid(60778372-FA23-3B3E-A626-9F6833937782),
version(1.0),
dual,
oleautomation,
custom(0F21F359-AB84-41E8-9A78-36D110E6D2F9,
"TestCOMClass.Employees+_Employees")

]
interface _Employees : IDispatch {
[id(0x00000001)]
HRESULT Add([in] _Employee* aEmployee);
[id(0x00000002)]
HRESULT Remove([in] long index);
[id(0x00000003), propget]
HRESULT Count([out, retval] long* pRetVal);
[id(00000000), propget]
HRESULT Item(
[in] long index,
[out, retval] _Employee** pRetVal);
};

Please let me know whether this example is helpful to you or not.

Best Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/...tance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.