collection issue with custom type variable

[JimmyTheHand]

Hi

See the code below:

[vba] Type MyType
a1 As String
a2 As Variant
End Type

Sub test()
Dim Col As Collection, Itm As MyType
Set Col = New Collection
Col.Add Itm
End Sub[/vba] I want to put custom type variables into a collection, but at the red line I get this horrible error message:

"Only public user defined types defined in public object modules can be used as parameters or return types for public procedures of class modules or as fields of public user defined types"

On my level of knowledge this error message is cryptic. Nevertheless I looked for solutions that I may be able to implement, but they suggest everywhere that I create a dll. Which I don't want to, because I don't know how to do it yet, and right now I think it would be too much fuss to learn it for such a small task.
I hope someone knows an easier solution? Or a workaround?
I would be very grateful for it--

Jimmy

[Bob Phillips]

I know I will be ploughing a lone furrow, but ditch UDTs, ditch collectuions, and build a collection class.

[mikerickson]

Collections do not accept custom data types.
Neither can UDT's be assigned to Variant variables.
You could use an array if you had to, but it would have to be typed as MyType, not Variant, which limits what you can do with it. (Can't return an array of UDTs from a function.)

I agree with XLD, use a Class rather than UDT.

BTW, what is a "dll"?

[JimmyTheHand]

I know I will be ploughing a lone furrow, but ditch UDTs, ditch collectuions, and build a collection class.

Well... umm...All right, Bob...
There's only two things I didn't understand.
#1 - "ploughing a lone furrow"
#2 - collection class

For #1, I have found a "solution" in a reasonably short time, though it's still a bit confusing. According to the idiom dictionary, you think you are in for a long work, alone, with no help from others. My first bet was that you think nobody will share your opinion?
I know it's irrelevant from my original question's point of view, but I'm still curious what your meaning was.

For #2, I guess I just have to find it out for myself. Mike has kindly confirmed this path, and also shed a little light on it by illuminating that "collection class" is not an occult piece of magic artifact, but a type of classes. I've got somewhat familiar with classes these last months.

Collections do not accept custom data types.
Neither can UDT's be assigned to Variant variables.
Can't return an array of UDTs from a function.

Wonder why is that. If memory serves, I had no such problems with Delphi. Anyway, your explanation is much easier to understand than the error message I got.

BTW, what is a "dll"?

"Dynamic link library", I guess. I found such suggestions on forums dealing with VB in general, not Excel and VBA, but thought it still held for my problem.


Bob, Mike, thank you both for putting me on the right path. For the moment I'm going to use an array of UDTs, which is suitable for my purposes, so that I can proceed with my project. In the meantime I will learn about collection classes and replace those arrays when I'm ready.

Jimmy

[Bob Phillips]

BTW, what is a "dll"?

Dynamic linked library. It is a shared library in MS windows terms, where you put the code that many applications use. They are everywhere in windows, is it not the same on the Mac?

[mikerickson]

From my VBE Help system:
dynamic-link library (DLL)
Applies to Windows only. A library of routines loaded and linked into applications at run time.
Note The Macintosh operating system convention is a code fragment or shared library.

It sounds like Mac shares routines differently.
I like to write algorithms, but I'm not very computer savy. I don't know much about this stuff.

[Bob Phillips]

It is very straight-forward. YOu create two classs, one for the object, MType, and one for a collection of MTypes.

The MType class is very simple

[vba]

Option Explicit

Private mA1 As String
Private mA2 As Variant

Public Property Let A1(ByVal Val As String)
mA1 = Val
End Property
Public Property Get A1() As String
A1 = mA1
End Property

Public Property Let A2(ByVal Val As String)
mA2 = Val
End Property
Public Property Get A2() As String
A2 = mA2
End Property

Public Function Name()
Name = mA1 & " " & mA2
End Function
[/vba]

Note that I have created two properties, the eelements of your UDT, and a simple function to process them. An example of using these would be

[vba]

Sub TestClass()
Dim mpType As mType

Set mpType = New mType
mpType.A1 = "Jimmy"
mpType.A2 = "The Hand"
MsgBox mpType.Name

Set mpType = Nothing
End Sub
[/vba]

[Bob Phillips]

And here it gets interesting, when you add the collection class. Unfortunately, you have to hand-crank all of the collection methods, like Add, remove, and enumerate.

I added an id to the MType class first

[vba]

Option Explicit

Private mcA1 As String
Private mcA2 As Variant
Private mcId As String

Public Property Let Id(ByVal Val As String)
mcId = Val
End Property
Public Property Get Id() As String
Id = mcId
End Property

Public Property Let A1(ByVal Val As String)
mcA1 = Val
End Property
Public Property Get A1() As String
A1 = mcA1
End Property

Public Property Let A2(ByVal Val As String)
mcA2 = Val
End Property
Public Property Get A2() As String
A2 = mcA2
End Property

Public Function Name()
Name = mcA1 & " " & mcA2
End Function
[/vba]

and the collection class, MTYpes, looks like

[vba]

Option Explicit

Private mcTypes As Collection

Function NewEnum() As IUnknown
Set NewEnum = mcTypes.[_NewEnum]
End Function

Public Function Add(ThisType As MType)
If Not ExistsInCollection(mcTypes, ThisType.Id) Then
mcTypes.Add ThisType, ThisType.Id
End If
End Function

Public Property Get Count() As Long
Count = mcTypes.Count
End Property

Public Property Get Items() As Collection
Set Items = mcTypes
End Property

Public Property Get Item(Index As Variant) As MType
Set Item = mcTypes(Index)
End Property

Public Sub Remove(Index As Variant)
mcTypes.Remove Index
End Sub

Private Sub Class_Initialize()
Set mcTypes = New Collection
End Sub

Private Sub Class_Terminate()
Set mcTypes = Nothing
End Sub


'--------------------------------------------------------------------------
Public Function ExistsInCollection(pColl, ByVal pKey As String) As Boolean
'--------------------------------------------------------------------------
On Error GoTo NoSuchKey
If VarType(pColl.Item(pKey)) = vbObject Then
' force an error condition if key does not exist
End If
ExistsInCollection = True
Exit Function

NoSuchKey:
ExistsInCollection = False
End Function
[/vba]

You can then use it as in this example

[vba]

Option Explicit

Public Sub AddToDirectoryClass2()
Dim mpTypes As MTypes
Dim mpType As MType

Set mpTypes = New MTypes

Set mpType = New MType
With mpType

.Id = "JTH"
.A1 = "Jimmy"
.A2 = "The Hand"
mpTypes.Add mpType
End With
Set mpType = Nothing

Set mpType = New MType
With mpType

.Id = "RP"
.A1 = "Bob"
.A2 = "Phillips"
mpTypes.Add mpType
End With
Set mpType = Nothing

Call TypeDetails(mpTypes)

mpTypes.Remove "JTH"

Call TypeDetails(mpTypes)

Set mpTypes = Nothing

End Sub

Public Sub TypeDetails(Optional Types As MTypes)
Dim mpType As MType
Dim mpMsg As String

mpMsg = "Number of Types = " & Types.Count & vbNewLine & vbNewLine & _
"Types.Item(1).id = " & Types.Item(1).Id & vbNewLine & vbNewLine
MsgBox mpMsg, vbInformation, "Class demo"

For Each mpType In Types.Items
mpMsg = mpType.Name
MsgBox mpMsg, vbInformation, "Class demo"
Next mpType

End Sub
[/vba]

As I say, a lot more code to set it up, but tons more flexible when done.

[JimmyTheHand]

As I say, a lot more code to set it up, but tons more flexible when done.

And also a lot of code to prod through. Bob, you gave me reading matter for the next few weeks!!
I'm going to study this, and when I create my first Collection Class, I'll send you a complimentary copy

Thanks ,

Jimmy

PS:
OMG, I'm really overwhelmed by this code...
I'll be back with further questions if I get stuck.

[Bob Phillips]

It's all pretty straight-forward Jimmy, once you get into it. Start with my first post and reconcile that to your MType UDT. Then move on to the second post (that is why I did two postings to differentiate them).

I think we covered this in a previous post of yours, I just didn't give you the code then.

The only cool thing there is the collection enumeration, I haven't seen that published anywhere else, but is a basic requirement in collections IMO, and certainly adds to their flexibility.