Creating Add-ins in Microsoft Excel 97

John Clarkson
Microsoft Corporation

April 1999

Summary: Describes the process of creating a simple Microsoft® Excel add-in. (7 printed pages) Provides detailed instructions and complete sample code. Includes:

Introduction
Adding a Command to the Tools Menu
Building the Product Sales Report
Loading, Unloading, and Running the Add-in

Introduction

A Microsoft Excel add-in is a workbook with an .xla extension. Add-ins are an ideal vehicle for storing and distributing complete applications, or custom functions and utilities. Advantages of distributing solutions as add-ins include:

Microsoft Visual Basic® for Applications (VBA) code in an add-in can be compiled at the time the file is saved. Code in a normal workbook isn't compiled until the file is run. Because add-in code is compiled ahead of time, it runs faster.
Add-in code can be hidden, preventing users from either viewing or modifying the code.
Worksheets in an add-in are hidden, allowing you to store formulas or data and keep them hidden from users.
Add-in events can't be bypassed by holding down the SHIFT key when the add-in is opened. This ensures that your initialization code will run.

This article explains how to create an Excel add-in, and describes the code in a sample add-in that does the following:

Adds a Report command to the Tools menu in Excel.
Opens an Excel template file.
Uses an Access 97 query to import data from the Northwind sample database and add it to the Excel template.

Excel add-ins are installed by default in the \Program Files\Microsoft Office\Office\Library folder. Loading an add-in makes the feature available in Excel and adds any associated commands to the appropriate menus.

Clicking the Report command on the Tools menu starts the Product Sales Report add-in described in this article. The next section describes how to add the Report command to the Excel user interface.

Adding a Command to the Tools Menu

This section explains how to create the code that adds and deletes a Report command on the Tools menu. (Please note that this code will not actually run until you install the add-in, as described in a later section.)

In Excel, press ALT+F11 to open the Visual Basic Editor (VBE).

In the project window, double-click ThisWorkbook. Copy the following event procedures to the ThisWorkbook module.

Option Explicit
Private Sub Workbook_AddinInstall()
' This procedure adds an item to the Tools 
' menu on the worksheet menu bar.

    ' This will be set to the Tools menu.
    Dim objCmdBrPp As CommandBarPopup
    ' This will be set to the Project menu item.
    Dim objCmdBtn As CommandBarButton
    
    ' Create an object variable referring 
    ' to the Tools menu.
    Set objCmdBrPp = Application.CommandBars _
            ("Worksheet Menu Bar").Controls("Tools")
    ' Add a command in the fourth position
    ' on the Tools menu.
    On Error Resume Next
    ' This generates a run-time error if 
    ' the menu item is absent.
    Set objCmdBtn = objCmdBrPp.Controls("Report")
    ' If a run-time error is generated,
    ' add the menu item.
    If Err.Number <> 0 Then
        Set objCmdBtn = objCmdBrPp.Controls.Add _
                (Type:=msoControlButton, before:=4)
    End If
    ' Disables the error handler.
    On Error GoTo 0
    
    ' Set properties for the new menu item.
    With objCmdBtn
        .Caption = "Report"
        .OnAction = "AddInCode.Master"
    End With

End Sub

Private Sub Workbook_AddinUninstall()
' This procedure deletes an item on the Tools 
' menu on the worksheet menu bar.

    ' If the Project command exists, delete it.
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar") _
           .Controls("Tools").Controls("Report").Delete
End Sub

Comments on the Code

The AddinInstall and AddinUninstall events occur when an add-in is installed and uninstalled, and are a convenient location for initialization and clean-up code. In this case, we're adding a Report command, which will be used to launch the Product Sales Report add-in, to the Tools menu.

The Delete method in the AddinUninstall event relies on the item's key, rather than its position, to identify the right item to delete.

    Application.CommandBars("Worksheet Menu Bar") _
        .Controls("Tools").Controls("Report").Delete

This is because, when Excel is closed and restarted, if our custom menu item is left in this position on the Tools menu, it will move up one position. By using the key, we're not relying on the Report command being in the fourth position on the menu. (By the way, you don't need to worry about deleting menu items accidentally. Excel replaces any missing built-in menu items each time it is started. Of course, if you delete the Exit command...)

The OnAction property sets the name of the procedure that runs when the Report menu item is clicked.

    With objCmdBtn
        .Caption = "Report"
        .OnAction = "AddInCode.Master"
    End With

The procedure the OnAction property calls must be located in a code module, and you must specify the module name.

Building the Product Sales Report

This section explains how to create the code that builds the Product Sales Report.

In the Visual Basic Editor on the Tools menu, click References.
In the References dialog box, click the checkbox beside Microsoft DAO 3.51 Object Library (or the latest version of DAO available), and then click OK.
On the Insert menu, click Module.
Press F4 to open the Properties window, and set the module's Name property to AddInCode.

