How to create a custom add-in code module that can be used by all workbooks?

[JenniferMurphy]

On my old XP machine, I had several add-in code modules that I created and populated with UDFs that I wrote. I created those modules a long time ago and I cannot remember how I did it.

Now I want to create similar add-ins on my new Win 10 machine, but I cannot figure out how. In the VBA editor (IDE?), if I click Insert | Module, it creates a workbook code module. I want a separate add-in code module that can be used by all workbooks.

Can someone explain what should be obvious?

Thanks

 

[Joe4]

Take a look here: How to Create and Use an Excel Add-in (Step-by-Step Tutorial)

 

[JenniferMurphy]

Take a look here: How to Create and Use an Excel Add-in (Step-by-Step Tutorial)

Thanks. Based on that, it looks like I have to create an XLAM file for the code module and then add that as an Add-In. Right?

I just did a search on my old XP machine and found an XLAM file for each of the Add-Ins I use there. Would I be better off just copying those files over to Win 10 and then adding them as Add-Ins there?

 

[Joe4]

Thanks. Based on that, it looks like I have to create an XLAM file for the code module and then add that as an Add-In. Right?

I actually have never created an Add-In myself, but from what I glean from the article, that is what it sounds like to me.

I just did a search on my old XP machine and found an XLAM file for each of the Add-Ins I use there. Would I be better off just copying those files over to Win 10 and then adding them as Add-Ins there?

It can't hurt to try, and see if that works. If it does, it should save you the work of having to re-create them.

 

[JenniferMurphy]

OK, thanks

 

[JenniferMurphy]

I actually have never created an Add-In myself,

Really? I couldn't live without them. I have dozens, maybe hundreds by now, of little utility UDFs that make my Excel life so much easier. Some of them just convert a long, complicated, error-prone expression into a simple UDF call. Others do involved calculations.

but from what I glean from the article, that is what it sounds like to me.

Yep, it works.

It can't hurt to try, and see if that works. If it does, it should save you the work of having to re-create them.

That also works. Now I have all of my UDFs working (so far) in Excel 365.

Thanks for your help.

 

[welshgasman]

@JenniferMurphy
Do you ever use your Personal.xlsb file?

 

[Joe4]

You are welcome.

As welshgasman mentions, I keep all mine in a Personal.xlsb file, mostly because I do not need to distribute them to others (just for my personal use).
If you do have need to do that, an Add-In is a good way to go.

 

[JenniferMurphy]

@JenniferMurphy
Do you ever use your Personal.xlsb file?

Nope, until you mentioned it just now, I didn't even know about it.

I just did a little reading. It appears that the main difference is that add-in modules can be sent to others or put on a shared disk.

I have several add-in modules. I have one main one, but a few others for specific tasks. And I have a "Test" add-in where I put new code while I am working out all the kinks. That way only code that has been tested and debugged are in the main add-in module. Can I do something similar with .xlsb modules?

Let me know if there are other differences or reasons why I should switch.

Thanks

 

[JenniferMurphy]

As welshgasman mentions, I keep all mine in a Personal.xlsb file, mostly because I do not need to distribute them to others (just for my personal use).
If you do have need to do that, an Add-In is a good way to go.

I do not need to give my code to anyone else. I use add-in module because that's all I knew about. Is there any other reason why I should switch?