Excel Library

[Henning Eiben]

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 van Gelder]

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

You can reference the XLA from VBA: Tools | References

 

[Bob Phillips]

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

 

[Henning Eiben]

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.

 

[Henning Eiben]

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)?

 

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

 

[Henning Eiben]

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?