class module in a xla file
 

Hi to all

I have a xla file. It should share functions which are common to 2 other excel files
But the classes of these 2 files are also the same, so I integrated a class module in my xla file
And now, my 2 files can view the class (I added a reference to this xla file) but impossible to do something with that
Here is my code :
' module which have access to the xla class (named "toto")
Sub test(
Dim X As tot
X.Init ("toto"
End Su

' Function in my class (named "toto") of my xla file
Sub test(
Dim X As tot
X.Init ("toto"
End Su

What's wrong with that, I can't also add a word 'new' to my 'normal' module
Thks a lot for your answer

class module in a xla file
 

Isabelle,

You can't create objects in one workbook that are based on
classes on another workbook or add-in. You can declare objects
of that type, but you can't create an instance of that object
(using the New keyword).

First, open your addin in the VBA editor and go to the Tools menu
and choose VBA Project Properties. In that dialog, give the
project a unique name, e.g., MyProj. Then, select the class
module in that project, press F4 for the Properties dialog, and
change the Instancing property from Private to
PublicNotCreatable. Then, in a standard code module of the
add-in, create a function like

Public Function GetMyClass() As MyClass
Set GetMyClass = New MyClass
End Function

where MyClass is the name of the class module.

Then, open the workbook in the VBA Editor. Go to the Tools menu,
choose References, and put a check next to MyProj, or whatever
you named the add-in project. Then, in a standard module in the
workbook's project, declare a variable of the MyClass and use the
function GetMyClass to initialize it to a new instance of
MyClass. E.g.,

Dim MC As MyProj.MyClass
Set MC = MyProj.GetMyClass()


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"isabelle" wrote in message
...
Hi to all,

I have a xla file. It should share functions which are common
to 2 other excel files.
But the classes of these 2 files are also the same, so I
integrated a class module in my xla file.
And now, my 2 files can view the class (I added a reference to
this xla file) but impossible to do something with that.
Here is my code :
' module which have access to the xla class (named "toto") :
Sub test()
Dim X As toto
X.Init ("toto")
End Sub

' Function in my class (named "toto") of my xla file:
Sub test()
Dim X As toto
X.Init ("toto")
End Sub

What's wrong with that, I can't also add a word 'new' to my
'normal' module.
Thks a lot for your answer

class module in a xla file
 

I believe you would need to have a procedure in your xla that creates and
instance of the class toto and returns a reference to it. You would then
call that procedure from the code in your spreadsheet to access the instance
of the class.

--
Regards,
Tom Ogilvy

"isabelle" wrote in message
...
Hi to all,

I have a xla file. It should share functions which are common to 2 other
excel files.
But the classes of these 2 files are also the same, so I integrated a
class module in my xla file.
And now, my 2 files can view the class (I added a reference to this xla
file) but impossible to do something with that.
Here is my code :
' module which have access to the xla class (named "toto") :
Sub test()
Dim X As toto
X.Init ("toto")
End Sub

' Function in my class (named "toto") of my xla file:
Sub test()
Dim X As toto
X.Init ("toto")
End Sub

What's wrong with that, I can't also add a word 'new' to my 'normal'
module.
Thks a lot for your answer

class module in a xla file - thanks !
 

Thanks Chip & Tom,

I have tried your solution and it works perfect

Best regards

Isabelle