Give the add-in a name and description by filling in the file properties (file summary information) for the source workbook. The title field appears in the add-in manager's available add-ins list, and the comments field becomes the description. This is shown in Figure 12 below.
Figure 12. Setting up the add-in title and description
If add-in code accesses objects within its own workbook such as dialog sheets or internal worksheets, be sure to declare object references using the syntax
ThisWorkbook.DialogSheets("Wiz1")
rather than
Application.DialogSheets("Wiz1") or ActiveWorkbook.DialogSheets("Wiz1").
Using the reference style ActiveWorkbook.DialogSheets works as long as the active workbook actually contains the dialog, as is probably the case while writing the application. However it won't work after making the workbook into an add-in since by definition an add-in can't be the active workbook.
Code should convert to an add-in quite smoothly by following the guidelines above. If there are any problems try adding Debug.Print or MsgBox statements to the code, or write data to a visible worksheet as a way of tracing errors.
During the process of debugging an add-in there's the need to create and install new versions. If the add-in doesn't seem to behave properly when updating to a new version be sure to clear the check box for the add-in using the Tools/Add-Ins dialog, then delete or rename the old add-in file, make a new version, close and exit Excel, then restart it and load your new version. If this becomes a frequent process, create a small utility routine in the add-in to unload it from memory:
Sub RemoveMyAddIn
ThisWorkbook.Close
End Sub
Assign this routine to a menu item or toolbar button. Closing a file through the add-in manager takes some time, but the add-in can close itself and this process is very fast. When ready to update the add-in the above routine can quickly remove it from memory and install a new version without leaving Excel. Use this method only for convenience while debugging; don't include it in the finished version.
Comments in code are saved in the add-in file. If there's any concern about security or reducing the file size, remove all comments from code before making the final version. A good way to do this is to prefix all comments with two single quotes, as in the example below.
''These comments will be deleted
''when the final version of the add-in is created
Sub Main()
Just before making the final version enable pattern matching in the Edit/Replace command and use it to replace all occurrences of ''* with null text.