Personal.xls Macros
Over the past year or so, I have built several macro
libraries including two that Excel applications use. I have 50 or so spreadsheets that use one or more macros from these libraries. Presently, the master copies of the Excel-relevant modules are stored in Personal.xls and are declared "public". When I try to call/refer to the macros in Personal.xls from another open spreadsheet, I get an error indicating that the referenced subroutine/function is not defined. I have tried adding the name of the "owning" spreadsheet, i.e., Personal.xls to the call (as is shown in the Macro List tool), but that generates "compiler" errors. The only way to make it work is to use the Application.Run command. But you can not pass arguements using that approach. So, I copy entire modules of specific macros into the application as I am building it. This works but it is cumbersome and requires nontrivial bookkeeping to ensure that an application is running the most recent library. Is there a simplier way to manage this stuff. For example, is there a way to have the application simply "fetch" or refer to the macro from Personal.xls at runtime? ANy help would be appreciated. |
Personal.xls Macros
"Mark" wrote in message ... The only way to make it work is to use the Application.Run command. But you can not pass arguements using that approach. Application.Run "MYCUSTOM.XLM!My_Func_Sum", 1, 5 |
Personal.xls Macros
Here are some methods for calling macros.
Syntax 1 and Syntax 2 assume that the macros are either in the same WorkBook or that there is a Reference to the WorkBook containing the macros. To set a reference to another WorkBook : If the WorkBook containing the macro to run is not open, open the WorkBook. Alt-F11 to access VBE. Ctrl-R to activate Project Explorer. Select the project of the macro that does the calling. Tools References. Check the name of the project that contains the called macros. Syntax 1 Call MacroName ' No parameters Call MacroName (param1, param2) ' Parameters Syntax 2 MacroName ' No parameters MacroName param1, param2 ' Parameters Syntax 3 assumes that the WorkBook contaning the called macros is open, but that there are no References to that WorkBook. Application.Run "'FileName.xls'!MacroName" ' No parameters Application.Run "'FileName.xls'!MacroName", param1, param2 ' Parameters Syntax 4 assumes that the WorkBook contaning the called macros is not open. Application.Run "'" & "PathName" & "FileName.xls" & "'!MacroName" ' No parameters Application.Run "'" & "PathName" & "FileName.xls" & "'!MacroName", param1, param2 ' Parameters HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- Over the past year or so, I have built several macro libraries including two that Excel applications use. I have 50 or so spreadsheets that use one or more macros from these libraries. Presently, the master copies of the Excel-relevant modules are stored in Personal.xls and are declared "public". When I try to call/refer to the macros in Personal.xls from another open spreadsheet, I get an error indicating that the referenced subroutine/function is not defined. I have tried adding the name of the "owning" spreadsheet, i.e., Personal.xls to the call (as is shown in the Macro List tool), but that generates "compiler" errors. The only way to make it work is to use the Application.Run command. But you can not pass arguements using that approach. So, I copy entire modules of specific macros into the application as I am building it. This works but it is cumbersome and requires nontrivial bookkeeping to ensure that an application is running the most recent library. Is there a simplier way to manage this stuff. For example, is there a way to have the application simply "fetch" or refer to the macro from Personal.xls at runtime? ANy help would be appreciated. |
Personal.xls Macros
Your suggestion to reference the Personal.xls file (VBA
editor/Tools/Reference) works exactly as I want. The only thing I had to do was change the name of VBAProject associated with Personal.xls from "VBAProject" which is ambiguous to something that is not. And that is a one time change. Thanks a lot -----Original Message----- Here are some methods for calling macros. Syntax 1 and Syntax 2 assume that the macros are either in the same WorkBook or that there is a Reference to the WorkBook containing the macros. To set a reference to another WorkBook : If the WorkBook containing the macro to run is not open, open the WorkBook. Alt-F11 to access VBE. Ctrl-R to activate Project Explorer. Select the project of the macro that does the calling. Tools References. Check the name of the project that contains the called macros. Syntax 1 Call MacroName ' No parameters Call MacroName (param1, param2) ' Parameters Syntax 2 MacroName ' No parameters MacroName param1, param2 ' Parameters Syntax 3 assumes that the WorkBook contaning the called macros is open, but that there are no References to that WorkBook. Application.Run "'FileName.xls'!MacroName" ' No parameters Application.Run "'FileName.xls'!MacroName", param1, param2 ' Parameters Syntax 4 assumes that the WorkBook contaning the called macros is not open. Application.Run "'" & "PathName" & "FileName.xls" & "'! MacroName" ' No parameters Application.Run "'" & "PathName" & "FileName.xls" & "'! MacroName", param1, param2 ' Parameters HTH Paul ---------------------------------------------------------- ---------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. ---------------------------------------------------------- ---------------------------------------------------- Over the past year or so, I have built several macro libraries including two that Excel applications use. I have 50 or so spreadsheets that use one or more macros from these libraries. Presently, the master copies of the Excel-relevant modules are stored in Personal.xls and are declared "public". When I try to call/refer to the macros in Personal.xls from another open spreadsheet, I get an error indicating that the referenced subroutine/function is not defined. I have tried adding the name of the "owning" spreadsheet, i.e., Personal.xls to the call (as is shown in the Macro List tool), but that generates "compiler" errors. The only way to make it work is to use the Application.Run command. But you can not pass arguements using that approach. So, I copy entire modules of specific macros into the application as I am building it. This works but it is cumbersome and requires nontrivial bookkeeping to ensure that an application is running the most recent library. Is there a simplier way to manage this stuff. For example, is there a way to have the application simply "fetch" or refer to the macro from Personal.xls at runtime? ANy help would be appreciated. . |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com