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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

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 > Preparations
Deze pagina in het Nederlands

Creating an addin from an Excel macro.

Preparing A Macro For Distribution

When one creates an Excel macro for personal use (which one wishes to be available to all open workbooks), the best place to store that macro is in a special workbook named personal.xls (or Personal.xlsb in Excel 2007 and up). This workbook is generated automatically as soon as one records a macro and indicates that Excel should store it in the "Personal Macro Workbook", as shown in this figure:

Record macro dialog.

This workbook is a hidden workbook, saved in the XLSTART folder of Excel, from which Excel loads all files when it starts.

In Windows XP this folder may be located here:

C:\Documents and Settings\UserName\Application Data\Microsoft\Excel\XLSTART

But the folder can also be located in the path to Office (which will then be available to all users on the system). For Office XP:

C:\Program Files\Microsoft Office\Office10\XLSTART

If needed (and with the proper permissions), one can create this folder manually.

When one wants to distribute a macro to others, it is not a good idea to send them your personal.xls/personal.xlsb workbook. If your recipient has his own personal.xls/personal.xlsb and tries to open yours, Excel will generate an error message stating it cannot open two files with the same name. Alternatively, if the recipient copies your file to his own XLSTART folder, his own personal.xls/personal.xlsb gets overwritten, wiping out his own macros permanently.

The way to do this is to copy your macro to a fresh workbook. If the macro is contained in one ore more modules, this is a simple process:

  

Copying a module in the VBE

As soon as all relating modules, forms and class modules have been copied the new workbook should be saved.

If you are using Excel 2007 or up, make sure you select the proper file type in the save-as window. You need to use the xlsm filetype, otherwise if you use the default xlsx type, you're macros will NOT be saved with the workbook!