Excel 2019 Bible

1027

C H A P T E R

48

Creating Custom Excel Add-Ins

IN THIS CHAPTER

Understanding add-ins

Converting a workbook to an add-in

F

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

Understanding Add-Ins

Generally speaking, an add-in is something that’s added to software to give it additional functional-

ity. Excel includes several add-ins, including the Analysis ToolPak and Solver. Ideally, the new fea-

tures 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 candi-

date for an add-in.

What distinguishes an add-in from a normal workbook? Add-ins, by default, have an .xlam exten-

sion. In addition, add-ins are always hidden, so you can’t display worksheets or chart sheets that

are contained in an add-in. However, 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 utilities that extend the power of

Excel.

Store proprietary macros If you don’t want end users to see (or modify) your macros, store the

macros in an add-in and protect the VBA project with a password. Users can use the macros, but

they can’t view or change them unless they know the password. An additional benefit is that the

add-in doesn’t display a workbook window, which can be distracting.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.