How to put user defined datatype into a Dictionary

Question

I'm trying to define my own data type and put it in a Dictionary as a value. VBA complains that it does not accept my data type. Any ideas about how to get this working?

Option Explicit

Public Type Translation
    german As String
    french As String
    italian As String
End Type

Private resource As Object

Public Sub addTranslation(key As String, g As String, f As String, i As String)
    Dim trx As Translation
    trx.german = g
    trx.french = f
    trx.italian = i

    resource.add key, trx  '<== here VBA is complaining
End Sub

Public Sub initResource()
    If resource Is Nothing Then Set resource = CreateObject("scripting.dictionary")
End Sub

This is the error messge:

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

Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound modules. — Doug Glancy, Feb 07 '12 at 15:04
If you change the line to "resource.Add key, trx.french" then it will compile, but I cant' tell what your goal is. Does "bla" = "add"? — Doug Glancy, Feb 07 '12 at 15:05
No, resource.Add key, trx.french would not help, the intention is to have all languages stored by one key. — BetaRide, Feb 07 '12 at 15:10

score 16 · Accepted Answer · edited Feb 07 '12 at 15:29

16

After some more digging i found this answer:

If you want to put a user defined data type into a Dictionary of Collection, you have to define it as class. You can do so by adding a new class module and just adding this code:

Public german As String
Public french As String
Public italian As String

Since I named the class module trans, my resulting code looks like this:

Private resource As Object

Public Sub addTranslation(k As String, g As String, f As String, i As String)
    Dim trx As trans
    Set trx = New trans
    trx.german = g
    trx.french = f
    trx.italian = i

    resource.Add k, trx
End Sub

Public Sub initTranslations()
    If resource Is Nothing Then Set resource = CreateObject("scripting.dictionary")
End Sub

Now I can dynamically add translations.

edited Feb 07 '12 at 15:29

JMax

26,109
12
69
88

answered Feb 07 '12 at 15:24

BetaRide

16,207
29
99
177

1

Thanks. This is so dumb, and applies to returning arrays of a user defined type/class too... – enderland Jan 23 '14 at 22:29
7

I step away from VBA, I swear it off forever. I return, dabbling. I forget myself and wade back in.... until disappointment washes over me anew... Carrying with it is the shame of being fooled a second time. Concise Q&A. – colemand77 Dec 27 '16 at 01:15

How to put user defined datatype into a Dictionary

1 Answers1

Linked