Excel – Save your custom functions and macros in an Add-In


To totally unlock this section you need to Log-in


Login

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.

Excel - Save your custom functions and macros in an Add-In

  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.

Excel - Save your custom functions and macros in an Add-In

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.

Excel - Save your custom functions and macros in an Add-In

Copy and paste custom functions and macros to code window.

Excel - Save your custom functions and macros in an Add-In

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.

Excel - Save your custom functions and macros in an Add-In

Select category "User Defined".

Excel - Save your custom functions and macros in an Add-In

Select your custom function and then click OK.

Excel - Save your custom functions and macros in an Add-In

How to use macros in an Add-In

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

Excel - Save your custom functions and macros in an Add-In

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).