How to refer to Excel macros (not functions) in .XLAM addin saved outside Personal Macro folder in Excel...












0















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.










share|improve this question























  • 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, or early-binding. These are concepts with which you should be familiar as a VBA programmer.

    – Ron Rosenfeld
    Nov 20 '18 at 11:44


















0















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.










share|improve this question























  • 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, or early-binding. These are concepts with which you should be familiar as a VBA programmer.

    – Ron Rosenfeld
    Nov 20 '18 at 11:44
















0












0








0








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.










share|improve this question














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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 18 '18 at 8:13









LunLun

867




867













  • 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, or early-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













  • 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, or early-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














2 Answers
2






active

oldest

votes


















0














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






share|improve this answer































    0














    After several weeks of researching and trying out different methods, I found the best method as follows:




    1. 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.


    2. Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)


    3. 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)


    4. 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)



    enter image description here



    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.



    enter image description here




    1. Save and close the VBA window.
      You can now call these macros from the macro list by typing Alt-F8.


    enter image description here




    1. Save and close your XL file.


    Sources:
    https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules






    share|improve this answer

























      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      0














      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






      share|improve this answer




























        0














        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






        share|improve this answer


























          0












          0








          0







          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






          share|improve this answer













          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







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 19 '18 at 15:16









          FXDFXD

          1,221126




          1,221126

























              0














              After several weeks of researching and trying out different methods, I found the best method as follows:




              1. 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.


              2. Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)


              3. 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)


              4. 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)



              enter image description here



              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.



              enter image description here




              1. Save and close the VBA window.
                You can now call these macros from the macro list by typing Alt-F8.


              enter image description here




              1. Save and close your XL file.


              Sources:
              https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules






              share|improve this answer






























                0














                After several weeks of researching and trying out different methods, I found the best method as follows:




                1. 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.


                2. Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)


                3. 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)


                4. 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)



                enter image description here



                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.



                enter image description here




                1. Save and close the VBA window.
                  You can now call these macros from the macro list by typing Alt-F8.


                enter image description here




                1. Save and close your XL file.


                Sources:
                https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules






                share|improve this answer




























                  0












                  0








                  0







                  After several weeks of researching and trying out different methods, I found the best method as follows:




                  1. 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.


                  2. Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)


                  3. 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)


                  4. 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)



                  enter image description here



                  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.



                  enter image description here




                  1. Save and close the VBA window.
                    You can now call these macros from the macro list by typing Alt-F8.


                  enter image description here




                  1. Save and close your XL file.


                  Sources:
                  https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules






                  share|improve this answer















                  After several weeks of researching and trying out different methods, I found the best method as follows:




                  1. 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.


                  2. Make sure MyMacros.xlam add-in is connected in Click File - Options (Or Alt - F, T) Add-Ins - Manage Excel Add-Ins - Go (G)


                  3. 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)


                  4. 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)



                  enter image description here



                  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.



                  enter image description here




                  1. Save and close the VBA window.
                    You can now call these macros from the macro list by typing Alt-F8.


                  enter image description here




                  1. Save and close your XL file.


                  Sources:
                  https://www.myonlinetraininghub.com/calling-vba-in-add-ins-from-vba-modules







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Jan 1 at 15:42

























                  answered Jan 1 at 15:32









                  LunLun

                  867




                  867






























                      draft saved

                      draft discarded




















































                      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.




                      draft saved


                      draft discarded














                      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





















































                      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







                      Popular posts from this blog

                      Monofisismo

                      Angular Downloading a file using contenturl with Basic Authentication

                      Olmecas