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

  1. Save a blank workbook as a Excel Add-In (*.xlam) in your Add-In folder.
    I named it MyAddIn.xlam.
  2. Click Office button.
  3. Click "Excel options" button.
  4. Click "Add-Ins" tab.
  5. Select Excel Add-ins.
  6. Click "Go..." button.
  7. Click "Browse.." button.
  8. Select MyAddIn.xlam.
  9. Click OK.
  10. Make sure MyAddIn is enabled in Add-In Manager.
  11. 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.
  5. Copy and paste custom functions and macros to code window.

How to use custom functions in an Add-In

  1. Select a cell.
  2. Type the custom function name in formula bar. Press Enter.
    If you don´t know the name of your custom function, continue to step 3.
  3. Click "Insert Function" button.
  4. Select category "User Defined".
  5. Select your custom function.
  6. 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.
  4. Click "Run" button

Example custom functions and macros

Which macro shortcut keys do you use? (Code for excel and outlook)
Personal.xls (Daily dose of excel)
User defined functions (Get Digital Help)

Recommended blog posts:

Excel Macro Toolbar
Menu for favorite macros in Excel 2007-2010 (for all workbooks)
Making Your Custom Functions Available Anywhere