Microsoft KB Archive/151490

= How to Create a Global User-Defined Function =

Article ID: 151490

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q151490





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

