Addin Basics

Although you can convert any workbook to an add-in, not all workbooks benefit from this conversion. Workbooks that consist only of worksheets (and no macros) become unusable because the add-ins are hidden; you are unable to access the worksheets.

In fact, the only types of workbooks that benefit from being converted to an add-in are those with macros. For example, a workbook that consists of general-purpose macros (Sub and Function procedures) makes an ideal add-in.

Creating an add-in is simple. Use the following steps 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 method for executing the macro or macros. You might want to add a new menu item to the Tools menu or create a custom toolbar. See Chapter 20 for details on customizing the menus and Chapter 19 for a discussion of custom toolbars.

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

Doing so 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 VBE and select the workbook in the Project window; choose ToolsOVBAProject Properties and click the Protection tab; select the Lock Project for Viewing check box and 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.

4. In Excel, choose FileOProperties.

5. Click the Summary tab.

6. Enter a brief descriptive title in the Title field and a longer description in the Comments field. Click OK.

Steps 4 through 6 are not required but make the add-in easier to use.

7. Choose Excel's FileOSave As.

8. In the Save As dialog box, select Microsoft Office Excel add-in (*.xla) from the Save as Type drop-down list.

9. Specify the folder that will store the add-in.

Excel proposes a folder named AddIns, but you can save the file in any folder you like.

10. Click Save.

You've just created an add-in! A copy of your workbook is converted to an add-in and saved with an XLA extension. Your original workbook remains open.

0 0

Post a comment