Installing an Excel Add-In

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.

 

Peltier Tech Charts for Excel

Comments

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

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

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

  4. 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!

  5. 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!

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

  7. So is there a way to go about fixing it, or do I just keep applying the add in every time?

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

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

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

  11. 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,

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

  13. Thank You Jon,
    Looks like it is solution I was looking for

  14. Hi Jon, I had an excel guru from work develop a macro file that I add in to co-workers computers. Some of them have Excel 2007 and some have Excel 2003. The macro was created in Excel 2003. He used coding for both versions to make the macro work. I am having one problem with one computer where when trying to use the Tools Add-Ins dialogue box, it won’t recognize the .xla file type and gives me an error. What could be the problem with this one computer?

  15. Jim – What does “doesn’t recognize the file type” mean? Can you describe the symptoms? Also, what is the error message (and not just the error number)?

  16. It tells me that .xla is not a valid filetype for excel. I can’t remember the rest of the error message or the number, since it is not my computer that it happens on, I can’t duplicate it at this time.

  17. Jom – That’s bizarre. Was it Excel 2003 or 2007 that experienced the error?

  18. it was excel 2003 and only one istance of 2003. other computers with 2003 can rou the macro. he even has another .xla on the same machine.

  19. Michael Kerner says:

    Hello,

    I’m in need of a radar graph utility / add-in for excel which allows to define the scale of each leg of the axes indepentently

    I tried to install the custom radar chart add-in from http://www.tushar-mehta.com, but it does not work

    I use excel 2003 prof sp 2 with windows xp sp3

  20. Hi Jon,

    Great blog! I am having a tough time with an Excel add-in and I’m not sure how you might be able to figure it out based on the information I have available, but I am going to give it a try.

    I got a text book that came with a CD that contained an Excel add-in, which I installed (Excel 2007, just as you described). The book makes reference to a number of custom excel functions (OV_IR_FIXEDYIELD_DATE_YLD, etc.) and I figured that once I had installed the add-in I could just click on a cell and type =OV and I’d see the whole list of new functions, but I get nothing. And when I type in the whole function with arguments and hit enter I get a #NAME? error.

    Like I said, not a lot to go on, but if you have any thoughts I love to hear them.

    Thanks,

    Shawn

  21. If the functions are public functions in the add-in, they will appear in the Insert Function dialog, with arguments clearly listed. Even a private function will work in a cell, and if you type it in, equals, function name, open parenthesis, then open the Insert Formula dialog, it won’t tell you what the arguments have to be, but it will tell you the value of the function.

    Even if the add-in isn’t installed, but only opened as a workbook (double clicked from Windows Explorer), the functions should work.

    #NAME? means Excel doesn’t recognize the name of the function that you’ve typed in. Triple check that you have spelled the function name correctly. If Excel recognizes the function but the arguments are wrong, you’ll get #VALUE! instead.

    Is there any other evidence that the add-in is properly installed? A project in the VB Editor, menu or ribbon elements, etc.?

  22. Shawn Mitchell says:

    Hey Jon, thanks for the quick reply (although I requested email notification and did not receive it).

    Here’s what I found.

    I’m running excel2007 and the add-in was written for excel2003. The default installation directory was …office11library and not office12library. The office11library directory was not permissioned by default to run add-in’s on my install, so I had to add a Trusted Location in the Trust Center and even then I’m guessing because of some difference between 2003 and 2007 the auto-complete wasn’t working so I had to go to Insert Function and select User Defined, which is a major drag.

    Do you have any tricks for getting auto-complete to work on user defined functions so I can just start typing the function name (after an = of course) and have a list of matching functions pop up?

    Shawn

  23. Shawn –

    I believe the auto-complete feature for functions is new in Excel 2007. I have not taken time yet to learn about it.

  24. I have installed your LOESS add in, but am having trouble actually using it, how do I get to the above screens you have mentiond.

    Thanks in advance

    Rhodri

  25. The Add-Ins dialog is accessible from the Tools menu > Add-Ins (Excel 2003) or the Office button > Excel Options > Add-Ins > Go (Excel 2007).

    The LOESS dialog is accessible, after installation, from the PTS menu (Excel 2003) or from the Add-Ins tab (2007).

  26. Jon,

    I am new to excell as well — and am trying to use your LOESS add-in. I installed it and found it in my list of add-ins …. but … as silly a question as this is — I can’t figure out how access it — say get to the dialog boxes you made and everything… if I got to the VBA interface – how do I implement it? –maybe just a couple hints to get started, and then I can figure the rest out? Thanks!!

  27. Katharine –

    The LOESS utility should add a menu called PTS Charts (Excel 2003 and earlier) or a group on the Add-Ins ribbon tab also called PTS Charts (Excel 2007 and later), and on PTS Charts you should see a button for LOESS.

    You could also press Alt+F8 to open the Macros dialog, enter LoessDialog into the macro name box, and press Enter. It’s less convenient, but gets the job done.

  28. Great info in your blog. I have been looking for the intellisense answer forevevr.

    A follow up to the intellisense question. It is best explained through an example. Say I create test1.xlsm with a Function myAdd(). Now I save this as an add-in as test1.xlam I also change the vbproject to TestProject and name the add-in Test Addin. Test Addin shows up in the add-in list, I enable it but myAdd does not show up in subsequently opened new workbooks.

    Going back to your response, are you suggesting I try to reopen test1.xlsm and include a reference to “Test Addin” (Which in this case will be itself?) Then resave it as test1.xlam. Will I then be able to see myAdd() in the intellisense list of functions when I type =myA ?

    Thanks much

  29. Naveen –

    I don’t know how to make a function from an add-in appear in the list, the way a function in that same workbook appears. Jan Karel Pieterse shows a way to register your function, which may help, in Registering a User Defined Function with Excel.

  30. I have created an excel add-in with multiple UDF and would like to deploy it to multiple users. However, when a UDF is used in a spreadsheet it hard wires the add-in functions file location so when another uses opens the file they get a #NAME? unless the file is installed in the exact same directory structure. Is there a way around this?

    Thanks.

  31. You have provided a fantastic add-in with the chart export.

    I am working on a client website and need to put charts on his site once I create them in Excel. This add-in creates a graphic with perfect clarity. Before I was using screen copy and the results were less than desirable.

    Thanks so much for this super add-in!

  32. Hi Marie –

    Thanks for the note. I wrote that code to help with graphics for my own web site, and I decided others might like it too.

  33. Do you have an Excel Add-In for Office for MAC 2008 or 2011? I need the Add-In that updates charts all over the sheet and am currently using Off for MAC 2008, But we just received Office for MAC 2011; I haven’t installed it yet.

    thanks, this could save me a lot of time and work each week

  34. Brett –

    I do not make any add-ins for Mac Excel. Excel for the Mac uses an ancient version of VBA, and Windows VBA has newer commands and syntax which would crash on the Mac. Mac Excel 2008 doesn’t even have any VBA.

  35. I have created an add-in and placed it in the correct folder. However, I had to Open the Add-In manager and check the box beside it o make it load for each new and old file. I need to do away with this manual intervention.

    Is there an special installer to automate the placement of add-in files and then set up Excel to load them automatically?

  36. Lou –

    I have a draft article that describes this, but it’s not ready to publish.

    I use a free tool to create setup files that do all of this. It’s called Inno Setup. You can Google for ‘Inno Setup Excel AddIn’ to find information.

  37. Really like your blog!
    I have an XL add-in developed in XL 2003 and cannot get it to run properly on XL 2010.
    Error message: (my translation from German) compilation error – cannot find project or library.
    This is happening on code like: environ(“username”) and simple things like date.
    eg If DTPicker2 > Date …
    If I comment out these lines, then the user forms run OK -i.e. I am accessing an sql 2008 database and that all works perfectly.
    If I create a 2 line macro using environ and date, they also work ok. (weird)
    Suspect some confusion with a library version (refedit???) . Compilation?
    I have checked extras, connections(?) and nothing is marked as “missing”.
    Would be grateful for a push in the right direction.
    Thanks
    Duncan

  38. Are you using the date picker control to get the date? This is not something that ships with Excel. It comes with Access and with some various data components. You may have noticed that it didn’t work on everyone’s computer even in 2003, depending on what other stuff was installed. I would guess that it’s not installed in the 2010 environment. If you’re using Excel 2010 64-bit, it won’t work anyway, because it’s a 32-bit control.

    It’s always a crapshoot to rely on a nonstandard control. For an alternative date picker, try Googling “excel vba date picker”.

  39. I’m using a Date Box = dtpicker..n to get the date and that is definitely OK. Just tried a msgbox (dtpicker2) and that works ok. That is not the problem.
    Thanks for the advice regarding 64-bit!
    The problem appears to be with “standard” code like environ and date – the add-in just isn’t finding these.
    For example, I use the environ(“username”) to give me the logged-on name.

    As I said this morning (CET) these instructions work OK in a 2 line macro but not in the add-in.
    It’s something to do with the compilation and/or available libraries etc – but what?
    Duncan

  40. Duncan – Have you compiled and saved the add-in as an xlam file?

  41. Yes -and I have now re-compiled using isAddIn property = false and saving it under a new name as xlam.
    This has confused the issue even more as I cannot get rid of the references to the xla and xlam which I have previously used and do not want any more.
    XL calls up a previous version now.
    Alt f11 shows me 2 x xlam – I only want to see one of them.
    And the problems with environ and date persist.

    How can I clean up these references?
    I am using XL 2010 and Win7
    Duncan

  42. Hey: I have office 2007 and that I run macro based worksheets and wanted to include a series of reference object library. Everytime I check those in the TOOLS>References dialig box and say OK. Next time around when I open the EXCEL workbook, it seems like they are unchecked again. Do you think this could be solved by some means. Thanks

    Satish

  43. Satish –

    References are added to a given workbook. If you add them to a workbook today, and open a different workbook tomorrow, then there are no references, because you added them to a different workbook.

    If you added references to an add-in, you must explicitly save the add-in. Excel will not warn you if you are closing an add-in without saving changes.

  44. This is amazingly useful… thanks!!

  45. Dear Jon,

    I really like this add-in! Thanks. Is there a way that it could also change x- and y- error bars?

  46. Michael –

    Which add-in is that?

  47. Hi,
    I want to ask something about this ad-in for excel. I installed it into my computer (version 2003) and it worked perfectly the first day I used it. Now I am trying to use it again but it does not work. What I mean is that when I open the window to introduce the data and I want to select one of the options or I want to introduce the data, the windows just vanishes and nothing happens!
    Thanks in advance!
    Ana

  48. Hi again,
    It seems that I had a problem with my excel file. I managed to make it work.
    This excel add-in is very useful!
    Ana

  49. Excellent !

    This Add-In is perfect for exporting excel charts.
    Especially handy for creating images for publications.

    Thanks
    FM

  50. Any version compatible for Office for Mac?
    Cheers,
    /S

  51. Sorry for the short post – I should have indicate that I tried to use “PTS-ChangeSeriesFormula” but get an “non-compatible with office for mac” error message :-(
    Any version compatible with Office for mac would be much appreciated ;)
    Cheers,
    /S

  52. I really need help. I have done all of the normal actions to enable the DDXL add-in to Excel 2010. I am running Windows 7. I have downloaded the DDXL, installed it, selected “file”, “options”, “add-ins”, browse, clicked on the DDXL file, clicked on the actual file with the extension xll, and then I get an error message which states that it is not a valid file. I get this message every time. I have turned off my computer, turned it on, uninstalled DDXL, re-installed, changed it from a “read only” file to not a read only file, etc. There is something that I need to do and I don’t know what it is and my stats class starts on Monday so help!

  53. Beth –

    Sorry, I haven’t ever heard of DDXL. You’ll have to contact their customer service department.

  54. Using Excel 2007, I’m trying to store a master copy of my AddIn on a network drive and have my coworkers get it via “Browse”. So, when I select my AddIn, it asks me if I want to copy the AddIn to my local AddIns folder. I select Yes and expect that it will load this new AddIn from my local folder. BUT, this does not happen. It copies it but loads the file on the network drive . . . I don’t understand why it does this, why would I want to copy the file to a local folder and then not use it? Do you know anything regarding this?

  55. Nathan –
    Installing add-ins from networks can be tricky. If you had ever installed from the network location without copying the add-in to the local drive, Excel will find the network path of the add-in in the registry and continue to use that. I’m not sure if Excel points to the local version if it’s the first time the add-in is installed. You might investigate the Add-In Loader from http://www.decisionmodels.com/downloads.htm on Charles Williams’ site.

  56. Pro Tip: you can get almost identical smoothing results by taking taking a simple average of n observations (i.e. rows). You just have to offset the averaged block of values so that n/2 values are from rows above, and n/2 are from rows below.

    For example, if you have data in rows A1:A1000 and you want to smooth across 50 observations to simulate your moving regression of n = 50, you would use the following formula in cell B25 : =AVERAGE(A1:A50)

    Then just copy that formula down through B1000. Unfortunately, you do not get any values for B1 through B24, but otherwise it be virtually identical to running the LOESS function with n = 50. Plus, it runs several hundred times faster!

  57. Hadam –
    This comment relates more to the LOESS Utility than to installing an update, so I have copied your comment to that page and responded there.

  58. hi Jon,
    what can I say; your site is just fantastic and so useful for a beginner like me.
    I got a question related to an add-in a colleague has developed which we use intensively; the problem is that when one uses and saves a worksheet and then transfers it to other users, the formula from the add-in keeps the entire C drive path ending up in a function that doesn’t work properly. Is there something wrong in the vba code or will each user have to edit/replace the link which would be cumbersome as there are thousands of cells containing the function from the add-in. Thanks so much for your help.
    Cheers

  59. Christian –
    This is a problem I’ve encountered, and I’ve never had a satisfactory solution. If the UDFs are distributed within an add-in, the results are usually more reliable. In some cases I’ve added the UDFs to the workbooks that needed them, but this is not an option for general-purpose functions.

  60. Hi John,

    This is great. Thank you so much for easing the process of exporting images, something that Excel should do quite easily by default but unfortunately they have overlooked this simple ability (apparently older versions of Excel could do this by right-clicking on the image).

    I have but one question: Is it possible to export as a TIFF?
    I work in the realm of clinical research and we often use Excel to create our charts, and the journals typically require images to be in TIF or JPG format at 300 DPI.

    Also, I am curious if it would be possible to put the button on a different menu tab, say View, just to decrease the number of tabs up top? This is a small concern mainly out of curiosity.

    Thanks again!

  61. Exporting directly as a TIFF depends on which image filters are recognized by Office, which I’ve found to be highly unreliable. It can be done using Windows APIs, which I’ve avoided because of the complexity.

    It’s generally possible to put buttons wherever you want on the ribbon, depending on how comfortable you are with the requisite XML code, though Excel 2013 (and I believe 2010) allow a certain amount of ribbon customization through the user interface.

Trackbacks

  1. Tidy Up Your Charts « Contextures Blog says:

    […] you can download and install Jon Peltier’s free Align Chart Dimensions utility. There are installation instructions on Jon’s […]

  2. […] Installing an Excel Add-In I described the protocol for installing an add-in in Excel. The protocol I described was valid for […]

  3. […] Excel add-in. Save it to any convenient directory, then install it following the instructions in Installing an Excel Add-In or Installing an Add-In in Excel […]

  4. […] (see the update below for a new version of the utility). Install this add-in using the protocol in Installing an Excel Add-In or Installing an Add-In in Excel […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0