3

Every time I try to pass a UDT around to other functions I get this 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."

The only internal function that I've tested that works with UDTs as arguments is VarPtr. I can't even use TypeName or VarType.

Is there a way to pass UDTs as arguments that are not explicitly expecting the specific type? Kind of like Variant but for UDTs.

I would also like to store UDTs in arrays/dictionaries/collections, but that also seems to be problematic. UDTs sound perfect for my needs (not having to have a separate class module), but it seems like they are very strictly-typed (if that's the correct term for it).

EDIT: It looks like VarPtr expects the argument as the "Any" type, so maybe that's the type that accepts Variants and UDTs. Problem is, the VBA IDE built into Excel doesn't allow me to use that type.

Community
  • 1
  • 1
Hao Zhang
  • 147
  • 9
  • *Not having to have a separate class module*. Did you not read your error message? –  Jan 02 '17 at 19:35
  • All of this is already in a module, not a class module. I want to define everything in one module, not multiple ones. – Hao Zhang Jan 02 '17 at 19:38
  • 1
    By imposing this constraint on yourself, you are opening yourself up to a world of hurt – chris neilsen Jan 02 '17 at 20:41
  • 2
    READ THE ERROR MESSAGE. IT'S SPECIFIC *"Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions."* –  Jan 02 '17 at 21:32
  • What is a public object module then? Is it not a normal module? – Hao Zhang Jan 02 '17 at 22:13
  • It's a class module. So the UDT becomes part of the COM object's object model. Non COM uses for UDT they are passed ByRef, such as Win32 API structures. Your COM object model is built from Public Sub's, Functions, Properties, and Variables in CLASS MODULES. –  Jan 02 '17 at 22:33
  • I see. So it's either using a class module or suffer the limitations of UDTs. – Hao Zhang Jan 02 '17 at 23:42
  • Only for COM. You can use UDT in API calls and within VB without a class module. The class module gets turned into a type library. COM mediates between programs that know little (servers know nothing, clients know what the type library says only) about other programs. So your data for interprocess calls via COM must be publically defined or other programs won't know what it is. Within VB, VB keeps track of things and knows exactly what it is. Just use a tab delimited string. –  Jan 03 '17 at 00:47
  • Wait, hang on. I'm not doing anything COM related here (other than maybe dictionaries). This is all just normal Excel VBA stuff. If I understand correctly, it means there's no way to pass a UDT around in such a way that a Variant can be passed? Is it not possible in Excel VBA to declare an argument of the Any type? – Hao Zhang Jan 03 '17 at 02:21
  • VBA uses COM to interact with Excel. You have no code so it's speculation. The error message says you are using COM. –  Jan 03 '17 at 02:32
  • You can't declare COM as any only API calls, which turns off type checks. –  Jan 03 '17 at 02:35
  • Oh, I see. Thanks for explaining! – Hao Zhang Jan 03 '17 at 02:56

1 Answers1

1

So you can only use UDT within the module they are declared in- so most likely you declared your UDT in one module and the error is being generated when a function in a different module tries to use your UDT...

I think based on your question you are looking for custom classes which are similar to UDTs but they can be used throughout your project and even passed as parameters to and from functions like you are looking for.

Hope this helps- TheSilkCode

TheSilkCode
  • 366
  • 2
  • 11