Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Library
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Library
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Library
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Library
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Library
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???? -- jwm "JLGWhiz" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Library
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 "velvetlady" wrote: 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???? -- jwm "JLGWhiz" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Library
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) On Sat, 18 Apr 2009 14:40:01 -0700, velvetlady wrote: 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???? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Library
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, -- jwm "Chip Pearson" wrote: 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) On Sat, 18 Apr 2009 14:40:01 -0700, velvetlady wrote: 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???? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating an Excel macro library | Excel Programming | |||
best way to set up macro library so all spreadsheets have access? | Excel Programming | |||
Reference Library - Missing Library in a lower version. | Excel Programming | |||
Macro Library | Excel Programming | |||
Macro Library | Excel Programming |