Hi,

I need to create dynamicly several Excel-sheets from a database. So far
everything works just fine. I have a Excel-sheet which works as a
user-frontend, where the user can select what files are being generated. I
have different frontends for different users, but they all share
common-code (vba-classes) to create these excel-sheets.

- So one way would be to import the common-code in every excel-sheet, but
I don't like this, because of the maintanence ...
- another solution would be have one front-end (excel-sheet), which
creates all the sheets, instead of having different front-ends, but that
would confuse the user ...
- so I would like create something like a library, where my common-code is
placed, and I reference this library from my front-ends. But how do I do
this with excel? I tried using a XLA, but I can only declare private
classes, so this way I can't create them from my front-end. Are there any
alternatives?

Revisit XLA. It's a very convenient place to store code.

You can reference the XLA from VBA: Tools | References

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Henning Eiben" wrote in message
news:opsfd5uqiie2dvaq@ws2993...
Hi,

I need to create dynamicly several Excel-sheets from a database. So far
everything works just fine. I have a Excel-sheet which works as a
user-frontend, where the user can select what files are being generated. I
have different frontends for different users, but they all share
common-code (vba-classes) to create these excel-sheets.

- So one way would be to import the common-code in every excel-sheet, but
I don't like this, because of the maintanence ...
- another solution would be have one front-end (excel-sheet), which
creates all the sheets, instead of having different front-ends, but that
would confuse the user ...
- so I would like create something like a library, where my common-code is
placed, and I reference this library from my front-ends. But how do I do
this with excel? I tried using a XLA, but I can only declare private
classes, so this way I can't create them from my front-end. Are there any
alternatives?

Rob is right about XLAs, but can you explain the problem that you get a bit
more?

--

HTH

RP

"Rob van Gelder" wrote in message
...
Revisit XLA. It's a very convenient place to store code.

You can reference the XLA from VBA: Tools | References

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Henning Eiben" wrote in message
news:opsfd5uqiie2dvaq@ws2993...
Hi,

I need to create dynamicly several Excel-sheets from a database. So far
everything works just fine. I have a Excel-sheet which works as a
user-frontend, where the user can select what files are being generated.
I
have different frontends for different users, but they all share
common-code (vba-classes) to create these excel-sheets.

- So one way would be to import the common-code in every excel-sheet,
but
I don't like this, because of the maintanence ...
- another solution would be have one front-end (excel-sheet), which
creates all the sheets, instead of having different front-ends, but that
would confuse the user ...
- so I would like create something like a library, where my common-code
is
placed, and I reference this library from my front-ends. But how do I do
this with excel? I tried using a XLA, but I can only declare private
classes, so this way I can't create them from my front-end. Are there
any
alternatives?

Am Tue, 5 Oct 2004 11:13:41 +0100 schrieb Bob Phillips
:

Rob is right about XLAs, but can you explain the problem that you get a
bit
more?


OK, so I create a XLA; I create a class "Class Foo" in VBA, in the
properties-tab I only have to option "instancing = private" or "instancing
= public not createble". So I can't create an instance of my class Foo in
my excel app.



--
Erstellt mit Operas revolutionärem E-Mail-Modul: http://www.opera.com/m2/

Am Tue, 5 Oct 2004 23:02:29 +1300 schrieb Rob van Gelder
:

Revisit XLA. It's a very convenient place to store code.

You can reference the XLA from VBA: Tools | References


If I change the location of my excel-sheet, can excel still access my XLA
even if the path of these two files changed (well, given that the XLA and
XLS are in the same dir)?

You can still use the add-in, but you can create an instance of the class in
the other workbook.

What you could do is to create an instance in the add-in, and use that.
Something like
- create the class in the project and set the instancing to Public Not
Creatable
- in a standard code module in the add-in, create a function like

Public Function CreateObj() As Class1
Set CreateObj = New Class1
End Function

Then, in the other workbook project, use something like

Dim Obj As Object
Set Obj = ProjName.CreateObj()

Here, it the add-in that is actually creating the instance of the class, the
other workbook is only getting a reference to that object.


--

HTH

RP

"Henning Eiben" wrote in message
news:opsfd741zve2dvaq@ws2993...
Am Tue, 5 Oct 2004 11:13:41 +0100 schrieb Bob Phillips
:

Rob is right about XLAs, but can you explain the problem that you get a
bit
more?


OK, so I create a XLA; I create a class "Class Foo" in VBA, in the
properties-tab I only have to option "instancing = private" or "instancing
= public not createble". So I can't create an instance of my class Foo in
my excel app.



--
Erstellt mit Operas revolutionärem E-Mail-Modul: http://www.opera.com/m2/

Am Tue, 5 Oct 2004 12:45:59 +0100 schrieb Bob Phillips
:

You can still use the add-in, but you can create an instance of the
class in
the other workbook.

What you could do is to create an instance in the add-in, and use that.
Something like
- create the class in the project and set the instancing to Public Not
Creatable
- in a standard code module in the add-in, create a function like

Public Function CreateObj() As Class1
Set CreateObj = New Class1
End Function

Then, in the other workbook project, use something like

Dim Obj As Object
Set Obj = ProjName.CreateObj()

Here, it the add-in that is actually creating the instance of the class,
the
other workbook is only getting a reference to that object.

OK, I created a public ObjectFactory in my XLA ... this seems to work,
although I don't have early binding in my XLS anymore :( well ... but I
have some problems with user-defined types. I have a type

Public Type Periode
Jahr As Integer
Monat As Integer
End Type

wich I want to reference from my XLS as well as my XLA, and I want to pass
this type as a parameter to some classes (in the XLA). But when I try to
compile my code I get an error-message something like "public defined
types cannot be casted into variant" ... well, I get the message in german
actually, so I don't know how it would look like in english.

Any suggestions on what this problem might be?