• An add-in has a file extension of "xla" rather than "xls"
   
• An add-in is not returned in the enumeration of Workbook objects. For example it will not be listed in the following code:

        Dim WB As Workbook
        For Each WB In Workbooks
            Debug.Print WB.Name
        Next WB

However, it is  accessible via the Workbooks collection directly. For example, the following code works fine:

         Debug.Print Workbooks("TestAddin.xla").Name

Note that the "xla" file extension is necessary.

An add-in workbook is not visible, and it has no Windows, so it cannot be made visible. However, if during the development process you do need to make it visible, you can use the following code:

    Workbooks("TestAddin.xla").IsAddin = False

This causes Excel to treat the add-in workbook is a regular, visible workbook. You must reset the IsAddIn property back to True  and Save the add-in file in order for your add-in to work properly.

• Functions in a loaded add-in may be called as if they were normal Excel functions, e.g., =MyFunction(A1), rather than prefixing the name of the containing workbook to the function name e.g., =Book1!MyFunction(A1), where MyFunction is a public Function declared in a standard code module of the Add-In project.

To create an add-in, open a new, empty workbook and then go to the File menu, choose Properties in the list and enter in the "Subject" box the text that you want to display in the Add-Ins dialog for your add-in. This is unrelated to the file name which you use to save the add-in. This value will be displayed in the Add-Ins dialog Box. Next, enter some descriptive text in the "Comments" box. This text will be displayed in the Description Box of the Add-Ins dialog box.  Later, if you wish to change these properties, you must first set the IsAddIn flag using

    Workbooks("TestAddin.xla").IsAddin = False

make the change in the Properties dialog, and then reset the IsAddIn flag with

    Workbooks("TestAddin.xla").IsAddin = True

and finally, save the add-in with

    Workbooks("TestAddin.xla").Save

The commands shown above may be entered directly into the Immediate Window in the VBA Editor.

You should also change the name of the project from the default "VBAProject" to a meaningful name. On the Tools menu in VBA, choose "VBAProject Properties" to display the Properties dialog, and give your project a new name. If desired, you can check "Lock Project For Viewing" and assign a password to protect the code.

Next, select Save As from the File menu. In the "Save as type" drop down box of the Save As dialog, select "Microsoft Office Excel Add-in (*.xla)". If you do not have Excel 2007 installed, this item is at the bottom of the list. If you do have Excel 2007 installed, this item is in the middle of the list. If your add-in is to work in  versions earlier than Excel 2007, be sure to save it as "Microsoft Office Excel Add-in (*.xla)", not "Excel 2007 Add-In (*.xlam)" . Add-ins written for Excel 2003 will work fine in 2007, with the following caveat. Since Excel 2007 no longer supports the command bar and menu structure of earlier versions, any user-interface controls will appear in the "Add-In" ribbon, mixed in with the user-interface elements of any other add-ins or workbook command bar or menu item items. This is a major drawback to Excel 2007.

The Save As dialog will automatically change the destination folder to the AddIns folder of your User Profile directory. You can save the file in that default directory, or in any other folder.

Since an Add-In is never visible (unless you clear the IsAddIn flag as described above), you will likely want to include some user elements such as menu items or command buttons to access the features of your add-in (this is not necessary if the sole purpose of the add-in is to provide additional worksheet commands -- in this case, you might want to consider and Automation Add-In, written in VB6). Typically, this is done with a command bar. The following code will create a new command bar with two buttons, one assigned to execute a procedure named "Macro1" and the other assigned to execute a procedure named "Macro2":

Private Const C_COMMANDBAR_NAME = "MyAddinCommandBar"

Sub Auto_Open()

Dim CmdBar As Office.CommandBar
Dim CmdBtn As Office.CommandBarButton

''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Delete the command bar. The command bar should not
' exist at this point, but it may not have been
' properly deleted (e.g., system crash)
''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Application.CommandBars(C_COMMANDBAR_NAME).Delete
On Error GoTo 0

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Create a new, temporary command bar with a name C_COMMANDBAR_NAME.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set CmdBar = Application.CommandBars.Add(Name:=C_COMMANDBAR_NAME, _
    temporary:=True)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Create two temporary control buttons on the command bar.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set CmdBtn = CmdBar.Controls.Add(Type:=msoControlButton, temporary:=True)
With CmdBtn
    .FaceId = 81
    .Caption = "Macro 1"
    .Style = msoButtonIconAndCaption
    .OnAction = "'" & ThisWorkbook.Name & "'!Macro1"
End With

Set CmdBtn = CmdBar.Controls.Add(Type:=msoControlButton, temporary:=True)
With CmdBtn
    .FaceId = 80
    .Caption = "Macro 2"
    .Style = msoButtonIconAndCaption
    .OnAction = "'" & ThisWorkbook.Name & "'!Macro2"
End With

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Make the commandbar visible and put it in the docked
' location with other command bars, not a floating
' command bar.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CmdBar.Visible = True
CmdBar.Position = msoBarTop

End Sub

The command bar is destroyed when the add-in is unloaded (either by closing Excel or unchecking the add-in's entry in the Add-Ins dialog box.

Sub Auto_Close()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Delete the command bar.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Application.CommandBars(C_COMMANDBAR_NAME).Delete
    On Error GoTo 0
End Sub

In the Auto_Open code above, the Face IDs were arbitrarily chosen to be 80 and 81. In your add-in, you will want to use a more appropriate FaceID. John Walkenbach has an excellent add-in that will display all available FaceIDs, displaying the picture along with the FaceID value. Read about it and download it here.

As an alternative to a command bar, you could add an item to a menu. The following code will create two items on the Tools menu and assign Macro1 and Macro2 to those menu items.

Private Const C_TOOLS_MENU_ID = 30007
Private Const C_TAG = "MyTag"

Sub Auto_Open()

Dim Ctrl As Office.CommandBarButton
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Set Ctrl as a new control on the Tools menu. The Tools menu is
' found with FindControl using the ID C_TOOLS_MENU_ID value.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set Ctrl = Application.CommandBars.FindControl(ID:=C_TOOLS_MENU_ID). _
    Controls.Add(Type:=msoControlButton, temporary:=True)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Set the attributes of the control.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Ctrl
    .Tag = C_TAG
    .Caption = "Macro 1"
    .BeginGroup = True
    .FaceId = 80
    .Style = msoButtonIconAndCaption
    .OnAction = "'" & ThisWorkbook.Name & "'!Macro1"
End With

Set Ctrl = Application.CommandBars.FindControl(ID:=C_TOOLS_MENU_ID). _
    Controls.Add(Type:=msoControlButton, temporary:=True)
With Ctrl
    .Tag = C_TAG
    .Caption = "Macro 2"
    .BeginGroup = False
    .FaceId = 81
    .Style = msoButtonIconAndCaption
    .OnAction = "'" & ThisWorkbook.Name & "'!Macro2"
End With

End Sub

In the code above, the BeginGroup property of the first control is set to True to include a separator line above the newly created control. The Tag property can be any unique string. This value is used to find the controls in order to delete them in the Auto_Close procedure, shown below.

Sub Auto_Close()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Delete the controls.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Ctrl As Office.CommandBarControl
    Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)
    Do Until Ctrl Is Nothing
        Ctrl.Delete
        Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)
    Loop
End Sub

Once you have created your add-in, you need to load it. Save and close your add-in project. Then go to the Tools menu in Excel, choose Add-Ins and in the Add-Ins dialog box, click "Browse" and navigate to and open your add-in. This will permanently add it to the list.

That's about all there is to writing an Excel add-in.