If an Excel add-in is acting peculiarly (or not at all), it's often because Excel is broken in some way. Updating and repairing Excel will often fix things.
Follow the steps below - first update, then diagnose/repair. It's best to reboot your computer before starting an update or repair, to reduce the chances of a running program preventing files from being repaired or replaced.
This should be the same process as updating Windows, as Microsoft has merged the two:
Make sure that Excel is closed (ideally you will have rebooted your computer so that Excel hasn't even been opened yet in your current Windows session).
Visit the Microsoft Update page for instructions on how to run Windows update on your particular machine (typically via "Windows Update" in the Windows Control Panel).
Follow through the instructions until your version of Office has been updated with all the latests patches and service packs (e.g. SP1, SP2, etc.).
Running Office Diagnostics / Detect and Repair
The instructions for this depend on which version of Excel you're using:
Running Office Diagnostics for Excel 2007 or Excel 2010
Open Excel 2007/2010 and click the "Office Button" (the round icon in the top-left corner of the Excel window).
Click "Excel Options" at the bottom of the popup menu (see screenshot).
A form will pop up - click "Resources" at the bottom of the menu on the left.
Click the "Diagnose" button to the right of "run Microsoft Office Diagnostics" (see screenshot).
If Office advises that diagnostics were recently run, click the "Run Diagnostics Again" button.
If a description of why you should run Diagnostics appears, click the "Continue" button.
Once a "Microsoft Office Diagnostics" screen appears, close the "Resources" form, close Excel down altogether. This leaves the Microsoft Office Diagnostics screen open.
Click the "Run Diagnostics" button. If you get a choice about which diagnostics to run, run all of them.
Once complete, start Excel again and keep your fingers crossed that whatever didn't work before does work now...
Running Detect and Repair for Excel 2002 (XP) and Excel 2003
Open Excel, and go to "Help" (at the top of the Excel window, next to "File", "Edit", "View", "Insert" etc.).
Select "Detect and Repair..." (see screenshot).
Make sure to select "Restore my shortcuts while repairing".
Click "Start" to begin the Detect and Repair process.