Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

As I note earlier, you can convert any workbook to an add-in, but not all workbooks are appropriate candidates for add-ins. First, an add-in must contain macros ( otherwise , it's useless).

Generally, a workbook that benefits most from being converted to an add-in is one that contains general-purpose macro procedures. A workbook that consists only of worksheets would be inaccessible as an add-in because worksheets within add-ins are hidden from the user . You can, however, write code that copies all or part of a sheet from your add-in to a visible workbook.

Creating an add-in from a workbook is simple. The following steps describe how to create an add-in from a normal workbook file:

1. Develop your application and make sure that everything works properly.

   Don't forget to include a way to execute the macro or macros in the add-in. You might want to add a new command to the Ribbon or a new menu item to a shortcut menu. See Chapters 22 and 23 for more information about modifying Excel's user interface.

2. Activate the Visual Basic Editor (VBE) and select the workbook in the Project window. Choose Tools xxx Properties (where xxx represents the name of the project) and then click the Protection tab. Select the Lock Project for Viewing check box and then enter a password (twice). Click OK.

   A Few Words about Passwords

   Microsoft has never promoted Excel as a product that creates applications in which the source code is secure. The password feature provided in Excel is sufficient to prevent casual users from accessing parts of your application that you'd like to keep hidden. Excel 2002 and later versions include stronger security than previous versions, but your passwords can be cracked. If you must be absolutely sure that no one ever sees your code or formulas, Excel is not your best choice as a development platform.

    

   This step is necessary only if you want to prevent others from viewing or modifying your macros or UserForms.

3. Reactivate Excel and choose Office Prepare Properties to display the Document Properties panel. Enter a brief descriptive title in the Title field and a longer description in the Comments field.

   This step is not required, but it makes the add-in easier to use by displaying descriptive text in the Add-Ins dialog box.

4. Choose Office Save As to display the Save As dialog box.

5. In the Save As dialog box, select Excel Add-In (*.xlam) from the Save as Type drop- down list.

6. Click Save. A copy of the workbook is saved (with an .xlam extension), and the original workbook remains open .

7. Close the original workbook and then install the add-in version.

8. Test the add-in to make sure it works correctly. If not, make changes to your code. And don't forget to save your changes.