Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, save user defined functions and macros in a personal add-in. When you open a new Excel spreadsheet, these functions and macros are ready to be used.

How to quickly create an empty add-in for excel 2007

Save a blank workbook as a Excel Add-In (*.xlam) in your Add-In folder. I named it MyAddIn.xlam.

1. Click Office button.
2. Click "Excel options" button.
3. Click "Add-Ins" tab.
4. Select Excel Add-ins.

1. Click "Go..." button.
2. Click "Browse.." button.
3. Select MyAddIn.xlam.
4. Click OK.
5. Make sure MyAddIn is enabled in Add-In Manager.
6. Click OK.

Add custom functions to your personal add-in

1. Click "Developer" tab on the ribbon.
2. Double click on Myfunctions.xlam in project window.
3. Click "Insert" tab.
4. Click Module.

Copy and paste custom functions and macros to code window.

How to use custom functions in an Add-In

Select a cell, then type the custom function name in formula bar. Press Enter. If you don´t know the name of your custom function, continue to next step. Click "Insert Function" button.

Select category "User Defined".

Select your custom function and then click OK.

How to use macros in an Add-In

1. Click "Developer" tab.
2. Click Macros button.
3. Type the name of your macro.

Note

The subs included in the .xlam o .xla will NOT display if:

• Option Private Module is specified in the module.
• The code is not in a regular/standard module.
• If the subs are private as in: Private Sub NameOfSub.
• If the subs have arguments as in: Sub NameOfSub(ByRef lngStuff as Long).