Excel 2016 Bible

Chapter 45Creating Custom Excel Add-Ins

IN THIS CHAPTER

1. Understanding add-ins
2. Converting a workbook to an add-in

For developers, one of the most useful features in Excel is the capability to create add-ins. This chapter discusses this concept and provides a practical example of creating an add-in.

What Is an Add-In?

Generally speaking, an add-in is something that's added to software to give it additional functionality. Excel includes several add-ins, including the Analysis ToolPak and Solver. Ideally, the new features blend in well with the original interface so that they appear to be part of the program.

Excel's approach to add-ins is quite powerful: any knowledgeable Excel user can create add-ins from workbooks. The type of add-in covered in this chapter is basically a different form of a workbook file. Any Excel workbook can be converted into an add-in, but not every workbook is a good candidate for an add-in.

What distinguishes an add-in form a normal workbook? Add-ins, by default, have an .xlam extension. In addition, add-ins are always hidden, so you can't display worksheets or chart sheets that are contained in an add-in. But you can access its VBA procedures and display dialog boxes that are contained on UserForms.

The following are some typical uses for Excel add-ins:

• Store one or more custom worksheet functions. When the add-in is loaded, you can use the functions like any built-in worksheet function.
• Store Excel utilities. VBA is ideal for creating general-purpose ...