Excel® 2007 VBA Programmer's Reference

Chapter 17. Add-ins

If you want to make your workbook invisible to the user in the Excel window, you can turn it into an Add-in file. An Add-in can be loaded into memory using Open under the Microsoft Office button, but it generally makes more sense to access it via the Add-Ins dialog box, which is covered later in this chapter. Either way, the file does not appear in the Excel Application window, but the macros it contains can be executed from the user interface. Any user-defined functions it contains can be used in worksheet calculations. The Add-ins macros can be attached to menu commands and toolbar buttons, and the Add-in can communicate with the user through UserForms and VBA functions such as InputBox and MsgBox.

It is widely believed that an Add-in is a compiled version of a workbook. In programming, compilation involves translating the human-readable programming code into machine language. This is not the case with an Excel Add-in. In fact, all that happens is that the workbook is hidden from the user interface. The Add-in's worksheets and charts can no longer be seen by anyone. Its code modules can still be viewed, as normal, in the VBE window and remain complete with comments as well as code.

However, it is possible to create a compiled version of an Add-in. This is referred to as a COM (Component Object Model) Add-in. COM Add-ins are discussed separately in Chapter 18.

This chapter has taken the CommandBars2.xlsm file used in Chapter 15, saved it as AddIn.xlsm prior to ...