How to Access a List (Of T) based object from COM Client

All

I am writing in VS2005 what, on the face if it, should be a relatively simple DLL, which will allow me to build a list of custom objects when called from within Excel 2007. To do this I have a Column and Columns class. As implied, Columns is a list of Column objects. The relevant classes are defined as follows:

<Guid("1CD713EC-D140-4e8b-92D7-99E098694A85")> _
<InterfaceType(ComInterfaceType.InterfaceIsDual)> _
Public Interface IColumn
    Property Name() As String
End Interface
<Guid("6B146BF8-E905-4ed1-84D5-147B1A510AE3")> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class Column
    Implements IColumn
    Private _name As String
    Public Sub New()
        MyBase.new()
    End Sub
    Public 开发者_C百科Property Name() As String Implements IColumn.Name
        Get
            Return _name
        End Get
        Set(ByVal value As String)
            _name = value
        End Set
    End Property
End Class

<Guid("C2C98A9B-DF7B-4ce1-9F48-EF2B0E63A8E8")> _
<InterfaceType(ComInterfaceType.InterfaceIsDual)> _
Public Interface IColumns
    Sub Add(ByVal col As Column)
End Interface

<Guid("CA76EAF0-1B87-402a-8720-933EDFA0CAE4")> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class Columns
    Inherits List(Of Column)
    Implements IColumns
    Public Sub New()
        MyBase.New()
    End Sub
    Public Overloads Sub Add(ByVal col As Column) Implements IColumns.Add
        col.Name = col.Name.ToUpper
        MyBase.Add(col)
    End Sub
End Class

The following code works as expected when in VS2005:

Dim col As Column
Dim cols As New Columns
col = New Column
col.Name = "DefType"
cols.Add(col)

However when called from within Excel using VBA:

Dim col As PiMdbManager.Column
Dim cols As PiMdbManager.Columns
Dim cnt As Integer
Dim colName As String

head = header.Value

For cnt = 0 To header.Count - 1
    colName = header(1, cnt + 1)
    If colName <> "" Then
        Set col = New PiMdbManager.Column
        col.Name = header(1, cnt + 1)
        cols.Add (col)
    End If
Next cnt

The line 'cols.Add (col)' generates the error 'Object doesn't support this property or method'. The .Add method is visible in VBA via intellisense.

Can someone please explain to me what is happening here and how I might be able to acheive what I am trying to do.

Kind Regards

Paul J.

---

Try removing the parenthesis in your VBA in your cols.add (col) so it looks like this: cols.Add col

or you can use the 'Call' keyword:

Call cols.Add (col)