Types in Add-In

[Henning Eiben]

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?

 

[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.

Regards,

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

 

[Henning Eiben]

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

Hi Henning,


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

 

[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.

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

 

[Henning Eiben]

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

Hi Henning,


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.

 

[Jan Karel Pieterse]

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

 

[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" !!!!

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 >

 

[Henning Eiben]

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.

 

[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.


keepITcool

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

 

[Henning Eiben]

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.