I am faced with a problem where I would like to break out commonly used code that has been duplicated in a number of spread sheets and put this common code in a separate macro sheet that is automatically loaded when Excel starts up.

The problem is that it appear that only sub procedures becomes available/visible to other sheets.
My question is: Is it possible to access functions and sub procedures in a auto loaded sheet from within other spread sheets?

I also need to know if it is possible to put generic class modules in a starup macro sheet and still be able to access it from other sheets?
I have commonly used properties class definitions that I would like to create instances of from other sheets - but I do not want to dublicate these class modules in each spread sheet - hence the idea of putting them in a auto loaded macro sheet.

All above tasks would be accessed from within sheet specific macros.

/g
--
Can't wait getting my hands on a real programming language such as Java

If you put your code in an addin then

1. auto_open will run when the add-in loads,
2. functions are available in other worksheets simply using the name of the
function in a cell, or you can use Application.Run to reference macros in
the add-in
3. you can set a reference in other workbooks to the addin which should
allow you to use their functionality, including classes. e.g.

MyAddIn contains class modules

In a separate workbook, open VBE, set a ref to MyAddIn.

In a Sub

Dim clsTest as MyAddIn.clsMyClass
with clsTest
'your code
End with

You can either register the add-in with Excel in a small VB app as part of
an install package, or install the add-in to the XlStart folder, or the
add-ins folder and tell your users to browse for it.

Robin Hammond
www.enhanceddatasystems.com

"ghostWolf" wrote in message
...
I am faced with a problem where I would like to break out commonly used
code that has been duplicated in a number of spread sheets and put this
common code in a separate macro sheet that is automatically loaded when
Excel starts up.

The problem is that it appear that only sub procedures becomes
available/visible to other sheets.
My question is: Is it possible to access functions and sub procedures in a
auto loaded sheet from within other spread sheets?

I also need to know if it is possible to put generic class modules in a
starup macro sheet and still be able to access it from other sheets?
I have commonly used properties class definitions that I would like to
create instances of from other sheets - but I do not want to dublicate these
class modules in each spread sheet - hence the idea of putting them in a
auto loaded macro sheet.

All above tasks would be accessed from within sheet specific macros.

/g
--
Can't wait getting my hands on a real programming language such as Java

"Robin Hammond" wrote ...

set a reference in other workbooks to the addin which should
allow you to use their functionality, including classes.

Robin,
If we are talking about an Excel workbook as an add-in, externally
visible classes will be of instancing type 'Public Not Creatable'.
Such classes can only be instantiated in the add-in, so an appropriate
method is required in the add-in to pass a pointer to a locally
created instance to the other workbooks.

This is an incentive to organizing one's classes as an object model
hierarchy: once the client workbook has a (pointer to) an instance of
the parent class, they can use child collection classes' Add methods
to create child objects, a la Excel e.g.

Application.Workbooks.Add

You only need an instance of the Application class to create
workbooks, worksheets, etc.

Jamie.

--

Exactly how should the MyAddIn instance be declared?
I tried all sort of things without any luck.

Say I have a xla file "COMMON.xla" with a module Log where I among
others have a public function writeLog() that I would like to access
from a different workbook/sheet
The COMMON.xla also have the class module <properties that I would like
to set up a reference to within my workbook/sheet.

How would I do this?

code example:

public MyAddIn ???
public Props As MyAddIn.properties

public sub someFuncion()
Props = new MyAddIn.properties

Props.<some_access_method

MyAddIn.writeLog("some string")

end sub


cheers,
g

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Sorry if this message has been repeated

Exactly how would you go about setting up a reference to MyAddIn?

Lets say you have an addin calles COMMON.xla with the class module
properties and the module Log.
The Log has a public function writeLog(str as String) that you want to
access from any workbook/sheet


Example:

Public MyAddIn ??? 'how to setup this reference to COMMON.xla?

Props as MyAddIn.properties

