This is from a previous posting by Chip Pearson:

There are basically three ways you can go about this. The first is to have
both the workbook that contains the sub and the workbook that needs to use
the sub open and use Application .Run to execute the code. E.g.,

Application.Run "'WorkbookWithSub.xls'!TheSubName"

Pay attention to the apostrophes. They are not necessary if your workbook
name does not contain spaces, but are necessary if the workbook name
contains spaces. In either case, they are harmless.

The second method is to create a reference from the workbook that needs to
use the sub to the workbook that contains the sub. In the workbook that
contains the sub, go to the Tools menu in VBA and choose "VBA Project
Properties". Change the name of the project to something meaningful, e.g.,
MyProject. Then open the workbook that needs to use the sub, go to to the
Tools menu in VBA, choose References, and select and check "MyProject" or
whatever you named the project. With this reference in place, you can call
the sub as if it resided in the same workbook. If there is the possibility
of a name collision (two subs with the same name), you can prefix the sub
name with the library name:

MyProject.MySub

Finally, you could put the sub into an Add-In and load that add-in via the
Add-Ins dialog on the Tools menu in Excel. With the add-in loaded, you can
call the sub directly as if it existed in the same workbook. Again, you can
avoid name collisions by including the project name of the add-in.

MyAddInProject.MySub





"velvetlady" wrote in message
...
yes i have them all declared as Public sub NNNN() or public function
MMMM(arg)
--
jwm


"JLGWhiz" wrote:

Have you made all the sub routines Public. Unless specifically declared
as
Public, they default to Private at Module level.


"velvetlady" wrote in message
...
I have a number of macros, both Subroutines (sub) and Function Routines
(Function) that may be applicable for use in any or many different
workbooks.
I would like to store these where Excel (either Excel2003 or Excel
2007)
can
always find them and use them when called either as function from
within a
cell or as a subroutine from a macro button on the sheet. My goal is to
have
only one place where I an edit and modify these routines where the
changes
will be "universal" without have to copy to each workbook and thereby
proliferate many older copies when a change is made.
I have tried storing them in personal .xla, personal.xls or another
named
addin. The function routine work from cells, but a subroutine call
alwaysgives the error that the routine cannot be found.
Is there a way to have Excel try to resolve references from
personal.xla,
or
anywhere else when there is a "missing" routine?
Thanks in advance for any help on this topic!!
-- jwm