Compile error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

Question

I'm struggling with a little bit of VBa and Excel. I need to create a structure in VBa, which is a Type. The problem I have is, I get an error message when I try to execute the code! I feel I need to explain how I have arrived where I am in case I've made an error.

I have read that to create a type, it needs to be made public. As such I created a new Class (under Class Modules). In Class1, I wrote

Public Type SpiderKeyPair
    IsComplete As Boolean
    Key As String
End Type

And within ThisWorkbook I have the following

Public Sub Test()    
    Dim skp As SpiderKeyPair
    skp.IsComplete = True
    skp.Key = "abc"    
End Sub

There is no other code. The issue I have is I get the error message

Cannot define a public user-defined type within an object module

If I make the type private I don't get that error, but of course I can't access any of the type's properties (to use .NET terminology).

If I move the code from Class1 into Module1 it works, but, I need to store this into a collection and this is where it's gone wrong and where I am stuck.

I've updated my Test to

Private m_spiderKeys As Collection 

Public Sub Test()                
    Dim sKey As SpiderKeyPair
    sKey.IsComplete = False
    sKey.Key = "abc"            
    m_spiderKeys.Add (sKey)    'FAILS HERE            
End Sub

Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

I have looked into this but I don't understand what it is I need to do... How do I add the SpiderKeyPair to my collection?

Answer 1

Had the exact same problem and wasted a lot of time because the error information is misleading. I miss having List<>.

In Visual Basic you can't really treat everything as an object. You have Structures and Classes which have a difference at memory allocation: https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/structures-and-classes

A Type is a structure (so are Arrays), so you if you want a "List" of them you better use an Array and all that comes with it.

If you want to use a Collection to store a "List", you need to create a Class for the object to be handled.

Not amazing... but it is what the language has available.

Answer 2

You seem to be missing basics of OOP or mistaking VBA and VB.NET. Or I do not understand what are you trying to do. Anyhow, try the following:

In a module write this:

Option Explicit

Public Sub Test()

    Dim skpObj          As SpiderKeyPair
    Dim m_spiderKeys    As New Collection
    Dim lngCounter      As Long

    For lngCounter = 1 To 4
        Set skpObj = New SpiderKeyPair
        skpObj.Key = "test" & lngCounter
        skpObj.IsComplete = CBool(lngCounter Mod 2 = 0)
        m_spiderKeys.Add skpObj
    Next lngCounter

    For Each skpObj In m_spiderKeys
        Debug.Print "-----------------"
        Debug.Print skpObj.IsComplete
        Debug.Print skpObj.Key
        Debug.Print "-----------------"
    Next skpObj

End Sub

In a class, named SpiderKeyPair write this:

Option Explicit

Private m_bIsComplete   As Boolean
Private m_sKey          As String

Public Property Get IsComplete() As Boolean
    IsComplete = m_bIsComplete
End Property

Public Property Get Key() As String
    Key = m_sKey
End Property

Public Property Let Key(ByVal sNewValue As String)
    m_sKey = sNewValue
End Property

Public Property Let IsComplete(ByVal bNewValue As Boolean)
    m_bIsComplete = bNewValue
End Property

When you run the Test Sub in the module you get this:

Falsch
test1
-----------------
-----------------
Wahr
test2

Pay attention to how you initialize new objects. It happens with the word New. Collections are objects and should be initialized as well with New.