Creating an AddIn

As I note earlier, you can convert any workbook to an add-in, but not all workbooks are appropriate candidates for add-ins. Generally, a workbook that benefits most from being converted to an add-in is one that contains macros — especially 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 menu or menu item or to create a custom toolbar. See Chapter 23 for details on customizing menus and Chapter 22 for a discussion of custom toolbars.

2. Test the application by executing it when a different workbook is active.

This simulates the application's behavior when it's used as an add-in because an add-in is never the active workbook.

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

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

A Few Words about Security

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 includes 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.

4. Reactivate Excel and choose File ^ Properties, click the Summary tab, and 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.

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

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

A workbook being converted to an add-in must have at least one worksheet. For example, if your workbook contains only chart sheets or Excel 5/95 dialog sheets, the Microsoft Excel Add-In (*.xla) option does not appear in the Save As dialog box.Also,this option appears only when a worksheet is active when you choose the File ^ Save As command.

0 0

Post a comment