How to Create a Global User-Defined Function

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

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:

141288 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:

108425 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
Properties

Article ID: 151490 - Last Review: Oct 10, 2006 - Revision: 1

Feedback