Excel allows you to create .xlsm files (macro enabled spreadsheets) but these are a bit of a hassle because whenever someone wants to open one they need to click "Enable Content". The other big issue is that any code you write is not reusable in other files. Creating an Excel Add-in means you can save commonly used functions in a central repository. They don't move with the file but they are always there for you on your computer. So, depending on your needs this might be better than an xlsm.
Note: there is another option of saving your reusable code in your Personal.xlsb file.
To make an Excel Add-in
- Open a new Excel file and Save As...
- Make the file type Excel 97-2003 Add-In (*.xla). This will change the save path to something like C:\Users\%UserName%\AppData\Roaming\Microsoft\AddIns. If you are using a new version of Excel you can also use the .xlam Excel Add-In file type)
- Make the File name something like "MyUDFs"
- Save. Close your new xla (or .xlam)
- Reopen your just made file (the VBA IDE can be finicky about what shows up and this is a good step just to ensure everything is fresh in the Project Explorer window)
- Open the VBA IDE (Alt+F11) and add a new Module
- With "VBAProject (MyUDFs.xla)" selected, Insert > Module
- Let's create an example function
Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End FunctionWhatever code you write in here will be available in your worksheets. Only functions (not subs) will show up as available worksheet functions. You can save subs in here if you want, too; they will be usable but not from a worksheet.
- Save. Close.
- Open a fresh, blank Excel workbook. On the ribbon select Developer > Add-Ins
- If the developer tab is not showing for you you can make it show up by right clicking on the ribbon > Customize the Ribbon...
- When you have the Add-Ins dialog open you can select your newly minted add-in from the list.
- Now test it out!
I'll include the current contents of my MyUDFs file here for my own use and yours. I'll try to keep it updated.
I moved my Excel UDFs to GitHub so I can keep them up to date better.