Copy the following statements and procedures to the AddInCode module.

Option Explicit
Dim objRecordset As Recordset
Dim objWorkbook  As Excel.Workbook

Sub Master()
' Called by the AddinInstall event.
' This procedure calls other procedures and 
' cleans up object variables.
    
    Call DbConnect
    Call GetProductData

    ' Close object variables.
    Set objRecordset = Nothing
    Set objWorkbook = Nothing
    
End Sub


Sub DbConnect()
' Called by Master procedure.
' This procedure opens the database and 
' creates the Recordset object that 
' contains data for the presentation.

    Dim dbsNorthwind As Database

    ' Edit this path to match your drive, if necessary.
    Set dbsNorthwind = OpenDatabase _
            ("C:\Program Files\Microsoft " _
            & "Office\Office\Samples\Northwind.mdb")

    Set objRecordset = dbsNorthwind. _
            OpenRecordset("product sales for 1995", _
            dbOpenSnapshot)

    Set dbsNorthwind = Nothing
    
End Sub


Sub GetProductData()
' Called by Master procedure.
' This procedure retrieves data 
' from the product sales query.

    ' Open the report template.
    ' Store a reference to the workbook
    ' containing the template.
    Set objWorkbook = Excel.Workbooks.Add("report.xlt")
    
    ' Get data from the query.
    With objRecordset
        Do
            ' Call the procedure that adds this
            ' data to the template worksheet.
            Call AddToSheet _
                    (objRecordset.AbsolutePosition, _
                     objRecordset!ProductName & "", _
                     objRecordset!CategoryName & "", _
                     objRecordset!ProductSales)
            ' Move to the next record.
            .MoveNext
        Loop Until .EOF = True
    End With
End Sub


Sub AddToSheet(lngRowNumber As Long, _
               strProdName As String, _
               strCatName As String, _
               lngProdSales As Long)
' Called by GetProductData procedure.
' This procedure adds data to the template.
    With objWorkbook.Worksheets(1) _
            .Rows(lngRowNumber + 3)
        .Cells(, 1).Value = strProdName
        .Cells(, 2).Value = strCatName
        .Cells(, 3).Value = lngProdSales
    End With

End Sub

In Excel on the File menu, click Properties. Feel free to add your own text here, but a good choice for title is "Sales Report," with "Sample add-in" for comments. The title identifies an add-in in the Add-In Manager. The comment is displayed when an add-in is selected in Add-In Manager. Click OK.
In the VBE, save your project to a working folder as Report.xls.

Comments on the Code

Data is retrieved from the Access query and passed to the AddToSheet procedure with the following statements in the GetProductData procedure. The first expression in the procedure call, objRecordset.AbsolutePosition, passes a row number. The next two expressions, objRecordset!ProductName & "" and objRecordset!CategoryName & "", both retrieve string values from the Access query and pad the result with a zero-length string. Adding the zero-length string prevents "Invalid use of Null" errors in case Null values are encountered.

    With objRecordset
        Do
            ' Call the procedure that adds this data
            ' to the template worksheet.
            Call AddToSheet _
                    (objRecordset.AbsolutePosition, _
                     objRecordset!ProductName & "", _
                     objRecordset!CategoryName & "", _
                     objRecordset!ProductSales)
            
            ' Move to the next record.
            .MoveNext
        Loop Until .EOF = True
        
    End With

These statements in the AddToSheet procedure add data to the report template. The current record number in the Recordset object is passed in by the lngRowNumber argument and used to set the row number in the With...End With statement. With the row number already set, the Cells property works fine with just a column argument.

    With objWorkSheet.Rows(lngRowNumber + 3)
        .Cells(, 1).Value = strProdName
        .Cells(, 2).Value = strCatName
        .Cells(, 3).Value = lngProdSales
    End With

Loading, Unloading, and Running the Add-in

This section explains how to install and run the Product Sales Report add-in.

With Report.xls (the file created in the previous section) open in Excel, on the File menu click Save As.
Select the \Program Files\Microsoft Office\Office\Library folder, set the file type to Microsoft Excel Add-In, and click Save. (This creates a file named Report.xlt in the folder.)
Close Report.xls and open a blank workbook. (This isn't strictly necessary, but for your own sanity it helps to get the .xls version of the add-in out of the way.)
On the Tools menu, point to Add-Ins and click Add-In Manager. Check the box indicating the Product Sales Report add-in (Report.xlt) and click OK.
On the Tools menu, click Sales Report.
After the report is built, on the Tools menu click Add-Ins. In Add-In Manager, clear the box indicating the Product Sales Report add-in and click OK to uninstall the add-in.