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:
- Open a blank workbook (File, New).
- Open the Visual Basic Editor (alt-F11).
- Copy the module(s), Class Module(s) and Userform(s) that contain your code by dragging their entry in the project explorer to the project of the new empty workbook (see below).
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!