class module in a xla file

[Guest]

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

 

[Chip Pearson]

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

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.

 

[Tom Ogilvy]

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

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.

 

[Guest]

Thanks Chip & Tom,

I have tried your solution and it works perfect

Best regards

Isabelle

 

[Martin SChukrazy]

I have a regular xls file and am writing macros. I want to use data objects
as a persistence layer (which in turn source from a database). For e.g. Lets
say i have a table called accounts. I want to create an account class that
manages the data transfer back and forth, using an Accounts class
ie.
Class Accounts {
private Dim Id As String
private Dim Name As String

.. have properties to get and let

.. have subs (public) to fetch data from recordset (ADODB) and set Data
into a table (ADODB)
}

I get a weird compilation error when i try to do this. Another thing is that
i do not want to share this with other excel files. All i want to do is OOP
within Excel VBA ? Is this possible ? Can some point me to how should i do
this ?

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.

 

[onedaywhen]

Classes are define in class modules, one class per class module. The
name of the class is determined by the name of the class module.

--