MAKING EXCEL WORK FOR YOU

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


 Insert 2007 Addin

This site has been produced to try to help you to use Excel more productively. A frequent question on Excel Forums is "what is the best way to distribute my macros?" I would suggest the best way is without any doubt to use an Excel add-in. Excel add-ins are a special kind of workbook that can be used to hold & distribute code and has been saved as an add-in, using File ->Save as -> Microsoft Excel add-in. Note: the extension for versions prior to Excel 2007 is *.xla, for later versions it is *,xlam Once saved and re-opened the Workbook will be hidden and can only be seen in the "Project Explorer" via the Visual Basic Editor. It is not hidden in the same way as the Personal.xls(m) as this can be seen (and made visible) using Windows>Unhide.. Once completed users can easily install your add-in The first thing to do is to write your code in a new workbook. Remember that to enable your users to use your macros it might be necessary to add a Custom ToolBar or Menu, or for versions from 2007 on you can add a Customised Ribbon. What you must bear in mind is that some slight amendments to your code may be necessary. Some things to be aware of are

  1. ThisWorkbook will always refer to the add-in, not the user’s Workbook. Use ActiveWorkbook instead.
  2. The sheets in the ActiveWorkbook cannot be referred to using Code Names.
  3. If your add-in changes the user's Excel settings - ToolBars, View, etc then all previous settings must be restored
  4. Your code must be well tested and have error handling built in
  5. If the user has any sort of Protection on sheets or workbooks then never use code to uprotect any part of the user's Workbook. Simply display a message asking them to unprotect.
  6. Make use of the Worksheet(s) you have in the add-in. You could use the Worksheet(s) to store user setting like toolbars etc.
  7. Holding down the Shift key will NOT prevent any add-in Workbook events running like it can in a normal Excel workbook

If you need to see the add-in Workbook again, e.g. to make updates, modifications etc. Go into the VBE while the add-in is installed and from the Properties Window select the Isaddin property and set it to False. Saving the Workbook as an add-in sets this Property to True.

Apply protection to the Modules of you add-in, in the VB Editor use Tools->VBAProject Properties->Protection.

Installing the add-in

There have been changes in how to do this depending on the Excel version that you use, although basically the same you access the add-in dialog differently for the later versions of Excel.



Earlier versions of Excel 2003 2003200

Learn how to install an addin in  versions of Excel, after Excel  2007

Versions since Excel 2007 2003 2003200

Read Now

Learn how to install an addin in earlier versions of Excel, before Excel 2007




Read Now