Interop classes are not recognized

[ArikNJ]

I created .NET DLL (COM interface). This assembly has a few classes and used for data retrieval. It worked fine before I created a class that has an array of other classes among other properties (class genCrvPoint has a few simple properties like date as-of, price, etc.):

ComVisible(True)> <COMCLASS()>PublicClass crv

Private descr_ AsString
Private pts_() As genCrvPoint

PublicProperty pts() As genCrvPoint()

Get

Return pts_
​

EndGet
Set(ByVal value As genCrvPoint())

ReDim pts_(value.GetLength(0))
value.CopyTo(pts_, 0)
​

EndSet

​

EndProperty
PublicProperty descr() AsString

Get

Return descr_
​

EndGet
Set(ByVal value AsString)

descr_ = value
​

EndSet
EndProperty
​

​

End Class

​

----------------------------------------------------------------

After compiling and registering I created a following code in VBA
(note that method getCrw returns as BYRef argument an instance of the class crv):

Dim a as Object, ret as object, errmsg as String

Set a = CreateObject("MYlib.dbAccess")
Set ret = CreateObject("MYlib.crv")

If a.getCurrFwd(tck, errmsg, ret) = True Then

Sheet1.Cells(1, 4) = ret.descr

For i = 0 To UBound(ret.pts)

Sheet1.Cells(2, 5 + i) = ret.pts(i).price
.......
​

Next i
​

Else

MsgBox (errmsg)
​

End If
--------------------------------------------------
This code works fine until I try to reference an array of classes ret.pts(). Call to function UBound(ret.pts) returns correct number, but line Sheet1.Cells(2, 5 + i) = ret.pts(i).price produces error 450 "Wrong number of arguments or invalid property assignment".

In a debug mode, if I add object ret into the Watches window -- all properties of class ret are visible and store proper data including array of classes genCrvPoint (with all properties of each array element assigned to the correct data values). This means that data fetch, assignments, and marshalling works just fine.
But if I add ret.pts into the Watches window, a correct size array of elements each of type genCrvPoint is shown, but unfortunately instead of genCrvPoint properties each element is empty (<NO variables> for each array element).

So, I can get the data structures, can see them and actual data in the debug mode, but can't parse and assign them in VBA to variables or use them.

In any other language I would use casting. I tried assignements to variant data types without any success. Your suggestions will be appreciated.

 

[ArikNJ]

Sorry, I forgot to mention that late binding was one of the requirements.

 

[ArikNJ]

I got it. Solution is pretty simple.
Passing arrays as pretty complicated objects are hard enough, so using arrays of classes is pushing Interop too far. I simplyfied the main crv class: replaced one property wich is array of classes genCrvPoint with a few arrays of strings and doubles. In VBA I assign array identifier (like ret.price) to the Variant and after treat this variant as array:
Dim v as Variant
v=ret.px
Sheet1.Cells(2, 5 + i) = v(i).

Not very elegant solution in terms of data storage and structuring, but at least this is working. And I have enough bumps on by head as the result of hitting the wall for two days.
I hope this will help somebody else to use interop services. I have one more question, but it will be a separate thread...