Making VBA code for the application, not the instance

Two examples --

If I write a VBA module for an Excel workbook, and I want that module to be available every time someone opens Excel (not the workbook it was written in) -- can that be done?

If I write VBA for Outlook, using, for example, the newmail event, can that code be made available network-wide or is it just for my workstation only?

Thank you

Who is Participating?
Excel uses a startup folder, (usually XLStart) to store default templates.  I've included the instructions for using this folder (taken from the Excel 2000 help) at the end of this post.  Other versions of Excel are similar but this should get you started.

I'm not aware of any easy way to share macros between Outlook clients.  There are a few options when you are using Outlook as an Exchange client but none that I'm aware of for straight Outlook.

Using the XLStart folder (from Excel 2000 Help)
Decide which type of template you want:

Workbook template

Create a workbook that contains the sheets, default text (such as page headers and column and row labels), formulas, macros, styles, and other formatting you want in new workbooks based on the template.

Worksheet template

Create a workbook that contains one worksheet. On the worksheet, include the formatting, styles, text, and other information you want to appear on all new sheets of the same type.

To display a picture of the first page of a template in the Preview box of the Templates dialog box (General Templates..., New Workbook task pane), click Properties on the File menu, click the Summary tab, and then select the Save preview picture check box.

On the File menu, click Save As.

In the Save as type box, click Template.

In the Save in box, select the folder where you want to store the template.

To create the default workbook template or default worksheet template, select either the XLStart folder or the alternate startup folder. The XLStart folder is usually

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

To create a custom workbook or worksheet template, select the Templates folder, which is usually

C:\Documents and Settings\user_name\Application Data\Microsoft\Templates

Enter the name of the template in the File name box. Do one of the following:

Workbook template

Type book to create the default workbook template.

To create a custom workbook template, type any valid file name.

Worksheet template

Type sheet to create a template for default worksheets.

To create a custom sheet template, type any valid file name.

Click Save, and then click Close on the File menu.

You can also save your workbook as a .xla add-in (it's one of the options in the File...Save As dialog). The Tools...Add-ins menu item allows you to select which add-ins get loaded every time Excel launches. Macro subs and functions contained in those add-ins are then available for use in any open workbook. Worksheets in the add-in may store data, but are not visible when the add-in is opened.

Besides templates, you can also store workbooks in the XLSTART folder. These workbooks will be opened when Excel launches, thus making their macros available for use. At least one worksheet must remain unhidden--but you can hide the window that contains it (using the Window...Hide) menu item. Note that if you hide the Window, you can save the file from the VBA Editor File...Save menu item.

Excel 97 and before used Personal.xls to store macros for ready use. Excel 2000 and later still allow the use of Personal.xls, but store it in the XLSTART directory. As such it is nothing different from any other workbook stored in the XLSTART folder.
Putting files in the XLSTART folder, Personal.xls and .xla add-ins are all valid solutions for a single user profile on a single workstation. This installation must be repeated for each different user and workstation in the system.

If you use functions from your add-in in a workbook you develop--it will work fine on your computer. But it won't work at all on a co-workers computer unless you also provide a copy of the add-in. For this reason, I prefer to package all the required code with each workbook I distribute to coworkers. This allows the files to be shared freely, both inside and outside the company.

The add-in approach is more useful if other people frequently develop spreadsheets for their own use, and wish to use functions and subs that you have written. As long as everybody has the add-in, maintenance is simpler because just one add-in needs to be updated (on each computer) rather than a large number of workbooks on each computer that were packaged with all the required VBA code.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.