Return to Index
Operations Research Models and Methods
Computation Section
Subunit Do it Yourself
 - Making an Add-in

Creating a workbook and adding code


This page provides a brief introduction to making an add-in. It is not hard to create an add-in, but complete understanding of the VBA editor and language is far beyond this introduction. A good reference book and much trial and error will be necessary.

Start with an empty Excel workbook and save it with a representative name say, my_program.xls. The xls suffix indicates that it is a standard Excel workbook. Transfer to the VBA Editor and view the Project Explorer.

The figure shows the Project Explorer with the add_orie.xla and add_ormm.xla add-ins installed. The workbooks paj_components.xls and my_program.xls are open. There are three empty worksheets in paj_components.xls. The ThisWorkbook module is automatically created but it is initially empty.

Forms and modules are added by clicking on my_program.xls line of the Explorer and choosing Insert/Form or Insert/Module.

The Project explorer is shown below with one form and one module added. The Module name has been changed to Control in the Properties Inspector, another window of the VBA editor.

Double clicking on a row of the Project Inspector will open a window showing the contents of the item. Forms are used to create dialog boxes and modules hold VBA code.

The easiest way to add code is to copy it from modules of other add-ins or workbooks and paste the code into modules of my_program. To see examples open paj_components.xls or open any of the add-ins provided in the collection.

As coding proceeds be sure to save the workbook occasionally. The code is stored with the workbook. Workbooks may be saved using the Save command of the VBA Editor, but be sure the name of the workbook is selected in the Project Explorer when the Save command is selected.

As the project is coded, you can frequently return to Excel to test the project by running programs. Excel VBA has several debugging tools that are very helpful.

Name Workbook


At any time during the coding process an add-in can be created. I usually wait until the coding is almost complete. The code in add-ins may be changed, but it is usually more convenient to do the initial coding in a workbook, before the add-in has been created.

One important step is to name the workbook before the add-in is created. This is done in the Properties window of Excel. The name placed on the Titles line is the name that will appear in the Add-in dialog reached from the Tools menu. It is important to set the name here, because once the add-in is created the Properties dialog can no longer be accessed.

Save as Add-in

Before creating the add-in delete all but one page of the my_program.xls workbook and clear that page of contents. The add-in will save all worksheets or a project, but they are invisible and cannot be changed so I remove as many as possible.

The add-in is created by simply choosing Save As from the File menu item of Excel. In the dialog select Microsoft Excel Add-in as the format. It is at the bottom of the list of formats. Save the add-in with the xla suffix. Once the add-in is saved it must be installed through the Add-in dialog on the tools menu. It can no longer be opened like a workbook. Add-in code may be changed in the VBA editor. A changed add-in must be saved from the VBA editor. Excel doesn't warn you to save a changed add-in, so a common mistake is to close the add-in before saving the changes.


Return to Top

tree roots

Operations Research Models and Methods
by Paul A. Jensen
Copyright 2004 - All rights reserved