create and distribute Excel add in

Question

I have created an extensive macro in excel, that I now want to distribute. I decided I want to do this by saving the macro as add in to a shared network, where all of my collegues have access to it. This way maintaining the macro will be way easier, as I am sure that there are plenty of updates, and bugfixing to come.

I have never created an add in before, so I read several online tutorials + stack overflow questions about it. Up until now I always saved my macros in personal.xlsb, likewise with the macro I now want to save as add in.

My first attempt was to simply save the workbook, where I run the macro, as .xlam, which did not work (I guess because the macro was not saved in that workbook, but in the personal.xlsb). So I deleted the macro from the personal.xlsb, opened a new workbook, opened the VBA editor, created a new module in that workbook and inserted my code. I then saved the workbook as .xlam. I checked that in the VBA Editor the IsAddin is set to True.

From here it gets confusing somehow. When I open the .xlam file there are no worksheets in it, the add-in is loaded, but the macro does not show up in "Macros", even though the code shows up in the Editor. When

However the .xlam is still empty when I open it - no code to be seen. When I open another workbook and load the add-in, in the VBA editor the add-in does show up, and the module with the code of the macro is there as well. But when I click on "macro" it does not show up. When I open a different Excel file, the add-in is loaded as well (in Excel-add-ins the box is ticked), the module does not show up, and in the VBA editor the .xlam also does not show up.

I have saved, closed, opened, and restarted several times. I have checked if the add-in is saved anywhere else - it was, but I deleted all .xlam files (as non of them worked anyway), and repeated my attempts stated above. When I load the .xlam from the shared network it seems to save itself into the addin folder of microsoft (I guess that is what it should do when it is loaded).

I would really appreciate if someone could help me with how to properly set this up.

I have:

• a VBA code that runs fine, when I run it as macro.

I want

• to save it to a shared network as add-in, and get it to run.

Edit1: the add-in shows up in the active add-ins section in options: