HELP: Only user-defined types defined in public object modules ...

[Takeshi]

Hi,

I'm a VB newbie (background C++/C on Linux) writing a couple of VB
functions in Excel to carry out some preliminary data analysis. I have
written a function prototyped as ff:

Private Sub OutputSpreadData(ByRef Dataset, ByVal SpreadType As
enumSpreadType, ByVal TradeType As Long, ByVal lngOptionType As Long)


Dataset is an array of UDTs which had been forward declared as ff:

Private Type Record
x As Long
y As Long
z As Single
End Type


All the code is in one module file in Excel (Module1), so I don't think
scope or vvariable visibility is an issue. The function was called in
code similar to this:

Public sub foo()
dim Dataset() as Record

..... Some code here
Dataset= fnCreateDataset(arg1, arg2,arg3)
OutputSpreadData Dataset, 1, 1, 1 '/* error here */

end Sub

The error message I get is as follows: "Only user-defined types defined
in public object modules can be coerced to or from a variant or passed
to late-bound functions"


Please help... I've already wasted half a day trying to figure this out.

Many thanks in advance


Takeshi

[Larry Serflaten]

Post by Takeshi
Hi,
I'm a VB newbie (background C++/C on Linux) writing a couple of VB
functions in Excel to carry out some preliminary data analysis. I have

If you don't get an answer here, post your question to the programming
group associated with the product you are using (Excel). VB groups are
for the full product version, you are using a different dialect (VBA).

There are slight differences, as well as object models differences that
are better addressed by those familiar with the product.

LFS

[dnagel]

A parameter of a Public Function must reference a public datatype.

A custom datatype (read as non-intrinsic) can only be public when it
exists in a module which can be AND is a public module (cls or bas).

To get your example to work two changes are necessary.

Change 1:
Private Type Record
becomes
Public Type Record

Change 2:
Public Sub foo()
becomes
Friend Sub foo()

Even though it's available outside of the particular module, using the
Friend
modifier allows the use of the Type becuase a Friend modifier does not
expose the function publicly (outside of the project),

I don't know if this WILL work but it should... I'm not sure if VBS
supports
Friend but IIRC it does.

When creating a standard EXE, VB does not create an exposed COM interface,
and if you know anything about VB, you should know it's a big F'in COM
wrapper.
Theres a whole depth to this topic that I'm certainly not an authority
on but
suffice it to say that they had to hack alot of things to make VB work
as much as it
does, and this specific issue is one of it's shortfalls.

D.

[Takeshi]

Post by dnagel
A parameter of a Public Function must reference a public datatype.
A custom datatype (read as non-intrinsic) can only be public when it
exists in a module which can be AND is a public module (cls or bas).
To get your example to work two changes are necessary.
Private Type Record
becomes
Public Type Record
Public Sub foo() becomes
Friend Sub foo()
Even though it's available outside of the particular module, using the
Friend
modifier allows the use of the Type becuase a Friend modifier does not
expose the function publicly (outside of the project),
I don't know if this WILL work but it should... I'm not sure if VBS
supports
Friend but IIRC it does.
When creating a standard EXE, VB does not create an exposed COM interface,
and if you know anything about VB, you should know it's a big F'in COM
wrapper.
Theres a whole depth to this topic that I'm certainly not an authority
on but
suffice it to say that they had to hack alot of things to make VB work
as much as it
does, and this specific issue is one of it's shortfalls.
D.

Thanks,

But I can't have a friend function in a module (Friends belong to
classes) so your suggestion unfortunately will not (does not) work

[Bob O`Bob]

Post by Takeshi
Hi,
I'm a VB newbie (background C++/C on Linux) writing a couple of VB
functions in Excel to carry out some preliminary data analysis. I have
Private Sub OutputSpreadData(ByRef Dataset, ByVal SpreadType As
enumSpreadType, ByVal TradeType As Long, ByVal lngOptionType As Long)

I note the lack of any "As" clause for your variable named Dataset.

Post by Takeshi
Private Type Record
x As Long
y As Long
z As Single
End Type
All the code is in one module file in Excel (Module1), so I don't think
scope or vvariable visibility is an issue. The function was called in
Public sub foo()
dim Dataset() as Record
..... Some code here
Dataset= fnCreateDataset(arg1, arg2,arg3)
OutputSpreadData Dataset, 1, 1, 1 '/* error here */
end Sub
The error message I get is as follows: "Only user-defined types defined
in public object modules can be coerced to or from a variant or passed
to late-bound functions"
Please help... I've already wasted half a day trying to figure this out.
Many thanks in advance
Takeshi

[Takeshi]

Thanks guys, I managed to fix it.

Soln: Nest the Record UDT array into another UDT and then pass that to
the function. VB funcs does not appear to like being passed arrays of
UDTs, although they are quite capable of handling UDTs (even thought the
UDT may contain arrays of another UDT). Go figure !. Give me C/C++ anyday !

Post by Bob O`Bob

Post by Takeshi
Hi,
I'm a VB newbie (background C++/C on Linux) writing a couple of VB
functions in Excel to carry out some preliminary data analysis. I have
Private Sub OutputSpreadData(ByRef Dataset, ByVal SpreadType As
enumSpreadType, ByVal TradeType As Long, ByVal lngOptionType As Long)

I note the lack of any "As" clause for your variable named Dataset.

Post by Takeshi
Private Type Record
x As Long
y As Long
z As Single
End Type
All the code is in one module file in Excel (Module1), so I don't think
scope or vvariable visibility is an issue. The function was called in
Public sub foo()
dim Dataset() as Record
..... Some code here
Dataset= fnCreateDataset(arg1, arg2,arg3)
OutputSpreadData Dataset, 1, 1, 1 '/* error here */
end Sub
The error message I get is as follows: "Only user-defined types defined
in public object modules can be coerced to or from a variant or passed
to late-bound functions"
Please help... I've already wasted half a day trying to figure this out.
Many thanks in advance
Takeshi

[Pásztor, Zoltán]

Post by Takeshi
Thanks guys, I managed to fix it.
Soln: Nest the Record UDT array into another UDT and then pass that to
the function. VB funcs does not appear to like being passed arrays of
UDTs, although they are quite capable of handling UDTs (even thought
the UDT may contain arrays of another UDT). Go figure !. Give me
C/C++ anyday !

I don't think it is that bad. Try the following change:

Private Sub OutputSpreadData(ByRef Dataset As Record(), _
ByVal SpreadType As enumSpreadType, ByVal TradeType As Long, _
ByVal lngOptionType As Long)


By omitting the type decl. for Dataset you implicitly made it a Variant, and
the restriction indicated in the error message applies to that type
conversion. The CVar() function used for it internally is a global one, and
does not know anything about the private UDT (it cannot be sure about the
result being used only locally).

Regards,