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 Addln.xlsm prior to converting it to Addin.xlam, and adapted the code to make it suitable for an Add-in. The code on popup menus has been removed because it is not relevant.

In previous versions of Excel, it is not necessary to give an Add-in filename any special extension. In Excel 2007, however, it is necessary to give an Add-in filename an .xlam extension. It is a good idea to do so, in any case, because it identifies the file as an Add-in and ensures that the Add-in icon appears against the file in the Windows File Manager. The conversion of a workbook file to an Add-in file is covered later in this chapter.

0 0

Post a comment