Hi,

I have created an Add-In (XLA) in VBA that exposes a user defined type in
a public module. I want to use that type in an excel-app to store data and
to pass it to functions of the Add-In.

But unfortunatly I can't compile my XLS, I always get an error about that
user-defined types must be in public modules (or something similar; well I
get the message in german I don't know the english word of the message).

What am I missing?

Hi Henning,

But unfortunatly I can't compile my XLS, I always get an error about that
user-defined types must be in public modules (or something similar; well I
get the message in german I don't know the english word of the message).


You must define the type in the project that is calling the addin too.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Am Thu, 21 Oct 2004 09:22:32 +0200 schrieb Jan Karel Pieterse
:

Hi Henning,

But unfortunatly I can't compile my XLS, I always get an error about
that
user-defined types must be in public modules (or something similar;
well I
get the message in german I don't know the english word of the message).


You must define the type in the project that is calling the addin too.


I thought of something like that already ... so I tried this:

--- cut --- cut --- cut --- cut ---
app.XLS:

modPublic includes:

Type Period
Year As Integer,
Month As Integer
End Type

Public Function Foo
Dim myPeriode As Periode
Call lib.Bar(myPeriode)
End Function

--- cut --- cut --- cut --- cut ---
lib.XLA:

modPublic includes:

Type Period
Year As Integer,
Month As Integer
End Type

Public Sub Bar (ByRef SubPeriod As Period)
...
End Sub
--- cut --- cut --- cut --- cut ---

But the "Call lib.Bar(myPeriode)" won't compile :(

Hi Henning,

But the "Call lib.Bar(myPeriode)" won't compile :(


Because there is a typo in this sub:

Public Function Foo
Dim myPeriode As Periode
Call lib.Bar(myPeriode)
End Function

Change Periode to Period.

Have you got "require variable declaration" checked in tools, options
editor of the VBE? If not, I recommend doing so. It will add "Option
Explicit" to the top of any new module (you'll have to add it yourself
to existing ones) and would have given a compile error on the Dim
statement.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Am Thu, 21 Oct 2004 09:48:57 +0200 schrieb Jan Karel Pieterse
:

Hi Henning,

But the "Call lib.Bar(myPeriode)" won't compile :(


Because there is a typo in this sub:

Public Function Foo
Dim myPeriode As Periode
Call lib.Bar(myPeriode)
End Function

Change Periode to Period.

Well ... That was just a typo in the post ... and I do have explicit
declaration turned on.

Hi Henning,

Well ... That was just a typo in the post ... and I do have explicit
declaration turned on.


OK, it was worth the try <smile.
No more time to investigate this one further though.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Assume:

I've an addin who's projectname is "PROXL"
in the addin i've a Public module
(check it does NOT contain :

'disabled: Option Private Module
Public Type udPrinter
Name As String
Port As String
End Type

Public Function GetP() As udPrinter
GetP.Name = "Aaap"
GetP.Port = "LPT1"
End Function



In the caller you must add a reference to
the addin. = ADD a reference to "PROXL" !!!!

Sub foo()
Dim x As PROXL.udPrinter
x = PROXL.GetP
End Sub


works for me.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Jan Karel Pieterse wrote:

Hi Henning,

Well ... That was just a typo in the post ... and I do have explicit
declaration turned on.


OK, it was worth the try <smile.
No more time to investigate this one further though.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Am Thu, 21 Oct 2004 05:35:56 -0700 schrieb keepITcool
:

Assume:

I've an addin who's projectname is "PROXL"
in the addin i've a Public module
(check it does NOT contain :

'disabled: Option Private Module
Public Type udPrinter
Name As String
Port As String
End Type

Public Function GetP() As udPrinter
GetP.Name = "Aaap"
GetP.Port = "LPT1"
End Function



In the caller you must add a reference to
the addin. = ADD a reference to "PROXL" !!!!

I've setup a reference to the XLA, since I can call other methods from the
addin.

Sub foo()
Dim x As PROXL.udPrinter
x = PROXL.GetP
End Sub


works for me.


Could it be, that passing the type "ByRef" causes the problem? Does Excel
try to convert the type to variant when calling the XLA? But I need the
ByRef, since I need this parameter as a return-value of the methode.

nope..
it MUST be byref alese you get compile error:
UD Type may not be passed Byval.

if you want:
zip the "caller".xls and "called".xla
and mail to address below.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Henning Eiben" wrote:

Am Thu, 21 Oct 2004 05:35:56 -0700 schrieb keepITcool
:

Assume:

I've an addin who's projectname is "PROXL"
in the addin i've a Public module
(check it does NOT contain :

'disabled: Option Private Module
Public Type udPrinter
Name As String
Port As String
End Type

Public Function GetP() As udPrinter
GetP.Name = "Aaap"
GetP.Port = "LPT1"
End Function



In the caller you must add a reference to the addin. = ADD a
reference to "PROXL" !!!!

I've setup a reference to the XLA, since I can call other methods from
the addin.

Sub foo()
Dim x As PROXL.udPrinter
x = PROXL.GetP
End Sub


works for me.


Could it be, that passing the type "ByRef" causes the problem? Does
Excel try to convert the type to variant when calling the XLA? But I
need the ByRef, since I need this parameter as a return-value of the
methode.

Am Thu, 21 Oct 2004 08:00:11 -0700 schrieb keepITcool
:

nope..
it MUST be byref alese you get compile error:
UD Type may not be passed Byval.

if you want:
zip the "caller".xls and "called".xla
and mail to address below.

Thanx, but I changed my app from using a type to a class. The class is
being provided by a factory method, since I can't have public classes in
Excel.