public Function someFunction() as boolean
Props = new MyAddIn.Properties

some_property = Props.<some access method_or_property

MyAddIn.Log.writeLog("some string") 'is this correct?

End Function

cheers,
g

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

ghost Wolf wrote ...

Exactly how would you go about setting up a reference to MyAddIn?

My workbook has a reference (in the VBE: Tools, References) to my
add-in's VBA project (named vbaServer), and the following code
applies:

' ---<In class module named MyClass in add-in
' Set Instancing property to 2 - PublicNotCreatable
Option Explicit

Private Const SECRET_TEXT As String = "" & _
"Hippo"

Public Property Get Secret() As String
Secret = SECRET_TEXT
End Property
' ---</In class module named MyClass in add-in


' --- <In a standard module in add-in ---
Option Explicit

Public Function GetClassInstance() As MyClass
Dim oMyClass As MyClass
Set oMyClass = New MyClass
Set GetClassInstance = oMyClass
End Function
' --- </In a standard module in add-in ---


' ---<In ThisWorkbook code module of workbook
Option Explicit

Private Sub Workbook_Open()
Dim Instance1 As vbaServer.MyClass
Set Instance1 = vbaServer.GetClassInstance()
MsgBox Instance1.Secret
End Sub
' ---</In ThisWorkbook code module of workbook


Jamie.

--

G,

1. Jamie's right about the class thing. My mistake. I actually use compiled
activex dlls for a lot of my classes where you can set the instancing to
public (creatable),
and you can then access the class.
2. If all you want to do is run the function in the other addin, you can use
application.run

in your case I think it should be
Retval = application.run("COMMON.xla!WriteLog","Log entry")

The function in common could reference the class modules in common.

Robin Hammond
www.enhanceddatasystems.com

"ghost Wolf" wrote in message
...


Sorry if this message has been repeated

Exactly how would you go about setting up a reference to MyAddIn?

Lets say you have an addin calles COMMON.xla with the class module
properties and the module Log.
The Log has a public function writeLog(str as String) that you want to
access from any workbook/sheet


Example:

Public MyAddIn ??? 'how to setup this reference to COMMON.xla?

Props as MyAddIn.properties

public Function someFunction() as boolean
Props = new MyAddIn.Properties

some_property = Props.<some access method_or_property

MyAddIn.Log.writeLog("some string") 'is this correct?

End Function

cheers,
g

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

guys,
you have been very helpful so far - lets see if you can resolve this as
well.

Ok, Now when I have a working common code that I am able to use then I
am faced with another problem which I never initially thought of.

Working with versioning I am faced with the simple fact that the common
code may reside in several environments

The problem however is that Excel stores static paths to the common
module within the xls that has references to the common module.
If my work environment is...
C:\Proj\Dev\Reports\*.*
and I have a local version of the common code module in this report
directory called "Ccommon.xla" and then set up a report x.xls to use
this Common.xla module, a static path to the Common.xla is stored within
the x.xls module.
In this instance the "C:\Proj\Dev\Reports\Common.xla" is stored in the
x.xls workbook.

Problems emerges when the x.xls is versioned into a test environment.
The initial problem is that I don't neccesary have the full picture of
what the server looks like. I may only see a fraction of the full
directory structure.
Secondly, my local project structure may not necessary look the same as
TEST and PROD.
Prod may look like... <drive:\some_tree\PROD\Reports\
or <drive:\some_tree\TEST\Reports\
NOT C:\PROJ\ect etc.

The migration of x.xls from DEV to TEST will cause the test version of
x.xls to still look for a version of the Common.xla in the
C:\PROJ\DEV\Reports\Common.xla

Does anyone have a solution to this problem. Obviously I would like
Excel to be able to pick up a local copy of the Common.xla in the
Reports doirectory regardless of environment.
If run in Test, use the TEST\Reports\Common.xla
If run in Prod, use the PROD\Reports\Common.xla

cheers,
g


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!