How to refer to Excel macros (not functions) in .XLAM addin saved outside Personal Macro folder in Excel...
We can create an .XLAM addin with custom functions (UDF). Once we connect Excel to the addin, these UDFs can then be called from another workbook regardless of where the addin was saved (even outside the Personal Macros folder).
But the same cannot be done for macros (sub-procedures) saved in the same .XLAM file. From my research and trial it seems the sub-procedures can be called correctly if the addin was saved in the Personal Macros folder. But if the addin was saved somewhere else, the macro would not show up in the Macro list upon pressing Alt-F8.
Is there a way around this? We need the addin to be saved in C:OneDriveMacrosAddin.xlam because the addin would update frequently so this saves the hassle when everybody updates it automatically via OneDrive.
We are using Excel 2016 and 2019.
excel vba user-defined-functions add-in
|
show 3 more comments
We can create an .XLAM addin with custom functions (UDF). Once we connect Excel to the addin, these UDFs can then be called from another workbook regardless of where the addin was saved (even outside the Personal Macros folder).
But the same cannot be done for macros (sub-procedures) saved in the same .XLAM file. From my research and trial it seems the sub-procedures can be called correctly if the addin was saved in the Personal Macros folder. But if the addin was saved somewhere else, the macro would not show up in the Macro list upon pressing Alt-F8.
Is there a way around this? We need the addin to be saved in C:OneDriveMacrosAddin.xlam because the addin would update frequently so this saves the hassle when everybody updates it automatically via OneDrive.
We are using Excel 2016 and 2019.
excel vba user-defined-functions add-in
Add-Ins are selected inDevelopers -> Add-Ins
(or in settings). They can be turned off or on. You can point to any add-in file - no matter where it's located.
– JohnyL
Nov 18 '18 at 8:18
Create an update function in the macro instead. I have code for that at my office and can share it tomorrow. Meaning when you start Excel it will look for a new version of the xlam file, if there is a new version it will copy the new and replace the old in the users add-in folder.
– Andreas
Nov 18 '18 at 8:27
Did you install the add-in? Once you do that, and set a reference in the VBA UI, you should be able to do what you say.
– Ron Rosenfeld
Nov 18 '18 at 11:23
@RonRosenfeld how do I add a reference in VBA UI?
– Lun
Nov 20 '18 at 10:21
1
It is one of the menu options. You should familiarize yourself with all of them. For this one, Do an internet search for something likeVBA add a reference
, orearly-binding
. These are concepts with which you should be familiar as a VBA programmer.
– Ron Rosenfeld
Nov 20 '18 at 11:44
|
show 3 more comments
We can create an .XLAM addin with custom functions (UDF). Once we connect Excel to the addin, these UDFs can then be called from another workbook regardless of where the addin was saved (even outside the Personal Macros folder).
But the same cannot be done for macros (sub-procedures) saved in the same .XLAM file. From my research and trial it seems the sub-procedures can be called correctly if the addin was saved in the Personal Macros folder. But if the addin was saved somewhere else, the macro would not show up in the Macro list upon pressing Alt-F8.
Is there a way around this? We need the addin to be saved in C:OneDriveMacrosAddin.xlam because the addin would update frequently so this saves the hassle when everybody updates it automatically via OneDrive.
We are using Excel 2016 and 2019.
excel vba user-defined-functions add-in
We can create an .XLAM addin with custom functions (UDF). Once we connect Excel to the addin, these UDFs can then be called from another workbook regardless of where the addin was saved (even outside the Personal Macros folder).
But the same cannot be done for macros (sub-procedures) saved in the same .XLAM file. From my research and trial it seems the sub-procedures can be called correctly if the addin was saved in the Personal Macros folder. But if the addin was saved somewhere else, the macro would not show up in the Macro list upon pressing Alt-F8.
Is there a way around this? We need the addin to be saved in C:OneDriveMacrosAddin.xlam because the addin would update frequently so this saves the hassle when everybody updates it automatically via OneDrive.
We are using Excel 2016 and 2019.
excel vba user-defined-functions add-in
excel vba user-defined-functions add-in
asked Nov 18 '18 at 8:13
LunLun
867
867
Add-Ins are selected inDevelopers -> Add-Ins
(or in settings). They can be turned off or on. You can point to any add-in file - no matter where it's located.
– JohnyL
Nov 18 '18 at 8:18
Create an update function in the macro instead. I have code for that at my office and can share it tomorrow. Meaning when you start Excel it will look for a new version of the xlam file, if there is a new version it will copy the new and replace the old in the users add-in folder.
– Andreas
Nov 18 '18 at 8:27
Did you install the add-in? Once you do that, and set a reference in the VBA UI, you should be able to do what you say.
– Ron Rosenfeld
Nov 18 '18 at 11:23
@RonRosenfeld how do I add a reference in VBA UI?
– Lun
Nov 20 '18 at 10:21
1
It is one of the menu options. You should familiarize yourself with all of them. For this one, Do an internet search for something likeVBA add a reference
, orearly-binding
. These are concepts with which you should be familiar as a VBA programmer.
– Ron Rosenfeld
Nov 20 '18 at 11:44
|
show 3 more comments
Add-Ins are selected inDevelopers -> Add-Ins
(or in settings). They can be turned off or on. You can point to any add-in file - no matter where it's located.
– JohnyL
Nov 18 '18 at 8:18
Create an update function in the macro instead. I have code for that at my office and can share it tomorrow. Meaning when you start Excel it will look for a new version of the xlam file, if there is a new version it will copy the new and replace the old in the users add-in folder.
– Andreas
Nov 18 '18 at 8:27
Did you install the add-in? Once you do that, and set a reference in the VBA UI, you should be able to do what you say.
– Ron Rosenfeld
Nov 18 '18 at 11:23
@RonRosenfeld how do I add a reference in VBA UI?
– Lun
Nov 20 '18 at 10:21
1
It is one of the menu options. You should familiarize yourself with all of them. For this one, Do an internet search for something likeVBA add a reference
, orearly-binding
. These are concepts with which you should be familiar as a VBA programmer.
– Ron Rosenfeld
Nov 20 '18 at 11:44
Add-Ins are selected in
Developers -> Add-Ins
(or in settings). They can be turned off or on. You can point to any add-in file - no matter where it's located.– JohnyL
Nov 18 '18 at 8:18
Add-Ins are selected in
Developers -> Add-Ins
(or in settings). They can be turned off or on. You can point to any add-in file - no matter where it's located.– JohnyL
Nov 18 '18 at 8:18
Create an update function in the macro instead. I have code for that at my office and can share it tomorrow. Meaning when you start Excel it will look for a new version of the xlam file, if there is a new version it will copy the new and replace the old in the users add-in folder.
– Andreas
Nov 18 '18 at 8:27
Create an update function in the macro instead. I have code for that at my office and can share it tomorrow. Meaning when you start Excel it will look for a new version of the xlam file, if there is a new version it will copy the new and replace the old in the users add-in folder.
– Andreas
Nov 18 '18 at 8:27
Did you install the add-in? Once you do that, and set a reference in the VBA UI, you should be able to do what you say.
– Ron Rosenfeld
Nov 18 '18 at 11:23
Did you install the add-in? Once you do that, and set a reference in the VBA UI, you should be able to do what you say.
– Ron Rosenfeld
Nov 18 '18 at 11:23
@RonRosenfeld how do I add a reference in VBA UI?
– Lun
Nov 20 '18 at 10:21
@RonRosenfeld how do I add a reference in VBA UI?
– Lun
Nov 20 '18 at 10:21
1
1
It is one of the menu options. You should familiarize yourself with all of them. For this one, Do an internet search for something like
VBA add a reference
, or early-binding
. These are concepts with which you should be familiar as a VBA programmer.– Ron Rosenfeld
Nov 20 '18 at 11:44
It is one of the menu options. You should familiarize yourself with all of them. For this one, Do an internet search for something like
VBA add a reference
, or early-binding
. These are concepts with which you should be familiar as a VBA programmer.– Ron Rosenfeld
Nov 20 '18 at 11:44
|
show 3 more comments
2 Answers
2
active
oldest
votes
If the add-in is loaded, you can call any sub/function with the following code
Sub: Application.Run "YourAddinSub",param1, param2, ...
Function: Application.Run("YourAddinFunction",param1, param2, ...)
If needed, you can qualify the sub. For example: YourAddin.YouAddinSub
add a comment |
After several weeks of researching and trying out different methods, I found the best method as follows:
Make sure you saved your macro source (e.g. MyMacros.xlsm) as MyMacros.xlam so that the add-in contains the latest macros and functions.
Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)
In the second Excel file (let's say MyExcel.xlsm), open VBA Editor (Alt-F11). Choose the MyMacros.xlsm project and rename the name to MyMacros to be different (pic)
If you have several XL files open, click on the MyExcel project in the left VBA Project List pane (not MyMacros!). Then go to Tools - References and select MyMacros from the list.
You should now see a new reference in the VBA Project panel on the left side (pic)
5., Create a module named something like CallMyMacrosMod and type in several macro names from the Freelensia Macros.xlam file using Application.Run such as:
Sub FormatTables()
Application.Run "MyMacros.FormatTables"
End Sub
It is convenient to construct texts in the MyMacros.xlsm file itself storing the commands to call all of your macros. You can then copy and paste to Word (to avoid the quotes problem), then copy from Word and paste in this VBA Editor.
- Save and close the VBA window.
You can now call these macros from the macro list by typing Alt-F8.
- Save and close your XL file.
Sources:
https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53359015%2fhow-to-refer-to-excel-macros-not-functions-in-xlam-addin-saved-outside-person%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If the add-in is loaded, you can call any sub/function with the following code
Sub: Application.Run "YourAddinSub",param1, param2, ...
Function: Application.Run("YourAddinFunction",param1, param2, ...)
If needed, you can qualify the sub. For example: YourAddin.YouAddinSub
add a comment |
If the add-in is loaded, you can call any sub/function with the following code
Sub: Application.Run "YourAddinSub",param1, param2, ...
Function: Application.Run("YourAddinFunction",param1, param2, ...)
If needed, you can qualify the sub. For example: YourAddin.YouAddinSub
add a comment |
If the add-in is loaded, you can call any sub/function with the following code
Sub: Application.Run "YourAddinSub",param1, param2, ...
Function: Application.Run("YourAddinFunction",param1, param2, ...)
If needed, you can qualify the sub. For example: YourAddin.YouAddinSub
If the add-in is loaded, you can call any sub/function with the following code
Sub: Application.Run "YourAddinSub",param1, param2, ...
Function: Application.Run("YourAddinFunction",param1, param2, ...)
If needed, you can qualify the sub. For example: YourAddin.YouAddinSub
answered Nov 19 '18 at 15:16
FXDFXD
1,221126
1,221126
add a comment |
add a comment |
After several weeks of researching and trying out different methods, I found the best method as follows:
Make sure you saved your macro source (e.g. MyMacros.xlsm) as MyMacros.xlam so that the add-in contains the latest macros and functions.
Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)
In the second Excel file (let's say MyExcel.xlsm), open VBA Editor (Alt-F11). Choose the MyMacros.xlsm project and rename the name to MyMacros to be different (pic)
If you have several XL files open, click on the MyExcel project in the left VBA Project List pane (not MyMacros!). Then go to Tools - References and select MyMacros from the list.
You should now see a new reference in the VBA Project panel on the left side (pic)
5., Create a module named something like CallMyMacrosMod and type in several macro names from the Freelensia Macros.xlam file using Application.Run such as:
Sub FormatTables()
Application.Run "MyMacros.FormatTables"
End Sub
It is convenient to construct texts in the MyMacros.xlsm file itself storing the commands to call all of your macros. You can then copy and paste to Word (to avoid the quotes problem), then copy from Word and paste in this VBA Editor.
- Save and close the VBA window.
You can now call these macros from the macro list by typing Alt-F8.
- Save and close your XL file.
Sources:
https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules
add a comment |
After several weeks of researching and trying out different methods, I found the best method as follows:
Make sure you saved your macro source (e.g. MyMacros.xlsm) as MyMacros.xlam so that the add-in contains the latest macros and functions.
Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)
In the second Excel file (let's say MyExcel.xlsm), open VBA Editor (Alt-F11). Choose the MyMacros.xlsm project and rename the name to MyMacros to be different (pic)
If you have several XL files open, click on the MyExcel project in the left VBA Project List pane (not MyMacros!). Then go to Tools - References and select MyMacros from the list.
You should now see a new reference in the VBA Project panel on the left side (pic)
5., Create a module named something like CallMyMacrosMod and type in several macro names from the Freelensia Macros.xlam file using Application.Run such as:
Sub FormatTables()
Application.Run "MyMacros.FormatTables"
End Sub
It is convenient to construct texts in the MyMacros.xlsm file itself storing the commands to call all of your macros. You can then copy and paste to Word (to avoid the quotes problem), then copy from Word and paste in this VBA Editor.
- Save and close the VBA window.
You can now call these macros from the macro list by typing Alt-F8.
- Save and close your XL file.
Sources:
https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules
add a comment |
After several weeks of researching and trying out different methods, I found the best method as follows:
Make sure you saved your macro source (e.g. MyMacros.xlsm) as MyMacros.xlam so that the add-in contains the latest macros and functions.
Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)
In the second Excel file (let's say MyExcel.xlsm), open VBA Editor (Alt-F11). Choose the MyMacros.xlsm project and rename the name to MyMacros to be different (pic)
If you have several XL files open, click on the MyExcel project in the left VBA Project List pane (not MyMacros!). Then go to Tools - References and select MyMacros from the list.
You should now see a new reference in the VBA Project panel on the left side (pic)
5., Create a module named something like CallMyMacrosMod and type in several macro names from the Freelensia Macros.xlam file using Application.Run such as:
Sub FormatTables()
Application.Run "MyMacros.FormatTables"
End Sub
It is convenient to construct texts in the MyMacros.xlsm file itself storing the commands to call all of your macros. You can then copy and paste to Word (to avoid the quotes problem), then copy from Word and paste in this VBA Editor.
- Save and close the VBA window.
You can now call these macros from the macro list by typing Alt-F8.
- Save and close your XL file.
Sources:
https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules
After several weeks of researching and trying out different methods, I found the best method as follows:
Make sure you saved your macro source (e.g. MyMacros.xlsm) as MyMacros.xlam so that the add-in contains the latest macros and functions.
Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)
In the second Excel file (let's say MyExcel.xlsm), open VBA Editor (Alt-F11). Choose the MyMacros.xlsm project and rename the name to MyMacros to be different (pic)
If you have several XL files open, click on the MyExcel project in the left VBA Project List pane (not MyMacros!). Then go to Tools - References and select MyMacros from the list.
You should now see a new reference in the VBA Project panel on the left side (pic)
5., Create a module named something like CallMyMacrosMod and type in several macro names from the Freelensia Macros.xlam file using Application.Run such as:
Sub FormatTables()
Application.Run "MyMacros.FormatTables"
End Sub
It is convenient to construct texts in the MyMacros.xlsm file itself storing the commands to call all of your macros. You can then copy and paste to Word (to avoid the quotes problem), then copy from Word and paste in this VBA Editor.
- Save and close the VBA window.
You can now call these macros from the macro list by typing Alt-F8.
- Save and close your XL file.
Sources:
https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules
edited Jan 1 at 15:42
answered Jan 1 at 15:32
LunLun
867
867
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53359015%2fhow-to-refer-to-excel-macros-not-functions-in-xlam-addin-saved-outside-person%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Add-Ins are selected in
Developers -> Add-Ins
(or in settings). They can be turned off or on. You can point to any add-in file - no matter where it's located.– JohnyL
Nov 18 '18 at 8:18
Create an update function in the macro instead. I have code for that at my office and can share it tomorrow. Meaning when you start Excel it will look for a new version of the xlam file, if there is a new version it will copy the new and replace the old in the users add-in folder.
– Andreas
Nov 18 '18 at 8:27
Did you install the add-in? Once you do that, and set a reference in the VBA UI, you should be able to do what you say.
– Ron Rosenfeld
Nov 18 '18 at 11:23
@RonRosenfeld how do I add a reference in VBA UI?
– Lun
Nov 20 '18 at 10:21
1
It is one of the menu options. You should familiarize yourself with all of them. For this one, Do an internet search for something like
VBA add a reference
, orearly-binding
. These are concepts with which you should be familiar as a VBA programmer.– Ron Rosenfeld
Nov 20 '18 at 11:44