Macro Library

[velvetlady]

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

 

[JLGWhiz]

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

 

[velvetlady]

yes i have them all declared as Public sub NNNN() or public function MMMM(arg)

 

[JLGWhiz]

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]

The third method mentioned is what I have always tried to do. All the general
macros are saved in personal.xla which is always loaded at startup.
I always get the message that the sub or function cannot be found when I try
to execute the calling macro from the open workbook.
What am I doing wrong????

 

[JLGWhiz]

The second method looks like the easiest but I guess you want to use the
macros with all workbooks, not just one additional. Here is Chip's websote
link. Check his detailed remarks for Add-Ins.

http://www.cpearson.com/excel/topic.aspx

 

[Chip Pearson]

With an XLA add-in, Excel will automatically search that add-in for
functions when called from a worksheet cell. The same does not hold
true for VBA code. VBA won't search anything to which it does not have
a reference. Therefore, to call code that resides in an XLA, the
calling workbook project must have a reference set to the XLA or use
Application.Run. In other words, if you don't tell Excel where to
find something, it won't find it.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[velvetlady]

Thank you very much!!! All I needed was for someone to expain to me in simple
term (I'm a simple girl) how to make the proper reference. I was sure there
was a way but had been unable to figure it our on my own.

Thanks again,