Create An Excel VBA Add In

If you have created VBA code and want the sub procedures and functions to be available in all existing and future workbooks you create, then the best way to access your VBA code is to create an Excel Add In. An add in for all intent and purpose, is a regular Excel Workbook with VBA which has a .xlam (or .xla in Excel 2003 or earlier) file extension. Once saved and installed as an Add In, the workbook is automatically opened and hidden so the VBA procedures and functions are accessible to all other workbooks. The Add In is hidden in a special way so that it can only be seen from the Project Explorer in the Visual Basic Editor (VBE) and can not be unhidden from within Excel. Add Ins are an ideal way of distributing VBA code. Excel comes with Add Ins that are not installed by default like Analysis ToolPak, Solver etc.

Lets assume that you have created an Excel workbook and have created a VBA module with numerous User Defined Functions (UDF) and Sub Procedures. This workbook must now be saved as an Add In. From within Excel, select Save As from the Office button. Save the file with a descriptive name and as an Add In file type which will have a .xlam file extension for 2007 and .xla type for 2003 and earlier. The file location will change to a dedicated Add In folder. You can save the Add In here or choose a different location. Close all open workbooks and re-start Excel.

To make this function available to all workbooks, the Add In must be installed. In Excel 2007 select the Office button and select the Excel Options button. From the Option categories down the left, select Add Ins. In the drop down box at the bottom of the window select Manage: Excel Add Ins, then click Go. The Add Ins dialog box is shown with a list of all available Add Ins. If you saved your Add In in the dedicated folder as above, the Add In should appear in the list. If you saved your Add In elsewhere, click the Browse button and navigate and select the Add In file, making sure the Add In is ticked in the Available Add In list.

Once the Add In is installed, its functions and procedures are available to be executed in the normal way. To make changes to the Add In in the future, you can access the VBA code from the Project Explorer in the Visual basic Editor where it appears as a project. Don't forget to save the changes.

It is advisable to password protect the VBA code so it cannot be viewed or modified by unauthorised users. To apply a password to the Add In, right click the Add In located in the Project Explorer of the VB Editor and select VBAProject Properties. On the Protection tab, tick the box to 'Lock project for viewing' and enter & confirm your password. Once saved, the protection will take effect once Excel is restarted.

If your Add In contains procedures or macros, these can be added on to the Quick Access Toolbar. Click the down-arrow to the right of the Quick Access Toolbar and select 'More Commands'. In the 'Choose commands from' box select 'Macros'. From the list of available procedures, select the procedure to add to the toolbar and click 'Add'. To change the appearance of the button, click the 'Modify' button. In the displayed dialog box, select an alternative icon and modify the procedure's display name.