When creating an Add-In, prepare all the procedures, objects and other elements as you would normally prepare a spreadsheet and test it out thoroughly.

Therefore, it is not uncommon to have a normal Excel file (xls/xlsx/xlsm/xlsb) and eventually an add-in file (xla/xlam) of the same information. From the ‘xls’ file, users save as an ‘xla’ file that allows for easier upgrade and maintenance.

An Add-In file can reside anywhere as the user can control where to point and load the file.

An Add-In file is in essence and invisible loaded file which can not physically be edited.

Creating the Add-In file

Prepare all the code including any functions, forms and other objects required for distribution.

The saving action is within the Excel interface (as you normally save a file) but you may want to change a few properties within the module especially applying a password protection to your code.

In the module, right-mouse click the VBAProject node in the Project Explorer view and choose VBAProject Properties... from the pop-up menu.

In the first tab, set the narratives as required.

(Note: The Project Name property can not contain any spaces).

Click on the Protection tab and set a password which is case sensitive and make sure you enable the tick Lock project for viewing.

Choose the OK button to apply.

The next time a user tries to view the code by expanding the node they will be prompted for a password.

Back in Excel, save your current file (xls/xlsx/xlsm) as an open copy before choosing save as (F12 function key).

Choose the Add-In file type and choose a location to store you copy file.

A copy is generated and the original remains as your working copy which is required should you want to re-generate a newer version.

All Add-In files are read-only and therefore can not be edited and saved.

Loading the Add-In file

To load and install the file you use the Add-In manager tool (and not file, open).

Click on the Office button and choose Excel Options at the bottom of the menu.

Click on the Add-Ins category (on the left pane) followed by the Go... button (at the bottom of the main screen.

You are now taken to the Add-Ins dialog box.

Select the Add-In file and click OK.

Every time Excel starts, it loads all the Add-In files in the background enabling the functionality from your file and is seamless to the application (and user).

Add-In Workbook Events

Special reserved events exist for an Add-In file which is executed as the Add-In is loaded and unloaded into Excel.

Developers typically use these events to control the initialisation and resetting of command bars and menus amongst other object changes.

The two events are:

Workbook_AddinInstall()
Workbook_AddinUninstall()

The events can be found by loading the module to ThisWorkbook (node) and choose Workbook from the Object drop-down control.

On the right hand drop down box (Procedure), scroll for the above two events and type the code required.

Example use for the above events:

'Creates a menu item too the Tools menu
Private Sub Workbook_AddinInstall()
    Dim cb As CommandBarControl
    Set cb = Application.CommandBars("Tools") _
          .Controls.Add(Type:=msoControlButton)
    With cb .BeginGroup = True
        .Caption = "My Report..."
        .FaceId = 0
        .OnAction = "MyReport"
    End With
End Sub

The above procedure creates and appends a menu item to the existing Tools menu (Ribbon Bar).

The OnAction property assigns the macro procedure that is called when the item is clicked.

'Removes a menu item from the Tools menu
'If it can be found.
Private Sub Workbook_AddinUninstall()
    Dim cb As CommandBarControl
    Dim cbt As Integer, i As Integer
    i = 1 cbt = Application.CommandBars("Tools").Controls.Count
    Do Until i > cbt
        Set cb = Application.CommandBars("Tools") _
            .Controls.Item(i)
        If cb.Caption = "My Report..." Then
            cb.Delete
            Exit Do
        End If
    Loop
End Sub

The above procedure removes the custom menu item if it can be found, resetting the Tools menu bask to default.

Both procedures should be completed with error handling procedures to prevent unnecessary errors occurring.

(Note: The above example code to install and uninstall was based on Excel version 2003).

Next Topic: Variables & constants

Want to teach yourself Access? Free online guide at About Access Databases

Tip: To switch between Excel interface and VBE window use ALT + F11 shortcut keys.

Use F12 function in Excel to run the 'Save As' command.

VBA Keywords: Application, CommandBars, Controls, Caption, Add, OnAction and Delete.