Making VBA code for the application, not the instance

Posted on 2004-10-21
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
    LVL 5

    Accepted Solution

    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 80

    Assisted Solution

    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 80

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    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…

    860 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now