[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Making VBA code for the application, not the instance

Posted on 2004-10-21
Medium Priority
Last Modified: 2010-05-02
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

Question by:gary_j
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

jmacmicking earned 1000 total points
ID: 12374018
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.

LVL 81

Assisted Solution

byundt earned 1000 total points
ID: 12387250
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.
LVL 81

Expert Comment

ID: 12387265
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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question