Installing an Excel Add-In
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
A lot of Excel procedures are packaged in Add-Ins. An Add-In is basically a workbook containing some code, that has some other unique properties. The workbook is invisible. The workbook can be installed, which means it opens when Excel opens, so that its functionality is always available.
Installing an Add-In is really not complicated. If it came in a zip file, unzip it into an appropriate directory. You can use any directory you want. Excel has one or more default add-ins directories, depending on version:
C:\Program Files\Microsoft Office\OfficeXX\Library
where XX is 12 for Office 2007, 11 for 2003, and so forth, and
C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns
where UserName is the user’s Windows account name.
With Excel running and any workbook open, go to Tools menu and choose Add-Ins. You will see the following dialog, which lists the add-ins Excel thinks are available. Any installed add-ins are checked.
If the add-in is saved in one of the default add-ins directories cited above, the add-in will be listed in the dialog, and you can check it to install it. If it isn’t listed, simply browse to select the add-in, and it will be added to the list and checked.
Below the list of add-ins, you will see the name and description of the selected add-in. Click the Okay button, and the add-in is installed and ready to go.
To uninstall an add-in, simply open the Add-Ins dialog and uncheck the add-in.
Possibly Related Posts:
- Installing an Add-In in Excel 2007
- Excel Templates Demystified
- Microsoft Chart Advisor
- Deming Regression Utility
- Unspecified but Painfully Frustrating Error
- Waterfall Chart Utility
- Interesting Links for 17 April 2009
- Improved Macro Security Warning in Excel 2010
- How To: Assign a Macro to a Toolbar or Menu
- LOESS Utility for Excel
Posted: Saturday, June 7th, 2008 under VBA.
Comments: 14
Comments
Comment from Marti Rosas
Time: Monday, June 9, 2008, 10:25 am
Dear Jon,
thank you very much for this esplendid and impressive web site you have created.
It is awesome indeed! I am quite new at excel macros and VBA (after some time dedicated to C++ and other environments), but I have seen that for the students this is an easier way to get into grips with programming. I have discovered the Solver Add-in not so long ago but I have found something weird I think. I follow the instructions that you give at
http://peltiertech.com/Excel/SolverVBA.html
but when you say “[...] In addition, the Solver library will be accessible through the VB Editor’s Object Browser (right), and you will have the benefit of Intellisense (below) while editing code that uses members of the Solver library” , I can see the classes, same as yours, but not the “Members of the VBA-Functions”, i.e. the functions SolverOk, SolverAdd, etc. Do you have any idea why is this so? Thank you very much for your time and congratulations again for the site!
Best wishes from Barcelona (Spain).
Comment from Jon Peltier
Time: Monday, June 9, 2008, 10:43 am
Hi Marti – Thanks for your note.
When you set a reference to SOLVER in a project, and the Object Browser is open, select the SOLVER library in the top dropdown of the Object Browser. This displays a list of only the SOLVER classes in the left hand list. VBA_Functions should be the last one listed. Selecting that class will show the functions in the list of members.
Comment from Marti Rosas
Time: Monday, June 9, 2008, 11:39 am
Hi Jon,
that is precisely the problem: when I select the SOLVER library in the top dropdown of the Object Browser it displays the list of the SOLVER classes in the left hand list and I can see the VBA_Functions at the end. But although I select that class, it does NOT show the functions in the list of Members. Consequently VB do not recognize any procedure related to Solver when I call it from a Macro. Nonetheless, I have just discovered something interesting (and even more strange for an absolute begginner like me!). The class Solver4 contains the same members as VBA_Functions though with another name: i.e., SolvAdd, SolvChange, and so on. When I use this “short-named” members, Solver can be called from my macro though when it finishes (after a SolvSolve instruction) there appears a dialog box that begins with
“Cell:[Solver.xla]Ecel4Functions!A1
Formula:
Start of solver cell.
[...]
asking you to continue, to halt, to do it step by step, etc. When I let it to continue and it finishes to calculate, the final Solver dialog box appears (that one with the options of answer, sensitivity, etc. reports) and everything finishes normally.;(
Anyway Jon: I do not want to bother you anymore. If it happens that you encounter the clue, I will be glad and thankfull! I will keep on
investigating what the h*ll is going on and if I find something I will tell you anyay just for you to know!
Thanks very much again for everything and your quick answer!
Comment from Jon Peltier
Time: Monday, June 9, 2008, 11:47 am
Hmmm, looks like a defect in your version of SOLVER. What version of Excel, what localization?
You can show or hide the dialogs you describe by setting the parameters of the VBA_Functions you call. I notice the functions in Solver4 have the same arguments as those in VBA_Functions. presumably the Solver4 functions behave the same way, so check my links for help on the VBA functions and their parameters, if I don’t have enough details in my web page.
Pingback from Tidy Up Your Charts « Contextures Blog
Time: Friday, June 20, 2008, 2:21 pm
[...] you can download and install Jon Peltier’s free Align Chart Dimensions utility. There are installation instructions on Jon’s [...]
Comment from Butch Cooper
Time: Sunday, September 14, 2008, 3:09 pm
Not really a comment, but a question.
I have Office 2003 installed and the forms I use for work is in Excel and we added an add-in to update a couple of forms.
When we added the add-in originally, it stay checked. BUT, the last few months, every time we open the form, we MUST go back and make sure the add-in is checked.
It never stays checked.
It USED to, but it no longer does.
Is there an easy way to fix my problem or will I just “ACCIDENTALLY” have to drop my computer in the ocean?
Any help on this would be FANTASTIC!
Comment from Jon Peltier
Time: Sunday, September 14, 2008, 6:04 pm
Butch -
This behavior points to one of two things that I can think of.
1. Excel isn’t shutting down properly, so it doesn’t remember add-ins that were installed during the session. If you manually customize the interface (add a custom button), does the customization appear next time you start up Excel?
2. There is some kind of IT policy that uninstalls some or all add-ins. If it happens on multiple computers, I would suspect something like this.
It might be something completely unrelated.
Comment from Butch Cooper
Time: Monday, September 15, 2008, 2:45 am
So is there a way to go about fixing it, or do I just keep applying the add in every time?
Comment from Jon Peltier
Time: Monday, September 15, 2008, 6:29 am
Butch -
Depends what the problem is. Shutdown errors are tricky to fix, IT problems are even more tricky, and it’s hard debugging computer problems via WordPress.
Comment from Rich Gautier
Time: Tuesday, May 5, 2009, 1:18 pm
Hi:
I developed an Add-in (xlam) for Excel 2007 for work that has some custom functions and custom macros. I found how to add custom Ribbon panels for my project, but I’d like the functions I’ve built-in to show up in Intellisense. I was browsing the web and noticed you say your add-in is available through Intellisense. Can you tell me how you exposed your Add-in functions to Intellisense?
Thanks,
Rich
Comment from Jon Peltier
Time: Tuesday, May 5, 2009, 3:56 pm
Hi Rich -
There are two steps. First, the procedures are declared as Public Subs or Functions. Second, in the VB project that accesses these procedures, I have set a reference to the add-in containing the procedures: VB Editor’s Tools menu > References. This is where having a unique project name is helpful, so you can recognize your add-in in the list. Otherwise you will see a handful of items names VBProject.
Comment from Igor
Time: Thursday, November 19, 2009, 9:13 pm
Hi,
How can I intercept from Add-In build in event- like FileSave, so I can trigger some custom handling for that event every time for any excel workbook opened
Thanks,
Comment from Jon Peltier
Time: Thursday, November 19, 2009, 11:00 pm
You need to insert a class module into the add-in, and declare an Excel Application variable with events. In the class module you can then add events like workbook_beforesave. Before using the class you have to instantiate the application variable.
This topic is covered in the “Application Events In An New Class Module” section of Chip Pearson’s Application Events page.
Comment from Igor
Time: Friday, November 20, 2009, 1:40 pm
Thank You Jon,
Looks like it is solution I was looking for
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.