Distributing an Excel add-in is a good way to distribute macros.
It’s an easy process, but if you choose this route, be sure to
use the ThisWorkbook property carefully because it won’t work as
expected in the add-in. VBA’s ThisWorkbook property refers to
the workbook that contains the VBA code. That’s why it can seem
to fail when used in an add-in; it returns the name of the add-
in, not the name of the workbook that’s hosting the add-in.
Instead of using ThisWorkbook, use ActiveWorkbook to refer to
the hosting workbook instead of the add-in. Of course, you
should use ThisWorkbook if you mean to refer to the add-in in
your code. To illustrate this gotcha, we’ll create two similar
macros and save the workbook as an add-in. Then, we’ll install
the add-in in a second workbook and run both macros to see how
they differ. Creating the add-in This add-in will contain only
two macros. To create the file, open a new workbook and then
open the Visual Basic Editor (VBE) by pressing [Alt]+[F11].
Next, do the following: -