Why Would I Want to 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 VBA Add-in. An add-in for all intents and purposes, 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 Excel VBA 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 Excel VBA 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 Excel VBA 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 Excel VBA Add-ins dialog box is shown with a list of all available Add-ins. If you saved your Excel VBA Add-in in the dedicated folder as above, the Add-in should appear in the list. If you saved your Excel VBA 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 Excel VBA Add-in is installed, its functions and procedures are available to be executed in the normal way. To make changes to the Excel VBA 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 Excel VBA Add-in, right click the Add-in located in the Project Explorer of the VB Editor and select VBA Project 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 Excel VBA 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.
Edited by Rich Moyer
Rich Moyer is Principal Consultant (retired) of Spaho Consulting