Bdtjtk

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.

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

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)

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.

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

7. Save and close your XL file.

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