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

Q141288 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:
Q108425 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 : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a
Platform : MACINTOSH WINDOWS
Issue type : kbinfo


Last Reviewed: October 14, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.