Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Create Addins > Book or Addin
Deze pagina in het Nederlands

Creating an add-in from an Excel macro.

Use A Workbook Or An Add-in

Besides personal.xls/personal.xlsb, other workbooks may be placed in the XLSTART folder. All files placed in this location will be opened by Excel automatically and macro's of files placed in this special folder usually are enabled by default.

One could therefore place the new macro workbook in that folder to ensure the macros are available to us in each Excel session. There is one disadvantage however. As soon as other files than personal.xls are placed in that location, Excel will no longer open a blank workbook when you start it.

The alternative method to make the code available to each session is by creating an add-in from the macro workbook. add-ins are available through Excel's Tools, add-ins menu.

An add-in is nothing more than a normal workbook, with two special characteristics:
1. Its sheets are invisible
2. Once setup, it is available in the Tools, add-ins list. (from which the add-in can easily be set NOT to load, whereas a normal workbook located in the XLSTART folder has to be moved to a different folder when it isn't needed).

Whether an add-in or a normal workbook is to be preferred, depends on the purpose of the utility.
When worksheets of the utility workbook need to be shown to the user, a workbook is the way to go. With many utilities however this is not the case and it may be better to use an add-in.

Saving a workbook as an add-in

Creating an add-in is a fairly simple process, involving these simple steps:

Save-As Dialog with add-in selected

It is to be advised to do future editing work in the source workbook, rather than in the add-in itself. Some Excel versions will not reliably save an add-in from the Visual Basic Editor.