What is wrong with this code ?

[ZABADAK]

I get an error message on the .Add LINE ("only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions")

Dim myPriority As typPriority 'In module CustomTypes
Dim colPriority As New Collection
.........
Do While Not rs.EOF
myPriority.Code = rs.Fields("Code").Value
myPriority.Desc = rs.Fields("Desc").Value
colPriority.Add myPriority <==== Error here
Loop

------- definition of type --------
Public Type typPriority
Code As String
Desc As String
End Type

 

[PHV]

Is typPriority defined in a standard code module (not a form/report module) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZABADAK]

Yes

 

[VBslammer]

You can add any kind of thing to a collection - as long as it is an object. This means that you cannot add fundamental data types like integers or strings, nor can you add user-defined types. Instead, you need to add object instances that you have defined in class modules, or that you have obtained a pointer to through OLE.

It would be very simple to convert your udt to a class by using public variables. For example, make a class named "clsPriorities" with your properties:

Option Compare Database
Option Explicit

Public Code As String
Public Desc As String

That's the class. Now try to add it to a collection:

Sub AddClassToCollection()
  Dim i As Integer
  Dim oPriority As clsPriorities
  Dim col As New Collection
  
  For i = 1 To 20
    Set oPriority = New clsPriorities

    oPriority.Code = Format(i, "000")
    oPriority.Desc = "Description " & i

    col.Add oPriority, CStr(i)
  Next i
  
  For i = 1 To col.Count
    Debug.Print col(i).Code, col(i).Desc
  Next i
End Sub

Output:
[tt]
001 Description 1
002 Description 2
003 Description 3
004 Description 4
005 Description 5
006 Description 6
007 Description 7
008 Description 8
009 Description 9
010 Description 10
011 Description 11
012 Description 12
013 Description 13
014 Description 14
015 Description 15
016 Description 16
017 Description 17
018 Description 18
019 Description 19
020 Description 20
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[ZABADAK]

If you look at my code, I'm not adding a user defined type to a collection. I'm adding an *instance* of a type just as I would add an instance of a string or an instance of an integer. It seems there is a fundamental difference in the way in-built types and user-defined types are usable and this is a serious flaw in the language which defeats the purpose of user-defined types.

I'll follow your advice but it makes zero sense to me why I cannot do what I was trying to do originally.

 

[PHV]

Instead of a Collection you may try a Scripting.Dictionary object (needs reference to Microsoft Scripting Runtime)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

Well, considering that Microsoft is about to abandon its support for VB6 altogether and has already made huge improvements to the language in the .NET platform, it's a little late to be arguing about the limitations of the "Beginner's All-Purpose Symbolic Instruction Code" language.

Normally, I use arrays when I need to process udts:

Sub UDTarray()
  Dim myPriorities() As typPriority
  Dim index As Long

  For index = 0 To 19
    ReDim Preserve myPriorities(index)

    myPriorities(index).Code = Format(index + 1, "000")
    myPriorities(index).Desc = "Description " & index + 1
  Next index

  For index = 0 To UBound(myPriorities)
    With myPriorities(index)
      Debug.Print .Code, .Desc
    End With
  Next index
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob