XL: How to Create a Global User-Defined Function

ID: Q151490

The information in this article applies to:

Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Macintosh, versions 5.0, 5.0a

SUMMARY

By default, a user-defined function is limited in scope to the workbook in which the function resides. In other words, only sheets within the same workbook can call a user-defined function. If you want to call a user- defined function from another workbook, you must first make a reference to the workbook that contains the user-defined function.

For information about using references to call user-defined functions, please see the following article in the Microsoft Knowledge Base:

      ARTICLE-ID: Q141288
      TITLE     : How to Use a Custom Function in Another Workbook

However, to be able to call a user-defined function from any file, new or existing, you have to manually create a reference in each and every workbook. To make your user-defined functions globally available, you can group them into one workbook and make an Add-in. With a module sheet active, click Make Add-in on the Tools menu. When the Add-in is loaded into memory, your functions will be available to any file.

MORE INFORMATION

The Add-in must be loaded into memory for your functions to be available. The Add-in can be placed in your XLSTART folder (directory),(usually "C:\MSOFFICE\EXCEL\XLSTART" (without the quotation marks) in Windows versions, and in the Excel Startup Folder on the Macintosh, either "System:Preferences:Excel Startup Folder (5)" or "System:Preferences:Excel Startup Folder" (without the quotation marks), or loaded through using the Add-in Manager.

To load an Add-In through the Add-In Manager, follow these steps:

1. On the Tools menu, click Browse.

2. Locate the file, click to select it, and then click OK.

Before making an Add-in, do the following:

Save your source file. Once the Add-in is created, it cannot be edited.
Remove any debugging code, comments, extraneous sheets, and so on -- anything that is not essential for the code to run. Leave this nonessential information in your source file.
You may specify a name for your Add-in.

To name an Add-In in Microsoft Excel version 5.0, follow these steps:

1. On the File menu, click Summary Info.

2. Type the name in the Title box, and click OK.

To name an Add-In in Microsoft Excel version 7.0, follow these steps:

1. On the File menu, click Properties.

2. Type the name in the Title box, and then click OK.

For information about creating a name and description for an Add-in, please see the following article in the Microsoft Knowledge Base:

      ARTICLE-ID: Q108425
      TITLE     : How to Create Add-in Application Name and Description

REFERENCES

"Microsoft Excel Visual Basic User's Guide," version 5.0, Chapter 13, "Creating an Add-in Application," and Chapter 3, "Creating User-Defined Functions."

For more information about Installing Add-ins, click Search, or the Index tab in Help and type:

 Add-ins

For more information about User-defined Functions in Microsoft Excel 7.0, click the Index tab in Help and type:

 User-Defined Function

Additional query words: 5.00, 5.00a, 5.00c, 7.00, 7.00a

Keywords          : kbcode kbprg PgmHowto 
Version           : WINDOWS: 5.0, 5.0c, 7.0, 7.0a; MACINTOSH: 5.0, 5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbinfo

Last Reviewed: December 2, 1997